Warum verwendet MySQL den falschen Index?
ich habeEin weiterer Frage zur Optimierung von MySQL-Indizes für unsere Priorisierung von jBPM. Die relevanten Indizes sehen folgendermaßen aus:
| JBPM_TIMER | 1 | JBPM_TIMER_REVERSEPRIORITY__DUEDATE_ | 1 | REVERSEPRIORITY_ | A | 17 | NULL | NULL | YES | BTREE | |
| JBPM_TIMER | 1 | JBPM_TIMER_REVERSEPRIORITY__DUEDATE_ | 2 | DUEDATE_ | A | 971894 | NULL | NULL | YES | BTREE | |
| JBPM_TIMER | 1 | JBPM_TIMER_DUEDATE_ | 1 | DUEDATE_ | A | 971894 | NULL | NULL | YES | BTREE | |
JBPM stellt beim Abrufen von Timern zwei Fragen. Der erste ist abhängig vom kombinierten Index (umgekehrte Priorität und Datum) und der zweite vom Solo-Datumsindex. Wenn Sie den Solo-Index hinzufügen, hat er jedoch Vorrang vor dem richtigen Index, wenn Sie diese Abfrage ausführen:
mysql> explain select timer0_.ID_ as col_0_0_ from JBPM_TIMER timer0_ where timer0_.ISSUSPENDED_<>1 and timer0_.DUEDATE_<='2009-08-17 14:51:06' order by timer0_.REVERSEPRIORITY_ asc, timer0_.DUEDATE_ asc limit 160;
+----+-------------+---------+-------+---------------------------+---------------------------+---------+------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------------------+---------------------------+---------+------+-------+-----------------------------+
| 1 | SIMPLE | timer0_ | range | JBPM_TIMER_DUEDATE_ | JBPM_TIMER_DUEDATE_ERIK_T | 9 | NULL | 971894| Using where; Using filesort |
+----+-------------+---------+-------+---------------------------+---------------------------+---------+------+-------+-----------------------------+
1 row in set (0.00 sec)
Dieser Index wird für eine andere Abfrage benötigt:
mysql> explain select timer0_.ID_ as col_0_0_ from JBPM_TIMER timer0_ where (timer0_.EXCEPTION_ is null) and timer0_.ISSUSPENDED_<>1 order by timer0_.DUEDATE_ asc limit 160;
+----+-------------+---------+-------+---------------+---------------------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------------------+---------+------+-------+-------------+
| 1 | SIMPLE | timer0_ | index | NULL | JBPM_TIMER_DUEDATE_ | 9 | NULL | 24249 | Using where |
+----+-------------+---------+-------+---------------+---------------------+---------+------+-------+-------------+
1 row in set (0.00 sec)
Wenn Sie den Solo-Index entfernen, wird Abfrage Nr. 1 korrekt ausgeführt und Abfrage 2 benötigt eine Dateisortierung. Das Hinzufügen der Solo-Index-Abfrage Nummer 2 wird korrekt ausgeführt und Abfrage 1 benötigt eine Dateisortierung.
Dieses unerwünschte Verhalten kann durch Hinzufügen eines Indexhinweises zur ersten Abfrage überschrieben werden:
explain select timer0_.ID_ as col_0_0_
from JBPM_TIMER timer0_ USE INDEX (JBPM_TIMER_REVERSEPRIORITY__DUEDATE_)
where timer0_.ISSUSPENDED_<>1 and
timer0_.DUEDATE_<='2009-08-17 14:51:06'
order by timer0_.REVERSEPRIORITY_ asc, timer0_.DUEDATE_ asc
limit 160;
Ist der Hinweis der einzige Weg, um MySQL beide Abfragen korrekt optimieren zu lassen? Oder machen wir etwas falsch?