Speed up COUNT(*) when using an index

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.

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)

StackExchange Database Administrators Q#108769, answer score: 4