adding ORDER BY decreases speed by 15x

Question

I have a simply query:

SELECT DISTINCT images.* 
FROM   `images` 
WHERE  `images`.`user_id` IN (SELECT following_id 
                                FROM   `follows` 
                                WHERE  `follows`.`follower_id` = 9 
                                       AND `follows`.`accepted` = 1) 
LIMIT  15 offset 0 

This executes in less than 1 ms.

Adding an ORDER BY on column that is indexed:

    SELECT DISTINCT images.* 
    FROM   `images` 
    WHERE  `images`.`user_id` IN (SELECT following_id 
                                    FROM   `follows` 
                                    WHERE  `follows`.`follower_id` = 9 
                                           AND `follows`.`accepted` = 1) 
ORDER  BY id DESC 
LIMIT  15 offset 0 

takes 15 ms to execute. There are a mere 7,000 images in the table. Should it really take 15 fold time to order an indexed column? What can I do to decrease such a performance hit?

Running on:

Server version: 5.6.11-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

EXPLAIN:

+----+--------------+-------------+-------------+-----------------------------------+----------------------+---------+--------------------------+------+--------------------------------------------------------------+
| id | select_type  | table       | type        | possible_keys                     | key                  | key_len | ref                      | rows | Extra                                                        |
+----+--------------+-------------+-------------+-----------------------------------+----------------------+---------+--------------------------+------+--------------------------------------------------------------+
|  1 | SIMPLE       | <subquery2> | ALL         | NULL                              | NULL                 | NULL    | NULL                     | NULL | Using where; Using temporary; Using filesort                 |
|  1 | SIMPLE       | images    | ref         | user_id                           | user_id              | 5       | <subquery2>.following_id |    3 | NULL                                                         |
|  2 | MATERIALIZED | follows     | index_merge | accepted,follower_id,following_id | follower_id,accepted | 5,2     | NULL                     |   19 | Using intersect(follower_id,accepted); Using where; Distinct |
+----+--------------+-------------+-------------+-----------------------------------+----------------------+---------+--------------------------+------+--------------------------------------------------------------+

Thanks

Answer 1

Should it really take 15 fold time to order an indexed column?

Well it depends where this column is being used. Just because it's indexed (and presumably in some order) doesn't mean that using it in a query will magically make it fast.

Assume there are two indexes in the table, for id (ordered by id) and for user_id (ordered by user_id). If MySQL uses the latter to quickly look up the rows that satisfy the WHERE predicate then the result is that it might have all the rows in user_id order.

You want them in id order, so MySQL has to order them. Your previous query didn't have to do ordering. It should come as no surprise that more work == more time.

What can I do to decrease such a performance hit?

Look at the execution plan. MySQL has EXPLAIN.

You should also make your queries as simple as you can. I assume your images table has a primary key, so the DISTINCT in SELECT DISTINCT images.* FROM images ... doesn't really make sense.

Also by taking all the columns (images.*) you're forcing MySQL to acquire every attribute for each row that is returned, whereas being more selective (no pun intended) with your columns might result in less data needing to be returned. Simplifying somewhat: less columns gives MySQL an opportunity to satisfy the query using only small indexes, rather than the table data.

Answer 2

The problem is likely that the index on id sorts in the ascending direction, but your ORDER BY is asking for the data in the descending direction. Your ORDER BY has to ask for the data in DESC direction so that the LIMIT clause will give you the rows you want.

I do not know whether MY SQL supports descending indexes or not. If it does, try adding a descending index on id and see what that does to your performance. In addition, the comments warning you against predicting performance in production based on tests with small sample data are good ones. You need to conduct a mre realistic test before you can predict real world preformance.