Mysql – How to design a table with greater than in where statement and ordering to avoid filesort

indexMySQLperformance

I have a list of documents that I store along with creation date and rank:

CREATE TABLE `document` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` INT UNSIGNED NOT NULL,
  `rank` INT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_combo` (`category_id` ASC, `created_at` ASC, `rank` ASC)
) ENGINE=InnoDB;

I would like to get a list of 10 documents that belong to category 30, were created_at is after certain point in time and order it by rank:

EXPLAIN
SELECT
    id
FROM
    docs.document
WHERE
    category_id = 30
    AND created_at > 1592862179
ORDER BY
    rank
LIMIT 10

It's not surprising that this query has to use filesort:

+------+-------------+----------+------+---------------+-----------+---------+-------+-------+------------------------------------------+
| id   | select_type | table    | type | possible_keys | key       | key_len | ref   | rows  | Extra                                    |
+------+-------------+----------+------+---------------+-----------+---------+-------+-------+------------------------------------------+
|    1 | SIMPLE      | document | ref  | idx_combo     | idx_combo | 4       | const | 19596 | Using where; Using index; Using filesort |
+------+-------------+----------+------+---------------+-----------+---------+-------+-------+------------------------------------------+

If the table is large and I have many records for many years and want to retrieve data for specific date range, filesort becomes performance issue. I would like to be able to retrieve results directly from the index. Is there a way to redesign the table, index or the select query for mysql to only use the index for retrieval and avoid temp tables or filesort?

Best Answer

Can't be done.

To achieve the goal, you would need a 2-dimensional index. Such does not exist.

WHERE
    category_id = 30
    AND created_at > 1592862179

That much is handled nicely with INDEX(category_id, created_at). But adding anything onto the end of the index definition will not benefit you

The only way to get the index to be also useful for the ORDER BY is to have ORDER BY created_at (either ASC or DESC). At that point, it can stop after 10 rows (because of LIMIT 10).

More on indexing: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Related Question