I got a large table with million of records. I have to do a count(*) for a certain criteria and there is no way I can get rid of it.
count() with InnoDB is very expensive. I have been trying to figure out different configurations for MySQL but all in vain. Can't speed up the count. The application requires the result to be less than 1 second because there are other dependent queries to run.
Any indexes are not helping because of the way InnoDB counts.
mysql> EXPLAIN SELECT count(*) FROM `callrequests` WHERE active_call = 1;
+----+-------------+--------------+-------+---------------+-------------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+---------------+-------------+---------+------+---------+--------------------------+
| 1 | SIMPLE | callrequests | index | NULL | active_call | 6 | NULL | 5271135 | Using where; Using index |
+----+-------------+--------------+-------+---------------+-------------+---------+------+---------+--------------------------+
mysql> show index from callrequests;
+--------------+------------+------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| callrequests | 0 | PRIMARY | 1 | id | A | 5271135 | NULL | NULL | | BTREE | | |
| callrequests | 0 | PRIMARY | 2 | campaign_id | A | 5271135 | NULL | NULL | | BTREE | | |
| callrequests | 0 | unique_contact | 1 | campaign_id | A | 4849 | NULL | NULL | | BTREE | | |
| callrequests | 0 | unique_contact | 2 | contact_id | A | 5271135 | NULL | NULL | | BTREE | | |
| callrequests | 0 | unique_contact | 3 | contact | A | 5271135 | NULL | NULL | | BTREE | | |
| callrequests | 1 | fk_callrequest_campaign1_idx | 1 | campaign_id | A | 10 | NULL | NULL | | BTREE | | |
| callrequests | 1 | index4 | 1 | campaign_id | A | 2506 | NULL | NULL | | BTREE | | |
| callrequests | 1 | index4 | 2 | contact | A | 5271135 | NULL | NULL | | BTREE | | |
| callrequests | 1 | phonbook_id_index | 1 | phonebook_id | A | 10 | NULL | NULL | | BTREE | | |
| callrequests | 1 | dnc_group_id_index | 1 | dnc_group_id | A | 2 | NULL | NULL | | BTREE | | |
| callrequests | 1 | active_call | 1 | campaign_id | A | 12 | NULL | NULL | | BTREE | | |
| callrequests | 1 | active_call | 2 | active_call | A | 16 | NULL | NULL | YES | BTREE | | |
| callrequests | 1 | call_status | 1 | call_status | A | 2518 | NULL | NULL | | BTREE | | |
| callrequests | 1 | call_status | 2 | processed | A | 2518 | NULL | NULL | | BTREE | | |
| callrequests | 1 | call_status | 3 | active_call | A | 2518 | NULL | NULL | YES | BTREE | | |
+--------------+------------+------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
The server is
Xeon machine with 12 CPU cores and 64 GB RAM dedicated 5.6.14-62.0 Percona Server
My innodb_buffer_pool_size is 38 GB and all of data sits in innodb buffer pool.
Please notice that counting with WHERE is not slower with InnoDB than it would be with MyISAM. Only a very bare
SELECT COUNT(*) FROM table
can be computed faster with MyISAM as this number is stored in MyISAMs table metadata.
If you have a query with WHERE constraint for example:
SELECT COUNT(*) FROM table WHERE active_calls = 1
the query needs to access the table data in both storage engines and there should be no notable performance difference between MyISAM and InnoDB.
Please see that your query does not use any proper index. This is not because InnoDB "prefers" a full table scan, but because there exists no proper index.
You have a combined index (campaign_id, active_calls), but active_calls is the second part of the index. As long as the first part is not used in the query, MySQL has no easy access to the second part.
What you want for this simple count query is another index (active_calls) only on this one column. It should run fast then.