0

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?

2
  • Add id to this index - it will become covering for your query which will improve performance avoiding access to the table body. Check what condition is more selective, and if the condition by created_at, then put it first in the index expression.
    – Akina
    Jun 23 '20 at 4:48
  • @Akina innodb already adds id to secondary index. That's not what question is about.
    – Jimbotron
    Jun 23 '20 at 14:23
0

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

1
  • Thank you. I'll have to find an alternative.
    – Jimbotron
    Jun 26 '20 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.