Slow join with order query

Question

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;

Add GROUP BY instead of Distinct for book.id.. Why order by clause is needed for a single column retrieval. — Rahul Biswas, Oct 29 '21 at 07:12
The `CREATE TABLE books` syntax is erroneous. Missing comma after `DEFAULT 'true'` and nearly every `KEY` assignment there, the column doesn't exists. — FanoFN, Oct 29 '21 at 08:12

Rick James · Answer 1 · 2021-10-29T16:04:29.740

Problems with books_genres.

It has no PRIMARY KEY.
All columns are nullable. Will you ever insert a row with any NULLs?

Recommend (after saying NOT NULL on all columns):

PRIMARY KEY(`book_id`,`genre_id`)
INDEX(genre_id, book_id, sort)

and remove all the rest.

I don't see livelib_read_num in the table???

In the other table, remove any indexes that are the exact prefix of some other index.

These might help with speed. (Again, filter out prefix indexes that are redundant.) (These are "covering" indexes, which helps a little.)

books:  INDEX(is_status, master_book, livelib_read_num, id)
books:  INDEX(livelib_read_num, id, is_status, master_book)

The second index may cause the Optimizer to give preference to ORDER BY. (That is a risky optimization, since it might have to scan the entire index without finding 25 relevant rows.)

score 0 · Accepted Answer · answered Oct 31 '21 at 18:50

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)

Slow join with order query

2 Answers2