This article gives out a relative fastest way to get the number of records from a huge table, which means, the table has millions, or even hundreds of millions, of records.
In such huge tables, each possible optimization method is worth considering. Sometimes, evan one single optimized SQL sentence can save you a lot of time, especially in busy websites.
Here, the example I will use to take the test is an Innodb, not MyISAM, engined table, which has five indices and 'id' as the primary key, with more than twenty million records.
The structure of the table test_table is:
+--------------+-----------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+-------------------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| ownerId | int(11) | | MUL | 0 | |
| ownerName | varchar(50) | | | | |
| visitorId | int(11) | | MUL | 0 | |
| visitorName | varchar(100) | | | | |
| visitTime | timestamp | YES | | CURRENT_TIMESTAMP | |
| firstTime | int(10) unsigned | | | 0 | |
| lastTime | int(10) unsigned | | MUL | 0 | |
| visitNum | mediumint(8) unsigned | | | 0 | |
| status | tinyint(3) unsigned | | MUL | 0 | |
+--------------+-----------------------+------+-----+-------------------+----------------+
These are three different SQL sentences to get the total number of records:
SELECT COUNT(*) FROM test_table;
SELECT COUNT(id) FROM test_table;
SELECT COUNT(1) FROM test_table;
The explains of the queries goes here, and they return the same results:
mysql> explain select count(*) from test_table;
+----+-------------+------------+-------+---------------+--------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+--------+---------+------+----------+-------------+
| 1 | SIMPLE | test_table | index | NULL | status | 1 | NULL | 23051499 | Using index |
+----+-------------+------------+-------+---------------+--------+---------+------+----------+-------------+
1 row in set (0.00 sec)
The results goes here:
mysql> SELECT COUNT(*) FROM test_table;
+----------+
| count(*) |
+----------+
| 20795139 |
+----------+
1 row in set (1 min 8.22 sec)
mysql> SELECT COUNT(id) FROM test_table;
+-----------+
| count(id) |
+-----------+
| 20795139 |
+-----------+
1 row in set (1 min 1.45 sec)
mysql> SELECT COUNT(1) FROM test_table;
+----------+
| count(1) |
+----------+
| 20795139 |
+----------+
1 row in set (56.67 sec)
I still wonder wether there is some even more efficient way to manage that. But the methods above is not to bad. If you have any better method, do not hesitate to tell me. It will be greatly appreciated.
