In MySQL 5, SELECT COUNT(1) FROM table_name is ver

Question

I have a MySQL 5.0 database with a few tables containing over 50M rows. But how do I know this? By running "SELECT COUNT(1) FROM foo", of course. This query on one table containing 58.8M rows took 10 minutes to complete!

mysql> SELECT COUNT(1) FROM large_table;
+----------+
| count(1) |
+----------+
| 58778494 | 
+----------+
1 row in set (10 min 23.88 sec)

mysql> EXPLAIN SELECT COUNT(1) FROM large_table;
+----+-------------+-------------------+-------+---------------+----------------------------------------+---------+------+-----------+-------------+
| id | select_type | table             | type  | possible_keys | key                                    | key_len | ref  | rows      | Extra       |
+----+-------------+-------------------+-------+---------------+----------------------------------------+---------+------+-----------+-------------+
|  1 | SIMPLE      | large_table       | index | NULL          | fk_large_table_other_table_id          | 5       | NULL | 167567567 | Using index | 
+----+-------------+-------------------+-------+---------------+----------------------------------------+---------+------+-----------+-------------+
1 row in set (0.00 sec)

mysql> DESC large_table;
+-------------------+---------------------+------+-----+---------+----------------+
| Field             | Type                | Null | Key | Default | Extra          |
+-------------------+---------------------+------+-----+---------+----------------+
| id                | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment | 
| created_on        | datetime            | YES  |     | NULL    |                | 
| updated_on        | datetime            | YES  |     | NULL    |                | 
| other_table_id    | int(11)             | YES  | MUL | NULL    |                | 
| parent_id         | bigint(20) unsigned | YES  | MUL | NULL    |                | 
| name              | varchar(255)        | YES  |     | NULL    |                | 
| property_type     | varchar(64)         | YES  |     | NULL    |                | 
+-------------------+---------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

All of the tables in question are InnoDB.

Any ideas why this is so slow, and how I can speed it up?

Answer 1

select count(id) from large_table will surely run faster

Answer 2

If you need to have the result instantly and you don't care if it's 58.8M or 51.7M, you can find out the approximate number of rows by calling

show table status like 'large_table';

See the column rows
For more information about the result take a look at the manual at http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

Answer 3

Counting all the rows in a table is a very slow operation; you can't really speed it up, unless you are prepared to keep a count somewhere else (and of course, that can become out of sync).

People who are used to MyISAM tend to think that they get count(*) "for free", but it's not really. MyISAM cheats by not having MVCC, which makes it fairly easy.

The query you're showing is doing a full index scan of a not-null index, which is generally the fastest way of counting the rows in an innodb table.

It is difficult to guess from the information you've given, what your application is, but in general, it's ok for users (etc) to see close approximations of the number of rows in large tables.