Por que o MySQL usa o índice errado?
eu tenhooutro pergunta sobre como otimizar os índices do mysql para priorizar o jBPM. Os índices relevantes são assim:
| 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 | |
O JBPM faz duas perguntas ao recuperar temporizadores. O primeiro depende do índice combinado (prioridade invertida e duedate) e o segundo do índice individual. No entanto, ao adicionar o índice solo, ele tem precedência sobre o correto ao executar esta consulta:
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)
Este índice é necessário para outra consulta:
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)
Ao remover o índice de solo, o número de consulta 1 é executado corretamente e a consulta 2 precisa de um backup de arquivo. A adição do número de consulta do índice 2 individual é executada corretamente e a consulta 1 precisa de um repositório de arquivos.
Esse comportamento indesejado pode ser substituído pela adição de uma dica de índice à primeira consulta:
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;
A dica é a única maneira de fazer o MySQL otimizar as duas consultas corretamente? Ou estamos fazendo algo errado?