Slow count query (mariaDB)

Question

I have a problem with the speed of query. Simple mysql query, but when I have a lot of records (currently > 1 000 000), the performance is really slow. Question is similar to this one, but can't find solution. Explain says that MySQL is using: Using where; Using index. Has anyone any suggestions to speed it up? Slow query:

select sql_no_cache
    count(distinct(`books`.`id`)) as `count`
from `books`
left join `books_genres` use index(categorie_id) on `books_genres`.`book_id` = `books`.`id`
where 1  and `books_genres`.`genre_id` in(307,380,385,384,1359,381,390,397,394,949,1390,1391,403,401,1332,393,398,1374,1397,402,984,1025,841,1027,359,577,365,1021,1023,360,368,369,370,942,1061,1348,1376,437,737,1137,1354,1384,1385,1115,1113,1114,1143,1363,593,581,583,567,978,973,576,677,825,595,826,1043,827,1077,323,324,1361,1362,1360,407,610,611,1179,608,336,831,1042,520,1079,1078,1081,352,1349,388,727,728,729,325,330,1099,616,320,1375,1138,1388,1119,1141,1140,1328,1136,1044,1103,1074,1150,1322) and `books`.`is_status` = 'active' and `books`.`master_book` = 'true'

Result:

+--------+
| count  |
+--------+
| 402545 |
+--------+
1 row in set (2.64 sec)

Explain:

+------+-------------+--------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------+--------------------------------+---------+--------------------------+
| id   | select_type | table        | type   | possible_keys                                                                                                                                                                                                              | key          | key_len | ref                            | rows    | Extra                    |
+------+-------------+--------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------+--------------------------------+---------+--------------------------+
|    1 | SIMPLE      | books_genres | index  | categorie_id                                                                                                                                                                                                               | categorie_id | 4       | NULL                           | 1866077 | Using where; Using index |
|    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,is_status_indexed,books_idx_is_stat_master_livelib_id,master_book_is_status,is_status_master_book_year | PRIMARY      | 4       | base.books_genres.book_id |       1 | Using where              |
+------+-------------+--------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------+--------------------------------+---------+--------------------------+
2 rows in set (0.10 sec)

My tables:

