Kann ich diese MySQL-Anfrage optimieren?

[Eddie]

Hallo allerseits,

folgende MySQL-Anfrage scheint zu kompliziert zu sein, die Datenbank braucht ewig dafür:

=====================================
SELECT DISTINCT c.*
FROM contentTable c, rel_content_category1 rel1, rel_content_category2 rel2
WHERE
(
 c.id = rel1.contentID
 AND
 (rel1.catID = 'ki' OR
  rel1.catID = 'fm' OR
  rel1.catID = 'nr')
)

OR
(
 c.id = rel2.contentID
 AND
 rel2.catID = '6'
)

Dabei ist das ja eigentlich garnich so kompliziert!
Teile ich die Anfrage in zwei Anfragen, geht es ruckzuck:

=====================================
SELECT DISTINCT c.*
FROM contentTable c, rel_content_category1 rel1, rel_content_category2 rel2
WHERE
 c.id = rel1.contentID
 AND
 (rel1.catID = 'ki' OR
  rel1.catID = 'fm' OR
  rel1.catID = 'nr')

SELECT DISTINCT c.*
FROM contentTable c, rel_content_category1 rel1, rel_content_category2 rel2
WHERE
 c.id = rel2.contentID
 AND
 rel2.catID = '6'

Die in der WHERE-Klausel verwendeten Attribute haben alle einen Index, außerdem sind die Tabellen garnicht so riesig:
  contentTable c: 500 Einträge
  rel_content_category1 rel1: 7000 Einträge
  rel_content_category1 rel2: 500 Einträge

Ich versteh das nicht? Wo liegt der Haken?

Danke für eure Hilfe,
Eddie

[dedlfix]

echo $begrüßung;

folgende MySQL-Anfrage scheint zu kompliziert zu sein, die Datenbank braucht ewig dafür:

Was gibt denn ein EXPLAIN vor dem Statement aus?

Die in der WHERE-Klausel verwendeten Attribute haben alle einen Index

Einen Index zu haben heißt nicht automatisch, dass der auch genutzt wird.

echo "$verabschiedung $name";

[Eddie]

Hallo dedlfix,

Was gibt denn ein EXPLAIN vor dem Statement aus?

phpMyAdmin gibt Folgendes aus:

TABLE   TYPE   POSSIBLE_KEYS  KEY    KEY_LEN   REF    ROWS   EXTRA
----------------------------------------------------------------------------
rel1    ALL    id,catID       NULL   NULL      NULL   6973   Using temporary
rel2    ALL    id,catID       NULL   NULL      NULL    485   Using where
c       ALL    PRIMARY,id     NULL   NULL      NULL    776   Range checked for each record (index map: 33)

Eddie

[globe]

n'abend,

Was gibt denn ein EXPLAIN vor dem Statement aus?
phpMyAdmin gibt Folgendes aus:

TABLE   TYPE   POSSIBLE_KEYS  KEY    KEY_LEN   REF    ROWS   EXTRA

rel1    ALL    id,catID       NULL   NULL      NULL   6973   Using temporary
rel2    ALL    id,catID       NULL   NULL      NULL    485   Using where
c       ALL    PRIMARY,id     NULL   NULL      NULL    776   Range checked for each record (index map: 33)

na das sagt dir immerhin schon mal, dass keine Keys benutzt werden.
die anzahl der möglichen Zeilen in ROWS ist auch etwas arg inakkurat. der Optimzier weiss also gar nichts.. und arbeitet blind alles ab...

vergleich doch das mal mit den EXPLAINs der 2 geteilten queries...

weiterhin schönen abend...

[Eddie]

Hallo allerseits,

na das sagt dir immerhin schon mal, dass keine Keys benutzt werden.
die anzahl der möglichen Zeilen in ROWS ist auch etwas arg inakkurat. der Optimzier weiss also gar nichts.. und arbeitet blind alles ab...

vergleich doch das mal mit den EXPLAINs der 2 geteilten queries...

AHA! Das hat doch schonmal was gebracht:
die 2 geteilten Queries verwenden die Keys! Allerdings zeigt sich hier auch, wo das Problem liegen koennte.

Wenn ich naemlich in der zweiten Teilabfrage

SELECT DISTINCT c.*
FROM contentTable c, rel_content_category1 rel1, rel_content_category2 rel2
WHERE
c.id = rel2.contentID
AND
rel2.catID = '6'

