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
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.