CREATE TABLE `books` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `donor` enum('default','ihavebook','flibusta','litres','book24','labirint','livelib') NOT NULL DEFAULT 'default' COMMENT 'Донор',
  `donor_id` varchar(200) DEFAULT NULL COMMENT 'ID у донора',
  `name` varchar(256) DEFAULT NULL COMMENT 'Название',
  `name_int` text COMMENT 'Оригинальное название',
  `name_alt` text COMMENT 'Альтернативные названия',
  `year` int(11) DEFAULT NULL COMMENT 'Дата выхода',
  `poster` varchar(256) DEFAULT NULL COMMENT 'Путь к изображению',
  `description` text COMMENT 'Описание',
  `rating` double(4,2) NOT NULL DEFAULT '0.00' COMMENT 'Рейтинг',
  `rating_count` int(11) NOT NULL DEFAULT '0' COMMENT 'Сколько проголосовали',
  `view_count` int(11) NOT NULL DEFAULT '0' COMMENT 'Количество просмотров',
  `allowed_fragment` enum('5','10','15','20','30','40','50','60','70') DEFAULT NULL COMMENT 'Разрешенный фрагмент правообладателем (если is_toread = false)',
  `is_status` enum('active','parser','incorrect','extremist','delete','fulldeteled') NOT NULL DEFAULT 'active' COMMENT 'Статус книги',
  `is_toread` enum('true','false') NOT NULL DEFAULT 'false' COMMENT 'Возможность читать онлайн',
  `is_similar` enum('true','false') NOT NULL DEFAULT 'false' COMMENT 'Возможность посмотреть список похожих книг',
  `is_partners` enum('true','false') NOT NULL DEFAULT 'false' COMMENT 'Наличие партнерских ссылок',
  `is_duplicate` enum('true','false') NOT NULL DEFAULT 'false' COMMENT 'Проверка на дубль',
  `is_rightholder_lock` enum('true','false') NOT NULL DEFAULT 'false' COMMENT 'блокировка правообладателя',
  `rating_litres` int(11) DEFAULT NULL COMMENT 'рейтинг Литрес',
  `date_written` date DEFAULT NULL,
  `is_genre_deleted` tinyint(1) DEFAULT NULL,
  `is_description_blocked` enum('false','true') DEFAULT 'false' COMMENT 'не давать парсерам менять описание',
  `translator` varchar(255) DEFAULT NULL COMMENT 'Переводчики',
  `genres_count` tinyint(4) DEFAULT NULL,
  `checked_admin` enum('false','true') DEFAULT 'false',
  `no_ru` enum('false','true') DEFAULT 'false' COMMENT 'не русская версия',
  `count_authors` int(11) DEFAULT NULL,
  `updated_count_authors` enum('false','true') DEFAULT 'false' COMMENT 'Пересчитано количество авторов у книги',
  `checked_fb2` enum('false','true') DEFAULT 'false' COMMENT 'Проверена книга или фрагмент на метатеги',
  `indexed` enum('false','true') DEFAULT 'false' COMMENT 'Проиндексировано ElasticSearch',
  `innodb` enum('false','true') DEFAULT 'false',
  `index_popular` int(11) DEFAULT NULL COMMENT 'Индекс полпулярности',
  `donor_id2` int(11) DEFAULT NULL COMMENT 'дополнительный ID донора',
  `isbn` varchar(50) DEFAULT NULL,
  `index_popular_set` enum('false','true') DEFAULT 'false' COMMENT 'просчитан индекс популярности',
  `storage` tinyint(1) DEFAULT NULL COMMENT 'номер диска для файлов',
  `lng` tinyint(4) DEFAULT NULL COMMENT 'язык книги',
  `google_search` bigint(20) DEFAULT NULL,
  `litres_csv_rate` decimal(5,1) DEFAULT NULL,
  `litres_livelib_csv_reads` int(11) DEFAULT NULL,
  `livelib_csv_rate` decimal(5,1) DEFAULT NULL,
  `livelib_url` varchar(255) DEFAULT NULL,
  `livelib_book_id` int(11) DEFAULT NULL COMMENT 'если минус - наш id, если плюс - ЛЛ',
  `livelib_share_vk` int(11) DEFAULT NULL,
  `livelib_fav_num` int(11) DEFAULT NULL,
  `livelib_read_num` int(11) DEFAULT NULL,
  `livelib_plan_read` int(11) DEFAULT NULL,
  `livelib_comments_num` int(11) DEFAULT NULL,
  `livelib_quotes_num` int(11) DEFAULT NULL,
  `livelib_collections_num` int(11) DEFAULT NULL,
  `master_book` enum('true','false') DEFAULT 'true',
  `age` varchar(20) DEFAULT NULL,
  `num_pages` varchar(50) DEFAULT NULL,
  `old_book_id` int(11) DEFAULT NULL,
  `old_book_url` varchar(255) DEFAULT NULL,
  `checked_year` enum('false','true') DEFAULT 'false' COMMENT 'проверен ли год выпуска модераторами, чтоб отображать в библиографии',
  `checked_pages` enum('false','true') DEFAULT 'false',
  PRIMARY KEY (`id`),
  KEY `is_status` (`is_status`),
  KEY `view_count` (`view_count`),
  KEY `poster` (`poster`(255)),
  KEY `rating` (`rating`),
  KEY `rating_litres` (`rating_litres`),
  KEY `date_written` (`date_written`),
  KEY `is_description_blocked` (`is_description_blocked`),
  KEY `genres_count` (`genres_count`),
  KEY `checked_admin` (`checked_admin`),
  KEY `no_ru` (`no_ru`),
  KEY `count_authors` (`count_authors`),
  KEY `updated_count_authors` (`updated_count_authors`),
  KEY `checked_fb2` (`checked_fb2`),
  KEY `indexed` (`indexed`),
  KEY `count_authors_updated_count_authors` (`count_authors`,`updated_count_authors`),
  KEY `is_genre_deleted_is_status` (`is_genre_deleted`,`is_status`),
  KEY `updated_count_authors_is_status` (`updated_count_authors`,`is_status`),
  KEY `innodb` (`innodb`),
  KEY `index_popular` (`index_popular`),
  KEY `donor_id2` (`donor_id2`),
  KEY `year` (`year`),
  KEY `index_popular_set` (`index_popular_set`),
  KEY `donor` (`donor`),
  KEY `donor_id` (`donor_id`),
  KEY `google_search` (`google_search`),
  KEY `litres_csv_rate` (`litres_csv_rate`),
  KEY `litres_livelib_csv_reads` (`litres_livelib_csv_reads`),
  KEY `livelib_csv_rate` (`livelib_csv_rate`),
  KEY `master_book` (`master_book`),
  KEY `livelib_book_id` (`livelib_book_id`),
  KEY `storage` (`storage`),
  KEY `livelib_read_num` (`livelib_read_num`),
  KEY `old_book_id` (`old_book_id`),
  KEY `old_book_url` (`old_book_url`),
  KEY `checked_year` (`checked_year`),
  KEY `is_status_master_book` (`is_status`,`master_book`),
  KEY `lng` (`lng`),
  KEY `livelib_book_id_master_book` (`livelib_book_id`,`master_book`),
  KEY `is_status_master_book_indexed` (`is_status`,`master_book`,`indexed`),
  KEY `genres_count_master_book` (`genres_count`,`master_book`),
  KEY `count_authors_updated_count_authors_master_book` (`count_authors`,`updated_count_authors`,`master_book`),
  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`),
  KEY `donor_donor_id_donor_id2_master_book_is_status` (`donor`,`donor_id`,`donor_id2`,`master_book`,`is_status`),
  KEY `lng_is_status_master_book` (`lng`,`is_status`,`master_book`),
  KEY `is_status_indexed` (`is_status`,`indexed`),
  KEY `books_idx_is_stat_master_livelib_id` (`is_status`,`master_book`,`livelib_read_num`,`id`),
  KEY `books_idx_livelib_num_id` (`livelib_read_num`,`id`),
  KEY `master_book_is_status` (`master_book`,`is_status`),
  KEY `is_status_master_book_year` (`is_status`,`master_book`,`year`),
  FULLTEXT KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `books_genres` (
  `book_id` int(11) NOT NULL,
  `genre_id` int(11) NOT NULL,
  `sort` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`book_id`,`genre_id`),
  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';

UPDATE 1 (Its still so long):

select sql_no_cache
    count(`books`.`id`) as `count`
from `books`
use index(is_status_master_book)
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 (307,380,385,384,1359,381,390,397,394,949,1390,1391,403,401,1332,393,398,1374,1397,402,984,1025,841,1027,359,577,365,1021,1023,360,368,369,370,942,1061,1348,1376,437,737,1137,1354,1384,1385,1115,1113,1114,1143,1363,593,581,583,567,978,973,576,677,825,595,826,1043,827,1077,323,324,1361,1362,1360,407,610,611,1179,608,336,831,1042,520,1079,1078,1081,352,1349,388,727,728,729,325,330,1099,616,320,1375,1138,1388,1119,1141,1140,1328,1136,1044,1103,1074,1150,1322)
                )
        )
    )

RESULT:

+--------+
| count  |
+--------+
| 402545 |
+--------+
1 row (1.774 s)

Explain:

+------+--------------+--------------+--------+--------------------------------------------------------+-----------------------+---------+-------------+---------+--------------------------+
| id   | select_type  | table        | type   | possible_keys                                          | key                   | key_len | ref         | rows    | Extra                    |
+------+--------------+--------------+--------+--------------------------------------------------------+-----------------------+---------+-------------+---------+--------------------------+
|    1 | PRIMARY      | books        | ref    | is_status_master_book                                  | is_status_master_book | 3       | const,const |  975330 | Using where; Using index |
|    1 | PRIMARY      | <subquery2>  | eq_ref | distinct_key                                           | distinct_key          | 4       | func        |       1 |                          |
|    2 | MATERIALIZED | books_genres | index  | PRIMARY,book_id,categorie_id,book_id2,genre_id_book_id | PRIMARY               | 8       | NULL        | 1866103 | Using where; Using index |
+------+--------------+--------------+--------+--------------------------------------------------------+-----------------------+---------+-------------+---------+--------------------------+
3 rows in set (0.00 sec)

UPDATE 2:

select sql_no_cache
    count(1) as `count`
from `books`
use index(is_status_master_book_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 (307,380,385,384,1359,381,390,397,394,949,1390,1391,403,401,1332,393,398,1374,1397,402,984,1025,841,1027,359,577,365,1021,1023,360,368,369,370,942,1061,1348,1376,437,737,1137,1354,1384,1385,1115,1113,1114,1143,1363,593,581,583,567,978,973,576,677,825,595,826,1043,827,1077,323,324,1361,1362,1360,407,610,611,1179,608,336,831,1042,520,1079,1078,1081,352,1349,388,727,728,729,325,330,1099,616,320,1375,1138,1388,1119,1141,1140,1328,1136,1044,1103,1074,1150,1322)
                )
        )
    )

Rusult:

+--------+
| count  |
+--------+
| 402553 |
+--------+
1 row in set (3.84 sec)

UPDATE 3:

ALTER TABLE books ADD INDEX is_status_master_book_id (is_status, master_book, id);

SELECT sql_no_cache COUNT(id) AS `count`
FROM books
use index(is_status_master_book_id)
WHERE books.is_status = 'active'
   AND books.master_book = 'true' 
   AND id IN (
        SELECT book_id
        FROM books_genres
        WHERE genre_id IN (307,380,385,384,1359,381,390,397,394,949,1390,1391,403,401,1332,393,398,1374,1397,402,984,1025,841,1027,359,577,365,1021,1023,360,368,369,370,942,1061,1348,1376,437,737,1137,1354,1384,1385,1115,1113,1114,1143,1363,593,581,583,567,978,973,576,677,825,595,826,1043,827,1077,323,324,1361,1362,1360,407,610,611,1179,608,336,831,1042,520,1079,1078,1081,352,1349,388,727,728,729,325,330,1099,616,320,1375,1138,1388,1119,1141,1140,1328,1136,1044,1103,1074,1150,1322)
    )

Result:

+--------+
| count  |
+--------+
| 402553 |
+--------+
1 row in set (1.47 sec)

1) you are filtering on non-null values in the right-hand-side table of the join effectively turning it into an inner join, so change the join type to inner join. 2) I do not know what fields are indexed in the category_id index, but its name suggests that category_id field is probably the leftmost one, but that field is not used anywhere in the query. 3) A compound index on book_id - genre_id for books_genres table should be more ideal index 4) since you only want to get the count from books table, consider using exists with subquery instead of a join. You can do away with distinct in count — Shadow, Nov 01 '21 at 10:36
Avoid `USE INDEX()` unless and until you really understand how MariaDB handles your query -- unless your name happens to be Stonebraker or Widenius you're better off trusting the query planner code in the server. — O. Jones, Nov 01 '21 at 11:46
@O.Jones without USE INDEX() time is 2+seconds in new query and 3+seconds in old query — wstudiokiwi, Nov 01 '21 at 11:50
Which database are you using... MySQL or MariaDB... two different databases. Only use correct tag now and future please. — DRapp, Nov 01 '21 at 11:54
For the exists subquery, can you pls try to force the subquery to use genre_id_book_id index instead of the primary one? The optimiser decided to materialise the exists subquery, which means it filters books_genres by genre_id first. With primary key it moved to a covering index, but it still has to scan the entire index. — Shadow, Nov 01 '21 at 12:27
I think you are starting to reach the lower bound of speed optimisation through programming techniques. If this is not fast enough for you, you may have to tweak mysql settings to speed things up. There already are lots of questions and answers on this for mysql / mariadb on SO and the DBA sister site of SO. — Shadow, Nov 01 '21 at 12:46

score 1 · Answer 1 · answered Nov 01 '21 at 12:35

First, just to be sure, use ANALYZE TABLE to update the internal statistics used by MariaDB to figure out which indexes to use.

ANALYZE TABLE books, book_genres;

In MariaDB you can use ANALYZE SELECT in place of EXPLAIN SELECT on your queries. ANALYZE actually runs the query then shows the same sort of output as EXPLAIN but with more details. So, use that to do your analysis.

SELECT COUNT(), sad to say, is inherently slow on big tables. If you do this

SELECT COUNT(id) AS `count`
  FROM books
 WHERE books.is_status = 'active'
   AND books.master_book = 'true'

you'll get a lower bound on (minimum for) the time your query will take. Your index called is_status_master_book will help with this query unless most of your books are 'active' and 'master,' in which case the server chooses a table scan because the index is not selective enough. Get this query working and study the ANALYZE output. (InnoDB indexes always append the primary key to the end of the list of columns.)

(Notice that your is_status index is redundant with your is_status_master_book index.)

Next, work on this shorter version of your genre lookup.

               SELECT book_id
                 FROM books_genres
                WHERE genre_id in (307,380,385,384,1359,381,390,397,394)

This query should use the genre_id_book_id on your books_genres table. Does it work? Does it perform tolerably well? If so, try it again with all the genre_ids.

If its performance gets noticeably worse with the longer list, you might try refactoring your app to put that long list into a temporary table and doing

               SELECT book_id
                 FROM books_genres
                 JOIN temptable ON books_genres.genre_id = temptable.genre_id

As a next step, try this.

SELECT COUNT(id) AS `count`
  FROM books
 WHERE books.is_status = 'active'
   AND books.master_book = 'true' 
   AND id IN (
         SELECT book_id
           FROM books_genres
          WHERE genre_id IN ( short list )
       )

How does this perform? Look at the ANALYZE output. Try using a different index; it may help if id is more selective than is_status and master_book.

ALTER TABLE books ADD INDEX id_status_master (id, is_status, master);

Then try the longer list of genres in the above query. That query is equivalent to your query.

ANALYZE TABLE results - OK. On your query with index `id_status_master` i have result time 1.629 s. But with index `ALTER TABLE books ADD INDEX is_status_master_book_id (is_status, master_book, id);` result is 1.421 s — wstudiokiwi, Nov 01 '21 at 12:46

DRapp · Answer 2 · 2021-11-01T12:07:32.310

You have some redundant indexes, I would get rid of those first... not that they would have impact on queries, but I'll explain.

If you have an index on (a, b) another index on (b, a), and you run a query qualifying BOTH columns, then either will work. However, if you only need column b as part of a query, have an index on that. The (a, b) would also be used if querying column a + anything else.

So your book table indexes of

is_status (is_status) is_status_master_book (is_status, master_book)

the single "is_status" index is redundant. By looking at some of the other indexes, there might be better indexing options but without knowing context of your queries, I'll leave alone.

Now, to help one more level, for this specific query, you probably need to make a COVERING index. This basically means the index has all the columns requested by the query as part of the index, so the engine never needs to go to the individual raw pages of record data to get/qualify the results.

So, I would change your "Is_Status_Master_Book" index to

is_status_master_book (is_status, master_book, id)

Similarly in your book genre table which you have with your unique key 'Book_id' which has both the book ID and genre. But also makes the "genre_id_book_id" redundant, but can leave the categorie_id index as that covers the genre_id if there is ever a genre_id only query being performed.

Now, for your query. You have a left-join, but had an "AND" Genre_id in the where clause making it an INNER JOIN.

    select sql_no_cache
          count( distinct b.id ) as `count`
       from 
          books b
             join books_genres bg
                on b.id = bg.book_id
       where 
              1  
          and b.is_status = 'active' 
          and b.master_book = 'true' 
          and bg.genre_id in (307,380,385,384,1359,381,390,397,394,949,1390,1391,403,401,1332,393,
398,1374,1397,402,984,1025,841,1027,359,577,365,1021,1023,360,368,369,
370,942,1061,1348,1376,437,737,1137,1354,1384,1385,1115,1113,1114,1143,
1363,593,581,583,567,978,973,576,677,825,595,826,1043,827,1077,323,324,
1361,1362,1360,407,610,611,1179,608,336,831,1042,520,1079,1078,1081,352,
1349,388,727,728,729,325,330,1099,616,320,1375,1138,1388,1119,1141,1140,
1328,1136,1044,1103,1074,1150,1322)

What is the true intent of your query... Do you want a count of all Active status books that are master books AND they meet one of the genres listed? If so, that is your query (less the left-join portion), but update the index is_status_master_book as suggested to make it covering so it does not need to go to the raw data pages to qualify records and return results.

An alternative would be do pre-query the distinct books within the qualifying genres.

    select sql_no_cache
          count( distinct b.id ) as `count`
       from 
          books b
             join 
             ( select distinct bg.book_id
                  from books_genres bg
                  where bg.genre_id in (307,380,385,384,1359,381,390,397,394,949,1390,1391,403,401,1332,393,
398,1374,1397,402,984,1025,841,1027,359,577,365,1021,1023,360,368,369,
370,942,1061,1348,1376,437,737,1137,1354,1384,1385,1115,1113,1114,1143,
1363,593,581,583,567,978,973,576,677,825,595,826,1043,827,1077,323,324,
1361,1362,1360,407,610,611,1179,608,336,831,1042,520,1079,1078,1081,352,
1349,388,727,728,729,325,330,1099,616,320,1375,1138,1388,1119,1141,1140,
1328,1136,1044,1103,1074,1150,1322) ) PQ
                on b.id = PQ.book_id
       where 
              1  
          and b.is_status = 'active' 
          and b.master_book = 'true'

thank you, i added Update 2, it's not faster Update 1 – wstudiokiwi Nov 01 '21 at 12:11 — wstudiokiwi, Nov 01 '21 at 12:11

Rick James · Answer 3 · 2021-11-01T15:22:53.097

It takes time to deliver 400K rows.

Simplify the indexes:

PRIMARY KEY (`book_id`,`genre_id`),
UNIQUE KEY `book_id` (`book_id`,`genre_id`),  -- redundant
KEY `categorie_id` (`genre_id`),  -- in the way
KEY `sort` (`sort`),
KEY `book_id2` (`book_id`),  -- in the way
KEY `genre_id_book_id` (`genre_id`,`book_id`)

-->

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

When you have INDEX(a,b), you don't also need INDEX(a).

And get rid of use index(categorie_id).

It seems strange to have x and x_id in the same table unless the focus of the table is listing all the x. (I am referring to donor.)

Is the query I/O-bound or is it CPU-bound?

How much RAM do you have? What is the value of innodb_buffer_pool_size? How many rows in each table? Is the disk HDD or is it SSD?

If it is I/O-bound, there are multiple possible speedups.

Slow count query (mariaDB)

3 Answers3