SQL Query - Performance Problem

Neox · 05.09.2011, 02:21

Hallo Team

Code:

SELECT neox_updates.id
FROM neox_updates
LEFT JOIN neox_noticebox_view ON neox_updates.id = neox_noticebox_view.update_id
WHERE (
neox_noticebox_view.update_id IS NULL 
)
AND (
(
username = 'Neo'
AND public =0
AND visible =0
)
OR (
visible =1
OR public =1
)
OR (
via = 'Neo'
)
)
LIMIT 10

Der obrige SQL-Query soll alle Updates abfragen, die folgende Kriterien erfüllen:

* noch nicht gelesen (neox_noticebox_view.update_id IS NULL)
* alle privaten Updates des Users (username = 'Neo' AND public =0 AND visible =0)
* alle öffentlichen Updates (visible =1 OR public =1)
* alle Nachrichten, die über den User gepostet wurden (via = 'Neo')

Das Problem liegt nicht in der Funktion, sondern in der Performance:

Zitat:

die Abfrage dauerte 6.9426 sek

Habe vorher an der Stelle des LEFT JOINs ein NOT IN verwendet, mit einem Ergebnis von 37 Sekunden Laufzeit.

Wäre es sinnvoller/schneller/effektiver, die Funktion "noch nicht gelesen" via PHP zu lösen, oder gibt es eine SQL-basierte Lösung?

Vielen Tausend Dank schonmal im Voraus,
Neo

ChrisB · 05.09.2011, 02:49

Welche Indizes hast du auf welche Spalten gesetzt …?

Neox · 05.09.2011, 03:27

Ich hab mich mit Indizes noch nicht wirklich beschäftigt, ergo: keine.

Neox · 05.09.2011, 03:32

Oha! Was so ein Index über neox_noticebox_view.update_id ausmacht > Laufzeit: 0.02 sec

Was passiert da DB-intern?

mermshaus · 05.09.2011, 05:03

Im Zweifel das hier: http://de.wikipedia.org/wiki/B-Baum

Stell dir vor, du suchst in einem Buch nach einem Stichwort. Da kannst du entweder alle Seiten durchgehen und prüfen, ob das Stichwort auftaucht, oder du kannst hinten im Buch den Index aufblättern, der dir zum jeweiligen Stichwort die relevanten Seitenzahlen nennt.

Ziemlich genau so verhält es sich mit Datenbank-Indizes.

Die Datensätze einer Tabelle sind grundsätzlich als ungeordnet anzusehen. Ohne Indizes bleibt dem Datenbanksystem deshalb nichts anderes übrig, als davon auszugehen, dass prinzipiell jeder Datensatz ein nachgefragtes Kriterium erfüllen kann. Das bedeutet, jeder Datensatz muss einzeln geprüft werden (full table scan).

Ein Index ist nun beispielsweise eine zusätzlich abgespeicherte, in einer Baumstruktur angeordnete Übersicht, die eine nach einer bestimmten Spalte sortierte Liste aller Datensätze enthält. (Wie eine Datenbank das im Detail umsetzt, ist grundsätzlich wenig relevant.)

Simples Beispiel: Du hast eine Tabelle, die Nutzernamen enthält. Du möchtest nun alle Nutzernamen auslesen, die mit b beginnen. Auf der entsprechenden Spalte hast du einen Index. Das Datenbanksystem beginnt nun, sich durch den Index-Baum zu hangeln. Auf der erste Ebene könnte das heißen: „Alle Einträge, die ‚kleiner‘ als Anfangsbuchstabe n sind, stehen in diesem Zweig, alle weiteren im anderen.“ Gefolgt wird also dem ersten Zweig. An der nächsten Abzweigung heißt es dann meinetwegen: „kleiner als g sowie: zwischen g (inklusiv) und n (exklusiv).“ Somit wird durch den Index und zwei günstige Operationen die Anzahl der zu durchlaufenden Datensätze schon mal auf grob ein Viertel reduziert.

Die Darstellung war jetzt nicht sonderlich exakt, aber ich denke, es wird deutlich, wie immens der Gewinn ist.

Deshalb sollte etwa auch in jedem WHERE-Clause mindestens eine Spalte auftauchen, auf der ein Index liegt.

Neox · 05.09.2011, 06:05

Danke für die ausführliche Erklärung!
Also kann ich ohne weiteres auf alle Spalten, in denen ich oft suchen werde, einen Index legen, seh ich das richtig?

Vielen Dank!

mermshaus · 05.09.2011, 13:01

Ja. Hier wird das alles auch noch mal offiziell beschrieben:

