SQL Abfrage optimieren

[martinm79]

Hallo,

mich würde mal interessieren, ob man eine Abfrage noch optimieren kann.
Und wenn ja, was ich noch verbessern kann?


Bei jedem Seitenaufruf prüft diese, ob der User eine neue Nachricht hat.
In der Nachrichtentabelle befinden sich zurzeit ca. 300000 Einträge.
Die Abfrage dauert "im Schnitt" 0.02 Sekunden.

SELECT
    COUNT(*) a
FROM
    prefix_nachrichten
WHERE
    user_id_ein = '%d'
AND
    del_ein = 0
AND
    datum_gelesen IS NULL 

Bei einer Abfrage mit Explain kommt das raus:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE prefix_nachrichten ref prefix_nachrichten_FKIndex1,del_ein prefix_nachrichten_FKIndex1 4 const 2863 Using where

Das ist der Aufbau der Tabelle.

CREATE TABLE prefix_nachrichten (
  nachrichten_id INTEGER UNSIGNED NOT NULL,
  ordner_id INTEGER UNSIGNED NOT NULL DEFAULT 0,
  user_id_ein INTEGER UNSIGNED NOT NULL DEFAULT 0,
  user_id_aus INTEGER UNSIGNED NOT NULL DEFAULT 0,
  antwort_id INTEGER UNSIGNED NOT NULL DEFAULT 0,
  del_ein TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  del_aus TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  datum_del_ein DATETIME NULL,
  datum_del_aus DATETIME NULL,
  datum_gesendet DATETIME NULL,
  datum_gelesen DATETIME NULL,
  datum_beantwortet DATETIME NULL,
  nachricht TEXT NULL,
  PRIMARY KEY(nachrichten_id),
  INDEX prefix_nachrichten_FKIndex1(user_id_ein),
  INDEX prefix_nachrichten_FKIndex2(user_id_aus),
  INDEX prefix_nachrichten_FKIndex3(ordner_id),
  INDEX prefix_nachrichten_FKIndex4(del_ein)
); 

Ich frage mich, wie Xing oder andere Seiten sowas lösen. In deren Tabellen befinden sich doch mehrere Millionen Einträge.

Wie kann ich die Abfrage noch verbessern?

[h3ll]

Mach mal ein EXPLAIN().

Hat damit nix zu tun, aber ich würd mich interessieren, warum du für del_ein und del_aus ein TINYINT verwendest? Wäre da nicht ENUM passender?

[TBT]

einen Index über alle 3 Felder aus der WHERE Bedingung

[onemorenerd]

Re: SQL Abfrage optimieren

Original geschrieben von martinm79
Ich frage mich, wie Xing oder andere Seiten sowas lösen.

Na vielleicht in-/dekrementieren sie einfach einen Zähler im Userprofil wenn eine Nachricht eingeht bzw. gelesen wird. Meldet sich der User an, kann der Zähler zusammen mit dem Passwortvergleich aus der DB in die Session geholt werden.
Das heißt, wenn ein User gerade eingeloggt ist, muss der Zähler (auch) in seiner Session angepasst werden.

Ich weiß nicht wie es Xing oder andere machen. Aber so ginge es, wie du siehst, völlig ohne DB-Anfrage.

[martinm79]

@h3ll
Ein

EXPLAIN SELECT
    COUNT(*) a
FROM
    prefix_nachrichten
WHERE
    user_id_ein = '1'
AND
    del_ein = 0
AND
    datum_gelesen IS NULL 

habe ich schon gemacht. Das Ergebnis steht oben im Posting drinne.
Hat Tinyint nicht 1 Byte Speicherbedarf?
Ich guck mir das morgen nochmal an.

@TBT
Wie mache ich einen Index über alle 3 Felder aus der WHERE Bedingung ?

Wo ich den Index bei "del_ein" vorhin gesetzt habe, hat MYSQL knapp 27 Sekunden gebraucht und es war danach schon bisschen besser. Also davor dauerten die Abfragen noch länger als 0.02


