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

Question

I have a MySQL 5.0 database with several tables containing over 50 million rows. But how do I know that? By running "SELECT COUNT (1) FROM foo", of course. This query in a single table containing 58.8M rows took 10 minutes !

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 tables listed are InnoDB.

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

Answer 1

If you need to immediately get the result, and you do not care if it is 58.8M or 51.7M, you can find out the approximate number of lines by calling

show table status like 'large_table';

. rows
http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

Answer 2

- ; , - (, , ).

, MyISAM, , (*) "", . MyISAM , MVCC, .

, , - , , , innodb.

, , , ( ..) .

Answer 3

select count(id) from large_table