- http://dev.mysql.com/doc/refman/5.6/...l-indexes.html

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
[Erledigt] Query Problem	ragtek	Datenbanken	3	29.06.2011 09:12
[Erledigt] Problem bei Zugriff von zwei Tabellen auf einer Tabelle - welcher JOIN?	darkman	Datenbanken	9	06.05.2011 11:47
[Erledigt] SQL Query funktioniert im phpmyadmin aber nicht mit PDO und PHP	litterauspirna	PHP Einsteiger	3	09.01.2011 10:50
[Erledigt] sql query mit variablen?	Helix	PHP Einsteiger	6	08.12.2010 11:19
[Erledigt] sql query nochmal filtern	gersh	PHP Einsteiger	30	02.12.2010 22:30
SQL Datei mit PHP ausführen	Kein Genie	PHP Tipps 2010	2	09.11.2010 09:52
[Erledigt] problem mit mysqli query	fulltilt	PHP Tipps 2010	10	24.09.2010 11:52
[Erledigt] Query Problem mit between bei Datum	fulltilt	PHP Tipps 2010	3	02.06.2010 12:43
Unabhängige "Auto-Increment Spalte" für ein ON DUPLICATE KEY query?	juice122	Datenbanken	4	31.05.2010 20:59
[Erledigt] performance query frage	rudygotya	Datenbanken	16	08.08.2009 12:23
ImageMagick Performance Problem	M3g4Star	PHP Tipps 2006	1	30.11.2006 09:35
Mega Sql Query Problem	Finkman	Datenbanken	7	20.05.2006 12:17
like opeartor - performance problem		Datenbanken	15	08.12.2004 03:48
[Erledigt] Query läuft nicht		Datenbanken	6	13.08.2004 21:13


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

05.09.2011, 02:49
ChrisB Moderator¹ Registriert seit: 28.03.2010 Beiträge: 7.470 PHP-Kenntnisse: Fortgeschritten	Welche Indizes hast du auf welche Spalten gesetzt …? __________________ RGB is totally confusing - I mean, at least #C0FFEE should be brown, right?

05.09.2011, 03:27
Neox Neuer Benutzer Registriert seit: 05.09.2011 Beiträge: 5 PHP-Kenntnisse: Fortgeschritten	Ich hab mich mit Indizes noch nicht wirklich beschäftigt, ergo: keine.

05.09.2011, 03:32
Neox Neuer Benutzer Registriert seit: 05.09.2011 Beiträge: 5 PHP-Kenntnisse: Fortgeschritten	Oha! Was so ein Index über neox_noticebox_view.update_id ausmacht > Laufzeit: 0.02 sec Was passiert da DB-intern? Geändert von Neox (05.09.2011 um 03:50 Uhr).

05.09.2011, 05:03
mermshaus Erfahrener Benutzer Registriert seit: 14.06.2009 Beiträge: 1.723 PHP-Kenntnisse: Fortgeschritten	Im Zweifel das hier: http://de.wikipedia.org/wiki/B-Baum Stell dir vor, du suchst in einem Buch nach einem Stichwort. Da kannst du entweder alle Seiten durchgehen und prüfen, ob das Stichwort auftaucht, oder du kannst hinten im Buch den Index aufblättern, der dir zum jeweiligen Stichwort die relevanten Seitenzahlen nennt. Ziemlich genau so verhält es sich mit Datenbank-Indizes. Die Datensätze einer Tabelle sind grundsätzlich als ungeordnet anzusehen. Ohne Indizes bleibt dem Datenbanksystem deshalb nichts anderes übrig, als davon auszugehen, dass prinzipiell jeder Datensatz ein nachgefragtes Kriterium erfüllen kann. Das bedeutet, jeder Datensatz muss einzeln geprüft werden (full table scan). Ein Index ist nun beispielsweise eine zusätzlich abgespeicherte, in einer Baumstruktur angeordnete Übersicht, die eine nach einer bestimmten Spalte sortierte Liste aller Datensätze enthält. (Wie eine Datenbank das im Detail umsetzt, ist grundsätzlich wenig relevant.) Simples Beispiel: Du hast eine Tabelle, die Nutzernamen enthält. Du möchtest nun alle Nutzernamen auslesen, die mit b beginnen. Auf der entsprechenden Spalte hast du einen Index. Das Datenbanksystem beginnt nun, sich durch den Index-Baum zu hangeln. Auf der erste Ebene könnte das heißen: „Alle Einträge, die ‚kleiner‘ als Anfangsbuchstabe n sind, stehen in diesem Zweig, alle weiteren im anderen.“ Gefolgt wird also dem ersten Zweig. An der nächsten Abzweigung heißt es dann meinetwegen: „kleiner als g sowie: zwischen g (inklusiv) und n (exklusiv).“ Somit wird durch den Index und zwei günstige Operationen die Anzahl der zu durchlaufenden Datensätze schon mal auf grob ein Viertel reduziert. Die Darstellung war jetzt nicht sonderlich exakt, aber ich denke, es wird deutlich, wie immens der Gewinn ist. Deshalb sollte etwa auch in jedem WHERE-Clause mindestens eine Spalte auftauchen, auf der ein Index liegt. __________________ Blog \| Buch \| Kaloa

05.09.2011, 06:05
Neox Neuer Benutzer Registriert seit: 05.09.2011 Beiträge: 5 PHP-Kenntnisse: Fortgeschritten	Danke für die ausführliche Erklärung! Also kann ich ohne weiteres auf alle Spalten, in denen ich oft suchen werde, einen Index legen, seh ich das richtig? Vielen Dank!

05.09.2011, 13:01
mermshaus Erfahrener Benutzer Registriert seit: 14.06.2009 Beiträge: 1.723 PHP-Kenntnisse: Fortgeschritten	Ja. Hier wird das alles auch noch mal offiziell beschrieben: - http://dev.mysql.com/doc/refman/5.6/...l-indexes.html __________________ Blog \| Buch \| Kaloa