Fixed Poor index condition on getExpiredWarnings

[Xon]

While troubleshooting some stalls, I noticed the query condition for the getExpiredWarnings (around the time of the stalls, but unrelated) has poor index usage:

   SELECT *
   FROM xf_warning
   WHERE expiry_date < ?
    AND expiry_date > 0
    AND is_expired = 0

The standard index is just on the expiry_date column, and gives the following query plan:

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'xf_warning', 'ALL', 'expiry', NULL, NULL, NULL, '12755', 'Using where'

Using a multi-column index (is_expired & expiry_date) gives the following query plan:
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'xf_warning', 'range', 'expiry', 'expiry', '5', NULL, '1', 'Using index condition'

Spoiler: SQL

ALTER TABLE `xf_warning`
DROP INDEX `expiry` ,
ADD INDEX `expiry` (`expiry_date` ASC);

explain
SELECT *
FROM xf_warning
WHERE expiry_date < 1484933945
 AND expiry_date > 0
 AND is_expired = 0;

ALTER TABLE `xf_warning`
DROP INDEX `expiry` ,
ADD INDEX `expiry` (`is_expired` ASC, `expiry_date` ASC);

explain
SELECT *
FROM xf_warning
WHERE expiry_date < 1484933945
 AND expiry_date > 0
 AND is_expired = 0;

 

[Mike]

I've replaced this index for the next release now. Thanks!