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 ran into a very weird query performance situation. If I run this query without order by, it finish in millsecs, with order by, it ends up with more than 4mins.

 EXPLAIN 
 SELECT `book_book`.`id`, `book_book`.`title`, `book_book`.`author`, `book_book`.`price`, `book_book`.`nump`, `book_book`.`raw_data` 
 FROM `book_book` 
 INNER JOIN `book_tagbook` 
 ON (`book_book`.`id` = `book_tagbook`.`book_id`) 
 WHERE `book_tagbook`.`tag_id` = 1  LIMIT 9 OFFSET 1;

+----+-------------+--------------+--------+---------------------------------------------+-----------------------+---------+-----------------------------+--------+-------+
| id | select_type | table        | type   | possible_keys                               | key                   | key_len | ref                         | rows   | Extra |
+----+-------------+--------------+--------+---------------------------------------------+-----------------------+---------+-----------------------------+--------+-------+
|  1 | SIMPLE      | book_tagbook | ref    | book_tagbook_3747b463,book_tagbook_752eb95b | book_tagbook_3747b463 | 4       | const                       | 116394 |       |
|  1 | SIMPLE      | book_book    | eq_ref | PRIMARY                                     | PRIMARY               | 4       | legend.book_tagbook.book_id |      1 |       |
+----+-------------+--------------+--------+---------------------------------------------+-----------------------+---------+-----------------------------+--------+-------+
2 rows in set (0.00 sec)

But if I added order by, it'll run 4mins.

EXPLAIN 
SELECT `book_book`.`id`, `book_book`.`title`, `book_book`.`author`, `book_book`.`href`,`book_book`.`price`, `book_book`.`nump`, `book_book`.`raw_data` 
FROM `book_book` 
INNER JOIN  `book_tagbook` 
ON (`book_book`.`id` = `book_tagbook`.`book_id`) 
WHERE `book_tagbook`.`tag_id` = 1  
ORDER BY `book_book`.`nump` ASC LIMIT 9 OFFSET 1;

+----+-------------+--------------+--------+---------------------------------------------+-----------------------+---------+-----------------------------+--------+---------------------------------+
| id | select_type | table        | type   | possible_keys                               | key                   | key_len | ref                         | rows   | Extra                           |
+----+-------------+--------------+--------+---------------------------------------------+-----------------------+---------+-----------------------------+--------+---------------------------------+
|  1 | SIMPLE      | book_tagbook | ref    | book_tagbook_3747b463,book_tagbook_752eb95b | book_tagbook_3747b463 | 4       | const                       | 116394 | Using temporary; Using filesort |
|  1 | SIMPLE      | book_book    | eq_ref | PRIMARY                                     | PRIMARY               | 4       | legend.book_tagbook.book_id |      1 |                                 |
+----+-------------+--------------+--------+---------------------------------------------+-----------------------+---------+-----------------------------+--------+---------------------------------+
2 rows in set (0.00 sec)

Here is the index in table book_book.

mysql> sHOW INDEX FROM book_book;

+-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book_book |          0 | PRIMARY        |            1 | id          | A         |      312887 |     NULL | NULL   |      | BTREE      |         |               |
| book_book |          0 | href           |            1 | href        | A         |      312887 |     NULL | NULL   |      | BTREE      |         |               |
| book_book |          1 | book_book_nump |            1 | nump        | A         |         159 |     NULL | NULL   |      | BTREE      |         |               |
| book_book |          1 | book_book_href |            1 | href        | A         |         159 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.68 sec)

And for the intermediate table

mysql> SHOW INDEX FROM book_tagbook;
+--------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book_tagbook |          0 | PRIMARY               |            1 | id          | A         |     2149842 |     NULL | NULL   |      | BTREE      |         |               |
| book_tagbook |          1 | book_tagbook_3747b463 |            1 | tag_id      | A         |      537460 |     NULL | NULL   |      | BTREE      |         |               |
| book_tagbook |          1 | book_tagbook_752eb95b |            1 | book_id     | A         |      716614 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.30 sec)
share|improve this question
    
Please run SHOW INDEX FROM book_tagbook; –  RolandoMySQLDBA Oct 14 '13 at 14:56
1  
Are book_book and book_tagbook using MyISAM or InnoDB ??? –  RolandoMySQLDBA Oct 14 '13 at 14:57
    
@RolandoMySQLDBA they are both InnoDB. And I update the question, added who index from book_tagbook. –  Feng Li Oct 14 '13 at 15:03
    
Can you also tell us how much time does it need to run the original query if you remove the LIMIT as well? –  ypercube Oct 14 '13 at 15:05
    
EXPLAIN SELECT STRAIGHT_JOIN book_book.id, book_book.title, book_book.author, book_book.href,book_book.price, book_book.nump, book_book.raw_data FROM book_book INNER JOIN book_tagbook ON (book_book.id = book_tagbook.book_id) WHERE book_tagbook.tag_id = 1 ORDER BY book_book.nump ASC LIMIT 9 OFFSET 1; can you run this query? JOINS and ORDER BY indexes are tricky your problem is "Using Temporary; Using filesort;" what could generate an MyISAM disk based table check /tmp/ to be sure... –  Raymond Nijland Oct 14 '13 at 17:07
show 7 more comments

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

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.