INNER JOIN + ORDER BY Performance

[heinzler]

Hallo,

ich habe ein Problem mit einem Inner Join und Sortierung.

Hier schnell die Tabellen (ist vereinfacht, es geht hier nur um eine sinnbildhaftige Darstellung eines ähnlichen Problemes mit einer einfachen Datenstruktur)

Tabelle: article
- artId (int) - PRIMARY KEY
- name (varchar 250)
- date (DateTime) - INDEX


Tabelle: articleToCategory
- artId (int) - PRIMARY KEY
- catId (int)

Folgendes Problem: Ich mache folgende Abfrage

SELECT a.* from article a INNER JOIN articleToCategory b USING (artId)
ORDER BY a.date

Wie ihr seht, möchte ich alle artikel aufgelistet haben, welche irgendwie mit der articleToCategory Tabelle referenzieren.

Das funktioniert natürlich, ist aber langsam, und zwar nur wenn ich sortiere nach Datumsfeld.

Die Abfrage ohne ORDER BY date benötigt 0,0008 Sekunden, also Tipp Top.
Mache ich die Abfrage mit dem ORDER BY date benötigt diese 0,1 Sekunde - Sehr langsam. (auf einem DualCore 4400+ mit 2GB iRAM IDLE)

Da das ganze auch bei vielen Userzahlen skalieren soll ist natürlich 100 Millisekunden sehr schlecht.

Das ganze durch Explain geschleust bestätigt auch das Problem bzw. erklärt die Ursache:
1 SIMPLE article ALL artId NULL NULL NULL 22 Using temporary; Using filesort
1 SIMPLE a eq_ref PRIMARY PRIMARY 4 artId 1

Wie hier zu sehen ist, baut mySQL eine Temporäre Tabelle auf und macht einen Filesort.

Lasse ich das order by weg läuft das ganze um das 100fache schneller ab.

Jetzt die Frage: Wie kann ich dieses Query optimieren, sodass es auch mit dem Sortieren funktioniert?

Selektiere ich beispielsweise nur nach artikeln und sortiere nach datum dann verwendet mysql auch den Datums-Index für die Sorttierung.

Bsp:

select * from article order by date
Zeit: 0.0004 Sekunden
Index: date


vielen Danke für die Unterstützung,

mfg,
heinzler

PS: Die Artikel-Tabelle hat 250.000 Einträge, die artikelToCategory Tabelle hat über 18.000 Einträge.

 

[Revontulet]

Ich sehe nicht ganz, wozu du zwei Tabellen brauchst?
Du könntest doch auch die catId mit in die article-Tabelle packen, und dann deine Abfrage modifizieren, so z.B.

SELECT ...
FROM article
WHERE catId IS NOT NULL
ORDER BY date

Dann müsstest du zwar deine eine Tabelle modifizieren, aber das ist ja nicht so das große Problem. Oder habe ich da etwas falsch verstanden?

 

[KonKorT]

Ich empfehle einfach mal folgendes:

Zwing die Query dazu, den Datumsindex zu nutzen, indem Du FORCE INDEX nutzt.

http://mysql.org/doc/refman/5.1/de/how-to-avoid-table-scan.html

Alternativ könntest Du natürlich für diese eine Query auch EINEN Index auf ZWEI Spalten setzen, nämlich id und datum.

Des Weiteren bevorzuge ich es, bei INNER JOINs über die WHERE Klausel zu referenzieren, also:

SELECT a.artId, a.name, a.date FROM article a, articleToCategory b WHERE a.artId = b.artId ORDER BY a.date

 

[heinzler]

Ich sehe nicht ganz, wozu du zwei Tabellen brauchst?
Du könntest doch auch die catId mit in die article-Tabelle packen, und dann deine Abfrage modifizieren, so z.B.

SELECT ...
FROM article
WHERE catId IS NOT NULL
ORDER BY date

Dann müsstest du zwar deine eine Tabelle modifizieren, aber das ist ja nicht so das große Problem. Oder habe ich da etwas falsch verstanden?

Natürlich. Aber wie ich schon in meinem ursprünglichen beispiel geschrieben habe, ist das nur eine vereinfachte Darstellung.

In meinem Fall sieht es nämlich so aus: Ich habe die ArtikelTabelle und eine Artikel-Rechte Tabelle. (mit den Benutzergruppen). Jetzt muss ich z.b.: den artikel mit der rechte Tabell joinen und sehen ob ich zugriff habe.

z.b: select * from article a inner join articleToRight b using (artId) WHERE b.groupId = x
ORDER BY a.date

Wie du hier siehst würde ich alle Artikel selektieren in denen ich Zugriff habe (x wäre meine Gruppe).

Das habe ich aber in meiner ursprünglichen Frage weggelassen da das unwichtig ist. Es geht mir ja um den speed.

Ich empfehle einfach mal folgendes:

Zwing die Query dazu, den Datumsindex zu nutzen, indem Du FORCE INDEX nutzt.

