(MySQL 5.1.41) LIMIT-Klausel verändert Ausführungsreihenfolge

[Hopsel]

Hi alle!

Ich habe hier ein Problem mit einer SQL-Abfrage, das mein Verständnis übersteigt.

Um das Problem noch nachvollziehen zu können, habe ich es stark eingeschränkt.

Ausgangsituation:
-----------------
Es gibt die zwei Tabellen "stand" und "category", die in einer n:m-Beziehung stehen. Verknüpft werden diese beiden Tabellen über eine dritte Tabelle "rel_stand_category".

Eine SQL-Abfrage über "stand" soll über die GROUP_CONCAT-Funktion alle Kategorie-IDs zu den Ständen ermitteln:

SELECT  
  s.id,  
  GROUP_CONCAT(DISTINCT rsc.category_id SEPARATOR ',') AS category_ids  
FROM stands s  
INNER JOIN rel_stand_category rsc  
  ON s.id = rsc.stand_id  
GROUP BY s.id

Diese SQL-Abfrage gibt 234 Datensätze zurück.

Problem:
--------
Der Server schmiert bei der (komplexen) Version der Abfrage wiederholbar ab. Das Problem ist aber auch schon bei der hier beschriebenen Testabfrage ersichtlich.
Wenn ich die Query mit einer LIMIT-Klausel auf 250 Datensätze beschränke, gibt es keine Probleme. Man beachte, dass das Limit höher ist als die tatsächliche Anzahl der Ergebnisse.

Ein EXPLAIN gibt auch Aufschluss, warum die Abfrage mit Limit ohne Probleme funktioniert:

EXPLAIN der Query mit LIMIT-Klausel:
--------------------------------------------------------------------------------------------------------------------------------------------------------------
id  select_type   table    type     possible_keys           key           key_len   ref                    rows   Extra
--------------------------------------------------------------------------------------------------------------------------------------------------------------
1   SIMPLE        rsc      range    stand_id,category_id    category_id   4         NULL                   348    Using where; Using temporary; Using filesort
1   SIMPLE        s        eq_ref   PRIMARY                 PRIMARY       4         db_xyz.rsc.stand_id    1      Using where
--------------------------------------------------------------------------------------------------------------------------------------------------------------

EXPLAIN der Query ohne LIMIT-Klausel:
--------------------------------------------------------------------------------------------------------------------------------------------------------------
id  select_type   table    type     possible_keys           key           key_len   ref                    rows   Extra
--------------------------------------------------------------------------------------------------------------------------------------------------------------
1   SIMPLE        rsc      ALL      stand_id,category_id    NULL          NULL      NULL                   417    Using where; Using temporary; Using filesort
1   SIMPLE        s        eq_ref   PRIMARY                 PRIMARY       4         db_xyz.rsc.stand_id    1      Using where
--------------------------------------------------------------------------------------------------------------------------------------------------------------

Zur Verdeutlichung die EXPLAIN-Ergebnisse der ursprünglichen, komplexen Abfrage mit mehreren JOINs und einem GROUP_CONCAT, welches in der Abfrage ohne LIMIT, dem MySQL-Server ganz schön zu schaffen macht:
Mit LIMIT:
--------------------------------------------------------------------------------------------------------------------------------------------------------------
id  select_type   table    type     possible_keys           key           key_len   ref                    rows   Extra
--------------------------------------------------------------------------------------------------------------------------------------------------------------
1   SIMPLE        s        index    PRIMARY                 PRIMARY       4         NULL                   1      Using where
1   SIMPLE        u        eq_ref   PRIMARY                 PRIMARY       4         db_xyz.s.user_id       1      Using where; Using index
1   SIMPLE        sca      ref      stand_id,category_id    stand_id      4         db_xyz.s.id            2      Using where
1   SIMPLE        sco      ref      stand_id                stand_id      4         db_xyz.s.id            9      Using index
1   SIMPLE        sh       ref      stand_id                stand_id      4         db_xyz.s.id            8      Using index
--------------------------------------------------------------------------------------------------------------------------------------------------------------

Ohne LIMIT:
--------------------------------------------------------------------------------------------------------------------------------------------------------------
id  select_type   table    type     possible_keys           key           key_len   ref                    rows   Extra
--------------------------------------------------------------------------------------------------------------------------------------------------------------
1   SIMPLE        rsc      ALL      stand_id,category_id    NULL          NULL      NULL                   417    Using where; Using temporary; Using filesort
1   SIMPLE        s        eq_ref   PRIMARY                 PRIMARY       4         db_xyz.rsc.stand_id    1      Using where
1   SIMPLE        u        eq_ref   PRIMARY                 PRIMARY       4         db_xyz.s.user_id       1      Using where; Using index
1   SIMPLE        sco      ref      stand_id                stand_id      4         db_xyz.s.id            9      Using index
1   SIMPLE        sh       ref      stand_id                stand_id      4         db_xyz.s.id            8      Using index
--------------------------------------------------------------------------------------------------------------------------------------------------------------

Fragen:
------
Wieso verändert das bloße Weglassen der LIMIT-Klausel die Ausführung der SQL-Abfrage so massiv und was kann ich dagegen tun?

Hat jemand schon mal ein ähnliches Verhalten beobachtet oder kann mir sagen, wie ich das Problem umgehen kann?

MfG H☼psel

[Hopsel]

Hi Ingrid!

Und schon der erste Fehler in der Fragestellung:
Der Alias "sca" wurde für das Beispiel zu "rsc" umbenannt. In der dritten EXPLAIN-Tabelle müsste statt "sca" also ein "rsc" stehen.

MfG H☼psel

[Hopsel]

Hi alle!

Eine Lösung, die nur die SQL-Abfrage geringfügig ändert oder die MySQL-Datenbank/-Tabelle so konfiguriert, dass die Abfrage ohne Probleme ausgeführt wird, habe ich noch nicht gefunden.

Im [MySQL-Handbuch wird zwar darauf hingewiesen, dass die Benutzung von Limit den Ausführungsplan einer SELECT-Anweisung prinzipiell ändern kann, aber von den Nachteilen wird nichts erwähnt.

MfG H☼psel

[Hopsel]

Hi alle!

Eine Lösung, die nur die SQL-Abfrage geringfügig ändert oder die MySQL-Datenbank/-Tabelle so konfiguriert, dass die Abfrage ohne Probleme ausgeführt wird, habe ich noch nicht gefunden.

Im MySQL-Handbuch wird zwar darauf hingewiesen, dass die Benutzung von Limit den Ausführungsplan einer SELECT-Anweisung prinzipiell ändern kann, aber von den Nachteilen wird nichts erwähnt.

MfG H☼p~gerklickt - grrrr~sel