Speed up COUNT(*) when using an index

Question

I have a table similar to this (simplified):

CREATE TABLE books (
 id INT AUTO_INCREMENT,
 category INT NOT NULL,
 PRIMARY KEY (id),
 KEY (category)
);

This table has over 10,000,000 rows, at around 12 categories. So each category has an average of 833,333 books.

When querying for count:

SELECT COUNT(*) FROM books WHERE category=1;

Even though it's using an index when querying, this takes quite a long time to complete (several seconds.) How would you optimize this?

Previously, I had increased a number every time I inserted to books (into a table that relates category -> book count.) But our code is complicated, and many places insert or delete books. I know it's possible to solve this with EVENTS, but I'm asking maybe there's a MySQL feature I missed.

• miracle173: can you add the output of an explain of your query

Answer 1

The query will be slow because cardinality of category index is low. There are 12 categories, so in average the query will read 1/12 part of the index. You can't improve this query.

Your original approach can improve overall performance. Just instead of manually updating book_count create a trigger on INSERT and DELETE event.

UPDATE: To prove the query will partially read index category

mysql> select count(*) from books;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)
mysql> select category, count(*) from books group by 1;
+----------+----------+
| category | count(*) |
+----------+----------+
| 0 | 50 |
| 1 | 77 |
| 2 | 88 |
| 3 | 84 |
| 4 | 102 |
| 5 | 79 |
| 6 | 79 |
| 7 | 73 |
| 8 | 84 |
| 9 | 76 |
| 10 | 87 |
| 11 | 83 |
| 12 | 38 |
+----------+----------+
13 rows in set (0.01 sec)
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from books where category = 6;
+----------+
| count(*) |
+----------+
| 79 |
+----------+
1 row in set (0.00 sec)
mysql> show status like 'Hand%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 79 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
18 rows in set (0.01 sec)

• miracle173: The query only reads 1/12th of the index to count all books of category 1? How should that work? When does it stop to read the index?
• akuzminsky: To count records in the index you need to read them, don't you?
• akuzminsky: For better understanding how indexes work slideshare.net/akuzminsky/…
• Jehad Keriaki: What is the result of your query? Does category 1 has around the average? Or it is way bigger?
• Rick James: That particular SELECT will reach into INDEX(category), which is stored as a BTree, and scan however many index records are there are rows with the given category. Then it is finished. The more records, the longer it will take; there is no avoiding it (for that SELECT).

Answer 2

my trick is, make temporary table with field category and units then u make schedule at night to counting it n save to your temporary table, then ur just read that table with just 12 row in it, very faster, but ur count H-1, u need liitle trick againt to add current day ...

• Mr.Brownstone: Welcome to the site! While pre-computing the information is a sound strategy, it may not be possible in all scenarios. Could you expand on your answer to provide a solution to speeding up the query without pre-computing the results?