Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an images table with approximately 5 million rows in it. For the purposes of this question lets say the table looks like this:

CREATE TABLE `images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stack_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_images_on_stack_id` (`stack_id`),
  KEY `index_images_on_created_at` (`created_at`)
);

An image can be part of a stack of images, which is an ordered set of images (position column omitted in this example). Each image in a stack has the id of the root image in the stack_id column.

The following query, which has the intention of only returning root images is taking 3–4 seconds to execute. Ideally it would take less than 100ms:

SELECT images.* FROM images
WHERE id = stack_id
ORDER BY images.created_at DESC
LIMIT 8 OFFSET 4111;

Query plan is as follows:

+----+-------------+--------+-------+---------------+----------------------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key                        | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+----------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | images | index | NULL          | index_images_on_created_at | 9       | NULL | 4119 | Using where |
+----+-------------+--------+-------+---------------+----------------------------+---------+------+------+-------------+

Note that it doesn't use the stack_id index. I presume the index is of no use as the value to look up in the index changes for every row.

Are there any suggestions for how to improve the performance of this query?

The obvious option is to add a boolean stack_root column to the table that tracks this property, which means the query would simply be able to use that column (and associated index). Before doing that though I thought I'd check if there was anything I'd missed.

Update @Phil suggested an index on id and stack_id, which I have already tried. MySQL still doesn't use such an index. Hinting with `USE INDEX (index_images_on_id_and_stack_id) does not help either:

EXPLAIN SELECT `images`.* FROM `images` USE INDEX (index_images_on_id_and_stack_id)
    -> WHERE id = stack_id
    -> ORDER BY images.created_at DESC
    -> LIMIT 8 OFFSET 5111;
+----+-------------+--------+------+---------------+------+---------+------+---------+-----------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra                       |
+----+-------------+--------+------+---------------+------+---------+------+---------+-----------------------------+
|  1 | SIMPLE      | images | ALL  | NULL          | NULL | NULL    | NULL | 5488884 | Using where; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+---------+-----------------------------+
1 row in set (0.00 sec)
share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.