"rel_content_category1 rel1"  rausnehme (aus der FROM-Klausel), dann sehen die Zahlen bei ROWS gleich viel vernuenftiger aus!
Es scheint also ein Problem zu sein, Tabellen zu nennen, sie dann aber nicht zu verwenden.

Stellt sich mir die Frage, wie ich das in der eigentlichen Abfrage realisieren kann? Dort wird ja mittels 'OR' jeweils eine der beiden rel-Tabellen nicht verwendet - und ich glaube, daran liegt es. Kann ich das irgendwie einbauen, ohne die Abfrage zu aendern?

Eddie

[globe]

n'abend,

"rel_content_category1 rel1"  rausnehme (aus der FROM-Klausel), dann sehen die Zahlen bei ROWS gleich viel vernuenftiger aus!
Es scheint also ein Problem zu sein, Tabellen zu nennen, sie dann aber nicht zu verwenden.

wo liegt der sinn eine tabelle (oder in deinem fall ein zwischenergebnis) mit einer weiteren tabelle zu joinen, wenn diese gar nicht gebraucht wird? die SQL Optimzier sind keine götter und erkennen sowas nicht immer.. also unnötigen kram rauslassen...

Stellt sich mir die Frage, wie ich das in der eigentlichen Abfrage realisieren kann? Dort wird ja mittels 'OR' jeweils eine der beiden rel-Tabellen nicht verwendet - und ich glaube, daran liegt es. Kann ich das irgendwie einbauen, ohne die Abfrage zu aendern?

Bedingungen die leichter abzuarbeiten sind, willst du an erster stelle haben!

SELECT DISTINCT c.*
FROM contentTable c, rel_content_category1 rel1, rel_content_category2 rel2
WHERE
(
 rel1.catID IN ('ki', 'fm', 'nr')
 AND
 c.id = rel1.contentID
)

OR
(
 rel2.catID = '6'
 AND
 c.id = rel2.contentID
)

auf diese art und weise sollten zuerst die zwischenergbnisse massiv reduziert werden und erst danach gejoined... dass das den ultimativen schub bringt bezweifle ich allerdings, wenn die indexe nicht benutzt werden...

statt der ORs hab ich das auf nen IN (..) umgeschrieben... IN () verarbeitet MySQL offenbar schneller, als mehrere ORs - ORs sind allgemein böhse und sind zu vermeiden, wo es geht... die kosten mehr zeit als ANDs. der gute demorgan hat da mal regeln für aufgestellt, wie man aus nem OR ein AND machen kann... die frage ist hier nur, ob das mit den beiden NOT-comparisons wirklich schneller läuft...

Hinweis: du kannst auch compund indexe benutzen (index (catID,contentID) )
bei rel1 könnte das dazu führen, dass er nen indexonly scan macht... du kannst mysql auch dazu "zwingen" gewisse indexe zu benutzen... wenn du aber nicht genau weisst was du da machst, solltes du lieber auf die magie des optimziers hoffen ;)

P.S.: falls ich hier irgendwas falsch oder unzureichend schildere möge man mich bitte verbessern... das buch über MySQL Query Optimziation ist schon wieder ziemlich lange her... :/

weiterhin schönen abend...

[Vinzenz Mai]

Hallo globe,

statt der ORs hab ich das auf nen IN (..) umgeschrieben... IN () verarbeitet MySQL offenbar schneller, als mehrere ORs - ORs sind allgemein böhse und sind zu vermeiden, wo es geht...

und genau aus diesem Grunde würde ich das zentrale OR durch UNION ersetzen.
Woher soll der Optimierer wissen, dass er im ersten Abschnitt alle Kombinationen mit rel2 vernachlässigen kann (es ist ja nirgendwo verboten) und im zweiten Abschnitt alle Kombinationen mit rel1 (genausowenig verboten). Infolgedessen gehe ich davon aus, dass wie ich bereits geschrieben habe tatsächlich alle möglichen ca. 2 Milliarden Kombinationen durchprobiert werden, ob die Bedingung(en) passen.

Voraussetzung für meine Lösung ist MySQL >= 4.0.0. Die enormen Unterschiede in der Funktionalität zwischen den einzelnen MySQL-Versionen erfordern es, dass man in vielen Fällen die Versionsnummer kennen muss, um zielgerichtet helfen zu können.

Freundliche Grüße

Vinzenz

[globe]

n'abend,

