Necessary index

[sposs]

Hi, After a few months of usage, with >100000 issues, about 50 users, and a lot of activity, our server became very sluggish. I have investigated and saw that there was a query,

SELECT MAX(`version`) FROM `audits` WHERE `auditable_id` = 96 AND `auditable_type` = 'UserPreference';

which would take up to 20 seconds. As this looks frequently called, our mariadb was always working. To fix the issue, I've investigated and found that the existing index (auditable_index) was not used

MariaDB [redmine_production]> EXPLAIN SELECT MAX(`audits`.`version`) FROM `audits` WHERE `audits`.`auditable_id` = 96 AND `audits`.`auditable_type` = 'UserPreference';
+------+-------------+--------+------+-----------------+------+---------+------+---------+-------------+
| id   | select_type | table  | type | possible_keys   | key  | key_len | ref  | rows    | Extra       |
+------+-------------+--------+------+-----------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | audits | ALL  | auditable_index | NULL | NULL    | NULL | 1019400 | Using where |
+------+-------------+--------+------+-----------------+------+---------+------+---------+-------------+

Then, I've added manually the following index

CREATE INDEX auditable_index2 on audits (version, auditable_id, auditable_type);

and now (note that the new index is not used)

MariaDB [redmine_production]> EXPLAIN SELECT MAX(`version`) FROM `audits` WHERE `auditable_id` = 96 AND `auditable_type` = 'UserPreference';
+------+-------------+--------+------+-----------------+-----------------+---------+-------------+--------+-------------+
| id   | select_type | table  | type | possible_keys   | key             | key_len | ref         | rows   | Extra       |
+------+-------------+--------+------+-----------------+-----------------+---------+-------------+--------+-------------+
|    1 | SIMPLE      | audits | ref  | auditable_index | auditable_index | 581     | const,const | 509702 | Using where |
+------+-------------+--------+------+-----------------+-----------------+---------+-------------+--------+-------------+
1 row in set (0.001 sec)

As a result, the request is now taking less than a second.