mysqlmariadbmysql-slow-query-log

Slow join with order query


I have a problem with the speed of query. Question is similar to this one, but can't find solution. Explain says that MySQL is using: Using where; Using index; Using temporary; Using filesort Slow query:

select 
    distinct(`books`.`id`)
from `books`
join `books_genres` on `books_genres`.`book_id` = `books`.`id`
where 
    `books`.`is_status` = 'active' and `books`.`master_book` = 'true'  
    and `books_genres`.`genre_id` in(380,381,384,385,1359)
order by 
    `books`.`livelib_read_num` DESC, `books`.`id` DESC
limit 0,25
#25 rows (0.319 s)

But if I remove order statement from query it is really fast:

select sql_no_cache
    distinct(`books`.`id`)
from `books`
join `books_genres` on `books_genres`.`book_id` = `books`.`id`
where 
    `books`.`is_status` = 'active' and `books`.`master_book` = 'true'  
    and `books_genres`.`genre_id` in(380,381,384,385,1359)
limit 0,25
#25 rows (0.005 s)

Explain:

   +------+-------------+--------------+--------+---------------------------------------------------------------------------------------------------------------------+------------------+---------+--------------------------------+--------+-----------------------------------------------------------+
| id   | select_type | table        | type   | possible_keys                                                                                                       | key              | key_len | ref                            | rows   | Extra                                                     |
+------+-------------+--------------+--------+---------------------------------------------------------------------------------------------------------------------+------------------+---------+--------------------------------+--------+-----------------------------------------------------------+
|    1 | SIMPLE      | books_genres | range  | book_id,categorie_id,book_id2,genre_id_book_id                                                                      | genre_id_book_id | 10      | NULL                           | 194890 | Using where; Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | books        | eq_ref | PRIMARY,is_status,master_book,is_status_master_book,is_status_master_book_indexed,is_status_donor_no_ru_master_book | PRIMARY          | 4       | knigogid3.books_genres.book_id |      1 | Using where                                               |
+------+-------------+--------------+--------+---------------------------------------------------------------------------------------------------------------------+------------------+---------+--------------------------------+--------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

My tables:

CREATE TABLE `books_genres` (
  `book_id` int(11) DEFAULT NULL,
  `genre_id` int(11) DEFAULT NULL,
  `sort` tinyint(4) DEFAULT NULL,
  UNIQUE KEY `book_id` (`book_id`,`genre_id`),
  KEY `categorie_id` (`genre_id`),
  KEY `sort` (`sort`),
  KEY `book_id2` (`book_id`),
  KEY `genre_id_book_id` (`genre_id`,`book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `books` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `is_status` enum('active','parser','incorrect','extremist','delete','fulldeteled') NOT NULL DEFAULT 'active',
  `livelib_book_id` int(11) DEFAULT NULL,
  `master_book` enum('true','false') DEFAULT 'true'
  PRIMARY KEY (`id`),
  KEY `is_status` (`is_status`),
  KEY `master_book` (`master_book`),
  KEY `livelib_book_id` (`livelib_book_id`),
  KEY `livelib_read_num` (`livelib_read_num`),
  KEY `is_status_master_book` (`is_status`,`master_book`),
  KEY `livelib_book_id_master_book` (`livelib_book_id`,`master_book`),
  KEY `is_status_master_book_indexed` (`is_status`,`master_book`,`indexed`),
  KEY `is_status_donor_no_ru_master_book` (`is_status`,`donor`,`no_ru`,`master_book`),
  KEY `livelib_url_master_book_is_status` (`livelib_url`,`master_book`,`is_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Solution

  • SELECT sql_no_cache
           `books`.`id`
        FROM
            `books` 
        use index(books_idx_is_stat_master_livelib_id)
        WHERE
            (
                1 = 1 
                AND `books`.`is_status` = 'active' 
                AND `books`.`master_book` = 'true'
            ) 
            AND (
                EXISTS (
                    SELECT
                        1 
                    FROM
                        `books_genres` 
                    WHERE
                        (
                            `books_genres`.`book_id` = `books`.`id`
                        ) 
                        AND (
                            `books_genres`.`genre_id` IN (
                                380, 381, 384, 385, 1359
                            )
                        )
                )
            ) 
        ORDER BY
            `books`.`livelib_read_num` DESC,
            `books`.`id` DESC LIMIT 0,
            25;
    25 rows in set (0.07 sec)