und genau aus diesem Grunde würde ich das zentrale OR durch UNION ersetzen.
Woher soll der Optimierer wissen, dass er im ersten Abschnitt alle Kombinationen mit rel2 vernachlässigen kann (es ist ja nirgendwo verboten) und im zweiten Abschnitt alle Kombinationen mit rel1 (genausowenig verboten). Infolgedessen gehe ich davon aus, dass wie ich bereits geschrieben habe tatsächlich alle möglichen ca. 2 Milliarden Kombinationen durchprobiert werden, ob die Bedingung(en) passen.

mit union habe ich mich bisher nicht weiter befasst... da alles was ich bislang über sql-optimziation von union abrät... gibt wohl nur wenige fälle wo das wirklich was bringt... 2 geteilte queries sind (laut buch) zu 70% aller fälle schneller als so ein union dings... (warum keine ahnung... steht da aber so...)

weiterhin schönen abend...

[Vinzenz Mai]

Hallo

mit union habe ich mich bisher nicht weiter befasst... da alles was ich bislang über sql-optimziation von union abrät... gibt wohl nur wenige fälle wo das wirklich was bringt... 2 geteilte queries sind (laut buch) zu 70% aller fälle schneller als so ein union dings

Was nutzen Dir zwei geteilte Ergebnisse, wenn Du nur ein gemeinsames benötigst? Da liegt doch die Krux. Bringe diese beiden Ergebnisse zusammen.
In diesem speziellen Fall mit den starken Einschränkungen über die WHERE-Klauseln sollten die Ergebnismengen beider Abfragen sehr klein und UNION somit sehr performant sein.

Zusätzlich empfiehlt MySQL sogar die Verwendung von UNION als effizientes Mittel in speziellen Fällen, siehe http://dev.mysql.com/doc/refman/5.0/en/searching-on-two-keys.html. Ich wüsste nicht, was in Eddies Fall gegen UNION spricht, es sei denn die MySQL-Version.

Freundliche Grüße

Vinzenz

[Vinzenz Mai]

Hallo Eddie,

folgende MySQL-Anfrage scheint zu kompliziert zu sein, die Datenbank braucht ewig dafür:

=====================================
SELECT DISTINCT c.*
FROM contentTable c, rel_content_category1 rel1, rel_content_category2 rel2
WHERE
(
c.id = rel1.contentID
AND
(rel1.catID = 'ki' OR
  rel1.catID = 'fm' OR
  rel1.catID = 'nr')
)

OR
(
c.id = rel2.contentID
AND
rel2.catID = '6'
)

Dabei ist das ja eigentlich garnich so kompliziert!

Es ist nicht kompliziert, es ist umfangreich. Du betrachtest hier das kartesische Produkt von drei Tabellen.

außerdem sind die Tabellen garnicht so riesig:
  contentTable c: 500 Einträge
  rel_content_category1 rel1: 7000 Einträge
  rel_content_category1 rel2: 500 Einträge

Ich versteh das nicht? Wo liegt der Haken?

Somit muss das DBMS ca. 500 x 7000 x 500 Kombinationen, also ungefähr 1.750.000.000 durcharbeiten. Du wunderst Dich, dass das lange dauert?

Teile ich die Anfrage in zwei Anfragen, geht es ruckzuck:

=====================================
SELECT DISTINCT c.*
FROM contentTable c, rel_content_category1 rel1, rel_content_category2 rel2
WHERE
c.id = rel1.contentID
AND
(rel1.catID = 'ki' OR
  rel1.catID = 'fm' OR
  rel1.catID = 'nr')

