I'm trying to figure out how I can speed up the time to get a count of all the rows in an InnoDB table. My deployment environment is AWS Aurora MySQL, but I see the same behavior in regular MySQL.
I have an indexed VARCHAR field 'zCreator' (not the primary key, which is a separate field called 'id') that happens to have a not-null value for all rows, and it's fairly fast to get a count of the rows:
mysql> show indexes from sync_fmlog;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sync_fmlog | 0 | PRIMARY | 1 | ID | A | 321352 | NULL | NULL | | BTREE | | | YES | NULL |
| sync_fmlog | 1 | creator | 1 | z_Creator | A | 257 | NULL | NULL | YES | BTREE | | | YES | NULL |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.11 sec)
mysql> select count(z_creator) from sync_fmlog;
+------------------+
| count(z_creator) |
+------------------+
| 350849 |
+------------------+
1 row in set (0.79 sec)
mysql> explain select count(z_Creator) from sync_fmlog;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | sync_fmlog | NULL | index | NULL | creator | 258 | NULL | 254782 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.04 sec)
However, if I want to enforce that this field is not-null, like this:
alter table sync_fmlog modify z_Creator varchar(255) not null;
Then my count becomes very slow, even though it returns the same results:
mysql> show indexes from sync_fmlog;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sync_fmlog | 0 | PRIMARY | 1 | ID | A | 321352 | NULL | NULL | | BTREE | | | YES | NULL |
| sync_fmlog | 1 | creator | 1 | z_Creator | A | 257 | NULL | NULL | | BTREE | | | YES | NULL |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.30 sec)
mysql> select count(z_creator) from sync_fmlog;
+------------------+
| count(z_creator) |
+------------------+
| 350849 |
+------------------+
1 row in set (13.14 sec)
mysql> explain select count(z_Creator) from sync_fmlog;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | sync_fmlog | NULL | index | NULL | creator | 257 | NULL | 306471 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.04 sec)
Why does this happen? Is there any efficient way to get an accurate count of all rows for an InnoDB table?
count(*)can be used.