@onemorenerd
Das mit dem zwischenspeichern in der Session habe ich mir zwar auch schon überlegt. Das würde mir aber nur was nützen bei der gesamten Nachrichtenanzahl. Trotzdem muß ich immer bei jedem Seitenaufruf mit einer kleinen Abfrage gucken ob eine neue Nachricht da ist.

[h3ll]

Original geschrieben von martinm79
Hat Tinyint nicht 1 Byte Speicherbedarf?

Ja schon, aber wenn du nur 0 und 1 verwendest, hast du 254 weitere mögliche undefinierte Zustände. Ich sprech das nicht wegen der Leistung an, sondern nur der Logik wegen. Von der Leistung her ist es wahrscheinlich völlig egal.

Bei deinem EXPLAIN kann ich irgendwie überhaupt nichts erkennen. Kannst du das lesbar formatieren?

[martinm79]

Habe das Ergebnis von Explain als Grafik im Angang.

Konnte es hier leider nicht besser darstellen.

Ja stimmt 0 und 1, das hätte ich auch in einer Spalte bekommen. Ich hätte aber auch eine timestamp Spalte mit 4 Byte nehmen sollen und nicht Datetime mit 8 Byte. Nächstes mal bin ich schlauer.


Leider komm ich mit dem Explain Ergebnis nicht weiter.

[PHP-Desaster]

Schau doch mal im Manual nach. Dort steht zum Beispiel, dass der Typ const für eine solche Abfrage der bestmögliche Typ ist. Die Abfrage ist also schon gut.

[onemorenerd]

Original geschrieben von martinm79
@onemorenerd
Das mit dem zwischenspeichern in der Session habe ich mir zwar auch schon überlegt. Das würde mir aber nur was nützen bei der gesamten Nachrichtenanzahl. Trotzdem muß ich immer bei jedem Seitenaufruf mit einer kleinen Abfrage gucken ob eine neue Nachricht da ist.

Nein, nicht bei jedem Seitenaufruf sondern nur wenn eine neue Nachricht eingeht. Aber geh erstmal den üblichen Weg. Bis du die Größe von Xing erreichst, ist das völlig okay.

[UzumakiNaruto]

user 234 schickt eine nachricht an user 2

die nachricht wird in der db gespeichert.
zusätzlich wird der nachrichtenzähler von user2 um eins hochgezählt.
ließt user 1 die nachricht wird er wieder runter gezählt.

das wäre die schnellste möglichkeit,

phpbb3 macht das genauso (wbb wohl auch) .. mit den posts und den pns.

[UzumakiNaruto]

ich meinte natürlich wenn user 2 die nachricht ließt

und du musst immer überprüfen ob es neue nachrichten gibt .. bei jedem seitenaufruf .. da wirst du NIE drumrumkommen

[onemorenerd]

Original geschrieben von UzumakiNaruto
und du musst immer überprüfen ob es neue nachrichten gibt .. bei jedem seitenaufruf .. da wirst du NIE drumrumkommen

Diese Prüfung muss aber nicht zwangsläufig ein COUNT() sein, im Idealfall ist dazu überhaupt keine DB-Anfrage notwendig. Der Wert kann schon fertig in $_SESSION stehen.

[h3ll]

Aber woher weiß die Session, dass inzwischen eine neue Nachricht gekommen ist? Das heißt man würde solange keine neuen Nachrichten empfangen können, bis irgendwann die Session gelöscht oder eine neue Session erstellt wird.

[martinm79]

Also vielen Dank für den super Hinweis.
Bei mir hat es Klick gemacht.

Also meine Abfrage war zwar richtig, aber die vorgehensweise nicht.
Ich bereite mal gleich was vor. Will auch gleich mal die Zeit messen.

[UzumakiNaruto]

Original geschrieben von h3ll
Aber woher weiß die Session, dass inzwischen eine neue Nachricht gekommen ist? Das heißt man würde solange keine neuen Nachrichten empfangen können, bis irgendwann die Session gelöscht oder eine neue Session erstellt wird.

oder die session var geändert wird