2

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.

  • can you add the output of an explain of your query – miracle173 Aug 1 '15 at 21:24
2

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)
  • 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? – miracle173 Aug 1 '15 at 20:59
  • To count records in the index you need to read them, don't you? – akuzminsky Aug 1 '15 at 21:03
  • For better understanding how indexes work slideshare.net/akuzminsky/… – akuzminsky Aug 1 '15 at 21:05
  • What is the result of your query? Does category 1 has around the average? Or it is way bigger? – Jehad Keriaki Aug 1 '15 at 21:12
  • 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). – Rick James Aug 2 '15 at 2:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.