Ja, hier hast Du einen (impliziten) Join. Viel schöner formulierst Du dies mit

  
SELECT DISTINCT c.*  
FROM contentTable c,  
INNER JOIN rel_content_category1 rel1  
ON c.id = rel1.contentID  
WHERE rel1.catID IN ('ki', 'fm', nr')

für die folgende gilt das Gleiche, es ist ein Join vorhanden:

=====================================
SELECT DISTINCT c.*
FROM contentTable c, rel_content_category1 rel1, rel_content_category2 rel2
WHERE
c.id = rel2.contentID
AND
rel2.catID = '6'

  
SELECT DISTINCT c.*  
FROM contentTable c,  
INNER JOIN rel_content_category1 rel2  
ON c.id = rel2.contentID  
WHERE rel1.catID = 6

Ich versteh das nicht? Wo liegt der Haken?

Bei der von Dir verwendeten MySQL-Version, die Du hättest angeben sollen. UNION bietet sich hier an:

  
SELECT DISTINCT c.*  
FROM contentTable c,  
INNER JOIN rel_content_category1 rel1  
ON c.id = rel1.contentID  
WHERE rel1.catID IN ('ki', 'fm', nr')  
UNION  
SELECT DISTINCT c.*  
FROM contentTable c,  
INNER JOIN rel_content_category1 rel2  
ON c.id = rel2.contentID  
WHERE rel1.catID = 6

UNION gibt es in MySQL ab Version 4.0.0, die Wahrscheinlichkeit, dass Dir UNION zur Verfügung steht ist somit recht hoch.

Weiterhin lege ich Dir die Lektüre der Beta-Versionen der Feature-Artikel von Rouven und mir ans Herz.

Freundliche Grüße

Vinzenz

[Ilja]

yo,

Es ist nicht kompliziert, es ist umfangreich. Du betrachtest hier das kartesische Produkt von drei Tabellen.

kartesische produkt ist die verbindung jedes datensatzes der einen tabelle mit jedem datensatz der anderen tabelle. diese beiden bedingungen...

c.id = rel1.contentID
c.id = rel2.contentID

... scheinen aber nur bestimmte datensätze der tabelle c und re1, bzw. rel2 miteinander zu verknüpfen. insofern kann es sich nicht um ein kartesiches produkt handeln.

Ilja

[Vinzenz Mai]

Hallo Ilja,

... scheinen aber nur bestimmte datensätze der tabelle c und re1, bzw. rel2 miteinander zu verknüpfen. insofern kann es sich nicht um ein kartesiches produkt handeln.

in diesem Falle doch, weil die beiden einschränkenden Verknüpfungen über ODER miteinander verknüpft sind, nicht mit UND. Ich denke nicht, dass hier der Optimierer ohne Nachhilfe etwas ausrichtet. Genau aus diesem Grund empfehle ich die UNION, da kann dieses Problem von vornherein nicht existieren.

Bei meinen Ausführungen hat sich übrigens ein (Copy&Paste-) Fehler eingeschlichen. Bei der zweiten Abfrage muss die WHERE-Bedingung selbstverständlich

WHERE rel2.catID = 6 lauten und nicht rel1.catID.

Du gibst mir jedoch recht, dass der Einsatz von UNION das Performanceproblem dieser Abfrage beheben sollte. Welche MySQL-Version Eddie verwendet, konnte ich trotz intensiver Recherche nicht herausfinden. Im Februar 2004 war es jedoch zumindest eine Version, die Subselects nicht unterstützt.

Freundliche Grüße

Vinzenz

[Ilja]

yo,

in diesem Falle doch, weil die beiden einschränkenden Verknüpfungen über ODER miteinander verknüpft sind, nicht mit UND.

nein, auch durch das oder werden nicht alle datensätze der einen tabelle mit allen datensätze der anderen tabellen verknüpft. kommt ein datensatz vor, der nicht einen der beiden ODER bedingungen entspricht, dann wird keie verknüpfung der beiden datensätze gebildet und somit kann es kein kartesiches produkt sein.

Du gibst mir jedoch recht, dass der Einsatz von UNION das Performanceproblem dieser Abfrage beheben sollte.

um ehrlich zu sein, habe ich in die problematik bis jetzt nur reingeschnuppert. aber UNION ist sicherlich ein gehbarer weg. aber wie immer gilt bei tuning, probieren geht über studieren...

Ilja

[Eddie]

Hallo Vinzenz,

Welche MySQL-Version Eddie verwendet, konnte ich trotz intensiver Recherche nicht herausfinden. Im Februar 2004 war es jedoch zumindest eine Version, die Subselects nicht unterstützt.

:-)

Du hast natuerlich Recht! Der Vollstaendig halber, obwohl UNION funzt: 4.0.25

Eddie

[Eddie]

Hallo Vinzenz,

Somit muss das DBMS ca. 500 x 7000 x 500 Kombinationen, also ungefähr 1.750.000.000 durcharbeiten. Du wunderst Dich, dass das lange dauert?

Oh-oh! :-/

UNION bietet sich hier an:

SELECT DISTINCT c.*
FROM contentTable c,
INNER JOIN rel_content_category1 rel1
ON c.id = rel1.contentID
WHERE rel1.catID IN ('ki', 'fm', nr')
UNION
SELECT DISTINCT c.*
FROM contentTable c,
INNER JOIN rel_content_category1 rel2
ON c.id = rel2.contentID
WHERE rel1.catID = 6

  
Wow, ja, das war's!!!  
Jetzt kann man wieder auf folgende Seite zugreifen:  
<http://www.umdiewelt.de/Australien-und-Ozeanien/Reiseziel-6.html>  
Jedenfalls vorausgesetzt, der Server steht sonst nicht zu sehr unter Last - ich arbeite dran :-)  
  
Einen riesen Dank, du hast mir sehr geholfen!!! Das UNION wird glaubich noch an anderen Ecken gute Dienste leisten :-)  
  