http://mysql.org/doc/refman/5.1/de/how-to-avoid-table-scan.html

Alternativ könntest Du natürlich für diese eine Query auch EINEN Index auf ZWEI Spalten setzen, nämlich id und datum.

Force index habe ich verwendet. Die Abfrage wird dadurch aber nicht schneller. Er verwendet laut Explain dann zwar den 'date' index, mache aber z.B.: ein LIMIT 4000, 10 dann braucht die Abfrage schon 2 Sekunden.

Mit meinem ursprünglichen Query bleibt die Abfrage konstant bei 100 ms mit order BY und bei 4 ms ohne order BY, gleichgültig welches LIMIT ich setze.

Danke und gruß,
heinzler

PS: Ich finde "INNER JOIN und USING" schöner als "WHEREs .."

 

[KonKorT]

Na, dann probier das doch mal mit dem Doppel-Index. War eigentlich klar, dass es mit FORCE INDEX langsamer ist, weil sich MySQL eigentlich schon immer automatisch den besten INDEX heraussucht.

PS: Bei LEFT JOIN wird man ja schon gezwungen, die unübersichtliche Schreibweise zu verwenden, bei einem INNER JOIN kann man sie glücklicherweise umgehen

 

[heinzler]

Doppel-Index bringt leider keinen Geschwindigkeitsvorteile, es wird nur noch langsamer.

 

[ag3nt]

Also das du den Query beschleunigen kannst ist ziemlich unwahrscheinlich, zumindest bei deiner Datenstruktur, wie du sie verwendest und vorausgesetzt du willst dir tatsächlich alle Artikel anzeigen lassen.

Versuchen kannst du aber mal folgendes:

Tabelle: article
- artId (int) - PRIMARY KEY -> ändern in unique
- name (varchar 250)
- date (DateTime) - INDEX -> raus

->neuen PK hinzufügen mit den feldern ( date, artId ) in genau dieser Reihenfolge!

das gleiche Problem tritt dann natürlich wieder auf, wenn du nach einer anderen Spalte sortieren willst.

Aber warum willst du dir überhaupt alle Artikel ausgeben lassen?

 

[heinzler]

Naja ich will mir ja nicht alle Aritkel anzeigen lassen. Aber es sollen halt eine Übersicht sein.

Z.b:. die letzten 10 Artikel. Und dann soll es eine Navigation geben (z.B.: 10 Artikel pro Seite)

Hat jemand eine Idee für eine bessere Datenstruktur?

Die UNIQUE-PRIMARY-Key verschiebung hilft leider nicht.

 

[ag3nt]

Wenn du ohnehin nur die letzten 10 Artikel angezeigt haben willst, verstehe ich dein Problem nicht so ganz, denn du wirst ja kaum dafür alle Artikel aus der DB auslesen, und bei 10 Artikeln ist es von der Geschwindigkeit her irrelevant ob du sortierst oder nicht

 

[heinzler]

Es macht keinen unterschied ob ich ein LIMIT 0,10 mache. Er verwendet immer die temporäre Tabelle und das Filesort.

Das wird daran liegen, dass er alle Datensätze joined und diese dann in eine temp table ladet. Dannach kommt das Filesort.

In diesem Fall wäre logischer ein Sortieren nach dem date index und dann die joins und dann kann er ja abbrechen. Aber das scheint nicht zu funktionieren
??

 

[ag3nt]

Also das ist durchaus logisch, da ein LIMIT ja "nur" die Ausgabe der Datensätze selbst reduziert, aber zumindest bei Mysql nicht den Abfrageplan selbst verändert. Du musst daher an der Logik etwas ändern. Da ich mal nicht annehme das eine Reportausgabe erhalten möchtest mit einer kompletten Übersicht, der neuesten 10 Artikel in jeder Kategorie, wäre die erste Maßnahme schonmal das hinzufügen der gewünschten Kategorien zum Query.
Als 2. musst du die Datenstruktur ändern, da du derzeit beide Tabellen brauchst um festzustellen, wann ein Artikel eingetragen wurde. Sprich das Datumsfeld muss auch in die Zuordnungstabelle. Diese Denormalisierung kannst du z.B. über Trigger realisieren. Anschließend kannst du einen Index auf die Spalten catID, Datum (in dieser Reihenfolge, absteigende Sortierung) erstellen.

SELECT	*
FROM	article
WHERE	artId IN
		(
			SELECT	artId
			FROM	articleToCategory
			WHERE	catId = <CATID>
			ORDER BY date DESC
			LIMIT 0, 10
		)

 

[heinzler]

Danke für deine Unterstützung.

Leider ist das auch keine Patentlösung. Es müsste dann ja auch beispielsweise möglich sein nach Titel zu sortieren oder nach dem Ersteller. Und all diese Felder redundant zu halten ist denke ich auch nicht die beste Lösung.

Das größere Problem ist allerdings dass mir mySQL folgenden Fehler ausspuckt:

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Ich habe mYSQL 5.0.38