SQL Abfrage optimieren

[Gast]

und unter welchen voraussetzungen würde die session var geändert ?
denke über eine db abfrage

in einem projekt von mir wird einfach bei jedem eintreffen einer nachricht einmal in einem entsprechenden feld der wert für nachrichten und ungelesene nachrichten gespeichert. diese beiden werte werden mit allen anderen relevanten userdaten in die session gespeichert. die 2. stelle an der diese beiden zahlen aktualisiert werden, ist wenn der user tatsächlich die nachricht liest.
es muss also nicht bei jedem seitenaufruf gerechnet werden sondern nur an diesen beiden stellen

[martinm79]

So, das mit der Nachricht ist ja nun klar.
Der User wird es einfach aus der Session entnehmen. Und das ist ein anderes Thema.

Und falls man es "zum Anfang" in der User Tabelle zwischenspeichert, habe ich auch gleich mal die Zeit gemessen.
Alte Abfrage: 0.02 bei 300000 Nachrichten.
Bei 5 Millionen möchte ich die Zahl garnicht lesen.

Ich kam auf 0.0009 und 0.0008 wenn ich beim 1. Feld gezielt aus 300000 Datensatze lese und beim 2. aus einer Usertabelle gezielt rauslese.
Damit kann man auch "erstmal" bei jedem Seitenaufruf leben.



Mit dem optimieren von SQL bin ich aber noch nicht schlauer geworden.
Die folgende Abfrage ist zwar nur auf der Übersichtsseite, dauert aber 0.03 bis 1.0 Sekunden und ich weiß nicht, wie ich da richtig den Index setzten soll. (Oder was ich verändern müßte)

SELECT
    u.geschlecht_id,
    u.nick,
    IF(u.online,IF(UNIX_TIMESTAMP()-UNIX_TIMESTAMP(u.datum_aktiv) < 3600,1,0),0) online
FROM  prefix_visit v
LEFT JOIN prefix_user u
    ON  u.user_id = v.visit_user_id
WHERE
    v.user_id = '%d'
AND 
    u.del_user_id = 0
AND 
    u.aktiviert = 1
AND 
    u.spam <> 1
GROUP BY v.visit_id
ORDER BY v.datum DESC
LIMIT 5 

Die Abfrage ermittelt nur die letzten 5 Profilbesucher.
Die Geschlecht ID, den Nicknamen und den Status (online / offline)

Die beiden Tabellen sind so aufgebaut.

CREATE TABLE prefix_visit (
  visit_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INTEGER UNSIGNED NULL,
  visit_user_id INTEGER UNSIGNED NOT NULL,
  datum DATETIME NULL,
  PRIMARY KEY(visit_id),
  INDEX prefix_visit_FKIndex1(user_id),
  INDEX prefix_visit_FKIndex2(visit_user_id)
);


CREATE TABLE prefix_user (
  user_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  ...
  geschlecht_id TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  nick VARCHAR(40) NULL,
  datum_aktiv DATETIME NULL,
  del_user_id INTEGER UNSIGNED NOT NULL DEFAULT 0,
  aktiviert TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  online TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  spam INTEGER UNSIGNED NOT NULL DEFAULT 0,
  ...
  PRIMARY KEY(user_id),
  INDEX prefix_FKIndex1(geschlecht_id),
  INDEX prefix_FKIndex2(land_id),
  INDEX prefix_FKIndex3(bundesland_id),
  INDEX prefix_FKIndex4(familienstatus_id),
  INDEX prefix_FKIndex5(raucher_id),
  INDEX prefix_FKIndex6(haushalt_id),
  INDEX prefix_FKIndex7(kinderwunsch_id),
  INDEX prefix_FKIndex8(haare_id),
  INDEX prefix_FKIndex9(augen_id),
  INDEX prefix_FKIndex10(figur_id),
  INDEX prefix_FKIndex11(s_geschlecht_id),
  INDEX prefix_FKIndex12(s_haushalt_id)
); 

Ein Explain Ergebnis habe ich im Anhang rangehangen.
Wäre top wenn ich da mal ein Tipp bekommen könnte, wie ich bei sowas vorgehen muß und worauf ich achten muß.

Bei mir local lief ja alles top, aber online bei mehr Datensätzen darf es nicht manchmal 1 Sekunde dauern.

[ghostgambler]

Tabelle visit:
INDEX prefix_visit_FKIndex1(user_id),
ändere den mal auf
(user_id, datum)

Danach noch mal EXPLAIN und posten bitte.

[h3ll]

Original geschrieben von martinm79
IF(u.online,IF(UNIX_TIMESTAMP()-UNIX_TIMESTAMP(u.datum_aktiv) < 3600,1,0),0) online

Wenn du immer erst auf Unix Timestamps umrechnest, wird es natürlich länger dauernd. Warum verwendest du nicht einfach normale DATETIME-Werte?

[martinm79]

@ghostgambler Ich habe das Feld Datum mal komplett weggelassen, da die Zeit nicht releveant ist sondern nur die Reihenfolge, habe dafür einfach das Feld visit_id genommen.

@h3ll Danke für den Hinweis. Konnte zum Anfang gut damit rechnen, werde ich aber umstellen.
Habe es jetzt zu testzwecken auch mal komplett weggenommen.


Hier nochmal die neue Abfrage. Einfach nur der Nickname, der zuletzt auf dem jeweiligen Profil war.

Die Abfrage dauerte 1.0960 sek

SELECT
    u.nick
FROM  prefix_visit v
LEFT JOIN prefix_user u
    ON  u.user_id = v.visit_user_id
WHERE
    v.user_id = '%d'
AND
    u.del_user_id = 0
AND
    u.aktiviert = 1
AND
    u.spam <> 1
GROUP BY v.visit_id
ORDER BY v.visit_id DESC
LIMIT 5 

Ein Bild von dem Explain Ergebnis habe ich rangehangen.
Wie werte ich sowas richtig aus?

[h3ll]

Mach mal statt dem LEFT einen INNER JOIN.

[martinm79]

Ich glaube ich muß da auch ganz anders vorgehen.

Also ich habe mal die einfache Abfrage probiert:

SELECT
    visit_id 
FROM  prefix_visit v
WHERE
    v.user_id = '%d'
GROUP BY v.visit_id
ORDER BY v.visit_id DESC
LIMIT 5; 

Und selbst diese Abfrage dauerte zu lange.
Dann habe ich mal die letzte ID von diesem User genommen:

SELECT
    visit_id 
FROM  prefix_visit v
WHERE
    visit_id >= 869611
AND v.user_id = '%d'
GROUP BY v.visit_id
ORDER BY v.visit_id DESC
LIMIT 5; 

Dann ging die Abfragen mit 0.0002 Sekunden.

Ich muß also auch die Profilaufrufe und die letzte ID in der Conf / Session zwischenspeichern.
Und immer nur Abfragen ob es ein neuen Besucher auf dem Profil gibt.

[martinm79]

Danke nochmal an alle für die Hilfe.


Und komm ich in den temp/ Ordner wo die Session liegen oder kann ich einfach den temp/ bzw. Session Ordner in der php.ini ändern so das ich denn mittels php daran komme?


Dann speicher ich die Session ID beim einloggen in der DB und wenn ich Daten vom anderen User ändern möchte, hole ich mir die Session ID und änder die Session.

Geht das so? ^^

[h3ll]

Hmm... sehr komisch. Optimier mal die Tabelle.

[ghostgambler]

Kein Explain-Output -> Keine vernünftige Antwort möglich.