> Weiterhin lege ich Dir die Lektüre der Beta-Versionen der Feature-Artikel von [Rouven](http://aktuell.de.selfhtml.org/artikel/review/datenbanken/joins/) und [mir](http://aktuell.de.selfhtml.org/artikel/review/datenbanken/fortgeschrittene-joins/) ans Herz.  

Liegt in der Druckschlange, das les ich offline. Irgendwie habe ich den Eindruck, dass ich mit der bisherigen Vermeidung von JOINS keinen so guten Weg gegangen bin...  
  
Also danke nochmal!  
Eddie  

-- 
Old men and far travelers may lie with authority.

[Ilja]

yo,

UNION bietet sich hier an:

SELECT DISTINCT c.*
FROM contentTable c,
INNER JOIN rel_content_category1 rel1
ON c.id = rel1.contentID
WHERE rel1.catID IN ('ki', 'fm', nr')
UNION
SELECT DISTINCT c.*
FROM contentTable c,
INNER JOIN rel_content_category1 rel2
ON c.id = rel2.contentID
WHERE rel1.catID = 6

  
komme glaube wieder mal zu spät, aber falls du hier noch mal reinschaust. der operator UNION führt immer ein "automatisches" DISTINCT aus, sprich es werden doppelte datensätze nicht angezeigt.  
  
nun bin ich mir nicht ganz sicher, ob man sich nun die beiden DISTINCT anweisung der einzelnen abfragen sparen kann, sollte aber eigentlich zum gleichen ergebnis führen und man hat wieder an performance gewonnen, weil man zwei zusätzliche sortierungen einsparen konnte. eventuell hat das der optimierer aber schon gemacht.  
  
wie auch immer, es ist ein versuch wert und vielleicht kannst du uns über das ergebnis berichten....  
  
Ilja

[Eddie]

Hallo Ilja,

komme glaube wieder mal zu spät, aber falls du hier noch mal reinschaust. der operator UNION führt immer ein "automatisches" DISTINCT aus, sprich es werden doppelte datensätze nicht angezeigt.

Praktisch!

nun bin ich mir nicht ganz sicher, ob man sich nun die beiden DISTINCT anweisung der einzelnen abfragen sparen kann, sollte aber eigentlich zum gleichen ergebnis führen und man hat wieder an performance gewonnen, weil man zwei zusätzliche sortierungen einsparen konnte.

eventuell hat das der optimierer aber schon gemacht.

Nein, hat er nicht. Es spart tatsaechlich Zeit, bei obiger Abfrage konnte ich nochmal ungefähr 40% rausholen. Danke!

Eddie

[MudGuard]

Hi,

der operator UNION führt immer ein "automatisches" DISTINCT aus, sprich es werden doppelte datensätze nicht angezeigt.

Nur der Vollständigkeit halber:
Nicht immer - nur dann, wenn kein ALL dahintersteht - UNION und UNION DISTINCT sind in MySQL äquivalent, UNION ALL aber nicht ...

cu,
Andreas

[Ilja]

yo,

Nicht immer - nur dann, wenn kein ALL dahintersteht - UNION und UNION DISTINCT sind in MySQL äquivalent, UNION ALL aber nicht ...

UNION ALL ist auch ein anderer operator als UNION, schließlich kann man nicht äpfel mit birnen vergleichen, obwohl beides obst ist. die aussage stimmt schon so, UNION führt immer ein automatisch DISTINCT aus. wenn man einen anderen operator benutzt, dann kommen natürlich andere ergebnissee heraus. das ist ja schließlich auch der sinn der sache, dass es dabei unterschiede gibt.

Ilja