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