resource_history needs an index

[thomasgoirand]

Hi,

We had a huge load on our MariaDB cluster because of queries that were taking really too much time. We tackled the issue by adding indexes. We were not sure which one, so we added 4 indexes. Here's what we have now:

MariaDB [gnocchidb]> SHOW INDEX FROM resource_history;
+------------------+------------+---------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+------------------+------------+---------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| resource_history | 0 | PRIMARY | 1 | revision | A | 1198959 | NULL | NULL | | BTREE | | | NO |
| resource_history | 1 | fk_rh_id_resource_id | 1 | id | A | 1198959 | NULL | NULL | | BTREE | | | NO |
| resource_history | 1 | idx_type_project_revision | 1 | type | A | 1745 | NULL | NULL | | BTREE | | | NO |
| resource_history | 1 | idx_type_project_revision | 2 | project_id | A | 66608 | NULL | NULL | YES | BTREE | | | NO |
| resource_history | 1 | idx_type_project_revision | 3 | revision_start | A | 1198959 | NULL | NULL | | BTREE | | | NO |
| resource_history | 1 | idx_type_project_revision | 4 | revision_end | A | 1198959 | NULL | NULL | | BTREE | | | NO |
+------------------+------------+---------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

This should IMO be added to the migration scripts.