Speed up COUNT(*) when using an index
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)