Solución para la característica de índice descendente faltante en MySQL
Tenemos un problema con nuestro entorno de producción que utiliza una versión modificada de jBPM que tiene soporte para prioridades. Los índices presentes son:
| JBPM_TIMER | 1 | JBPM_TIMER_DUEDATE__PRIORITY_ | 1 | PRIORITY_ | A | 2 | NULL | NULL | YES | BTREE | |
| JBPM_TIMER | 1 | JBPM_TIMER_DUEDATE__PRIORITY_ | 2 | DUEDATE_ | A | 51 | NULL | NULL | YES | BTREE | |
La consulta problemática:
mysql> explain select * from JBPM_TIMER where PRIORITY_ < 0 order by PRIORITY_ ASC, DUEDATE_ desc;
+----+-------------+------------+-------+-------------------------------+-------------------------------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+-------------------------------+-------------------------------+---------+------+------+-----------------------------+
| 1 | SIMPLE | JBPM_TIMER | range | JBPM_TIMER_DUEDATE__PRIORITY_ | JBPM_TIMER_DUEDATE__PRIORITY_ | 5 | NULL | 10 | Using where; Using filesort |
+----+-------------+------------+-------+-------------------------------+-------------------------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
La consulta con PRIORITY_ ordenada ascendente en su lugar:
mysql> explain select * from JBPM_TIMER where PRIORITY_ < 0 order by PRIORITY_ ASC, DUEDATE_ asc;
+----+-------------+------------+-------+-------------------------------+-------------------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+-------------------------------+-------------------------------+---------+------+------+-------------+
| 1 | SIMPLE | JBPM_TIMER | range | JBPM_TIMER_DUEDATE__PRIORITY_ | JBPM_TIMER_DUEDATE__PRIORITY_ | 5 | NULL | 10 | Using where |
+----+-------------+------------+-------+-------------------------------+-------------------------------+---------+------+------+-------------+
1 row in set (0.00 sec)
Buscar en Google sugiere que la solución a esto es agregar otra columna (REVERSEPRIORITY_) que contenga el valor de PRIORITY_ * -1 e indexarla. Esto me parece una solución bastante fea, ¡así que quiero preguntarle a la gente si tiene mejores!