Add/Remove Indexes - Metadata Table

[kaysiz]

Purpose

We have an incomplete index that is causing the query to do a filesort in the filter. The first part of the query uses index to fetch record but other part is now using filesort.

=> EXPLAIN for: SELECT `metadata`.* FROM `metadata` WHERE `metadata`.`dataset` = 38 ORDER BY created DESC
+----+-------------+----------+------------+------+------------------------------------+-----------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table    | partitions | type | possible_keys                      | key             | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+----------+------------+------+------------------------------------+-----------------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | metadata | NULL       | ref  | dataset_version,FKE52D7B2F4D3D6B1B | dataset_version | 8       | const |    7 |    100.0 | Using index condition; Using filesort |
+----+-------------+----------+------------+------+------------------------------------+-----------------+---------+-------+------+----------+---------------------------------------+

closes: #1125

Approach

• Add a composite index that will cover both parts of the query

  => EXPLAIN for: SELECT `metadata`.* FROM `metadata` WHERE `metadata`.`dataset` = 38 ORDER BY created DESC
  +----+-------------+----------+------------+------+-------------------------------------------------------------------+--------------------------------+---------+-------+------+----------+-------------+
  | id | select_type | table    | partitions | type | possible_keys                                                     | key                            | key_len | ref   | rows | filtered | Extra       |
  +----+-------------+----------+------------+------+-------------------------------------------------------------------+--------------------------------+---------+-------+------+----------+-------------+
  |  1 | SIMPLE      | metadata | NULL       | ref  | dataset_version,FKE52D7B2F4D3D6B1B,index_metadata_dataset_created | index_metadata_dataset_created | 8       | const |    7 |    100.0 | Using where |
  +----+-------------+----------+------------+------+-------------------------------------------------------------------+--------------------------------+---------+-------+------+----------+-------------+

• Refactor Metadata related queries

Open Questions and Pre-Merge TODOs

Learning

Types of changes

• [ ] Bug fix (non-breaking change which fixes an issue)

• [ ] New feature (non-breaking change which adds functionality)

• [ ] Breaking change (fix or feature that would cause existing functionality to change)

Reviewer, please remember our guidelines:

• Be humble in the language and feedback you give, ask don't tell.
• Consider using positive language as opposed to neutral when offering feedback. This is to avoid the negative bias that can occur with neutral language appearing negative.
• Offer suggestions on how to improve code e.g. simplification or expanding clarity.
• Ensure you give reasons for the changes you are proposing.