Workaround for missing descending index feature in MySQL

Question

Hello!

We have a problem with our production environment which uses a modified version of jBPM that has support for priorities. Indices present are:

| 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      |         |

The problematic query:

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)

The query with PRIORITY_ sorted ascending instead:

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)

Googling around suggests that the solution to this is to add another column (REVERSEPRIORITY_) that contains the value of PRIORITY_ * -1 and index that one instead. This seems to me like a pretty ugly solution so I want to ask you folks if you have better ones!

Answer 1

-PRIORITY is the best solution.

However, you can use MySQL FORCE INDEX and STRAIGHT_JOIN to emulate SKIP SCAN:

SELECT  jt *
FROM    (
        SELECT  DISTINCT priority
        FROM    JBPM_TIMER
        ORDER BY
                priority DESC
        ) jtd
STRAIGHT_JOIN
        JBPM_TIMER jt FORCE INDEX (ix_JBPM_TIMER_priority_duedate)
ON      jt.priority >= jtd.priority
        AND jt.priority <= jtd.priority

You need to create an index on (priority, duedate):

CREATE INDEX ix_JBPM_TIMER_priority_duedate ON JBPM_TIMER (priority, duedate)

Note that unlike your original solution, this really is an ugly hack whose behavior can change in the future releases of MySQL.

I'm posting it here only as a workaround if you cannot change your schema.

Don't use it if there is a slightest chance of your MySQL being upgraded.