Kann ich diese MySQL-Anfrage optimieren?
Eddie
Kann ich diese MySQL-Anfrage optimieren?
Eddie
MudGuardHallo 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
--
Old men and far travelers may lie with authority.
Old men and far travelers may lie with authority.
-
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";
-
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
--
Old men and far travelers may lie with authority.-
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...
--
wer braucht schon großbuchstaben?
sh:( fo:# ch:# rl:° br:> n4:& ie:{ mo:} va:) de:] zu:} fl:{ ss:? ls:[ js:|-
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
--
Old men and far travelers may lie with authority.-
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...
--
wer braucht schon großbuchstaben?
sh:( fo:# ch:# rl:° br:> n4:& ie:{ mo:} va:) de:] zu:} fl:{ ss:? ls:[ js:|-
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
-
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...
--
wer braucht schon großbuchstaben?
sh:( fo:# ch:# rl:° br:> n4:& ie:{ mo:} va:) de:] zu:} fl:{ ss:? ls:[ js:|-
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
-
-
-
-
-
-
-
-
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ägeIch 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 = 6Ich 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 = 6UNION 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
-
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
-
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 = 6lauten 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
-
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
-
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
--
Old men and far travelers may lie with authority.
-
-
-
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 = 6Wow, 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.-
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 = 6komme 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-
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
--
Old men and far travelers may lie with authority. -
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--
Warum nennt sich Andreas hier MudGuard?
Schreinerei Waechter
Fachfragen per E-Mail halte ich für unverschämt und werde entsprechende E-Mails nicht beantworten. Für Fachfragen ist das Forum da.
-
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
-
-
-
-