MySQL get the number of rows in an innodb table

Question

I have a table using innodb. I know the table has roughly 89 million rows. Using

SELECT COUNT(*) FROM table;

takes about five minutes to run. I know that innodb is not optimized for unconditional COUNT(*) queries. How can I restructure the query to give me a count faster? Would just adding WHERE 1=1 work, or do I need to query a specific field?

I know I can get the approximate number of rows using SHOW TABLE STATUS, but I want to get the value in a PHP script, and it seems like there is a lot to dig through using that method.

See also: http://www.tocker.ca/2013/05/02/fastest-way-to-count-rows-in-a-table.html — Morgan Tocker, Jun 25 '14 at 02:51

score 24 · Accepted Answer · answered Feb 26 '11 at 17:35

24

If you are OK with the estimated number and just don't want to mess with running SHOW TABLE STATUS from PHP, you can use the information_schema DB:

SELECT TABLE_ROWS FROM information_schema.tables
WHERE TABLE_SCHEMA = 'my_db_name' 
AND TABLE_NAME = 'my_table_name';

answered Feb 26 '11 at 17:35

Galz

6,713
4
33
39

I'm curious as to how this is "estimated." Could someone provide me with more information on that? Why wouldn't this number be completely accurate? Thanks. – M Miller Jul 10 '13 at 17:10
2

@MMiller - You can read all about it here: http://dev.mysql.com/doc/innodb/1.1/en/innodb-other-changes-statistics-estimation.html – Galz Jul 12 '13 at 15:29
+1 Very good for when we need not use `WHERE`. If you use `WHERE`, maybe a `SELECT count (1)` is the best outlet (I do not know if this makes a buffer of all lines displayed). – Protomen Jan 02 '14 at 20:22
@Pacerier - of course. This answer is an optional optimization if an estimation is OK. Otherwise there is no escaping a full table scan (AFAIK) – Galz Dec 09 '14 at 15:01

score 8 · Answer 2 · answered Feb 26 '11 at 10:24

8

If you are ok with approximate number of records, you can use output of "explain".

Simplified verion of the code is

$result = mysql_query('explain SELECT count(*) from TABLE_NAME');
$row = mysql_fetch_assoc($result);
echo $row['rows'];

answered Feb 26 '11 at 10:24

Zimbabao

8,150
3
29
36

Much easier and seems to show the same number as information_schema.tables, thanks! – Kevin Mar 17 '23 at 00:39

score 4 · Answer 3 · answered Feb 25 '11 at 16:58

4

If the table is read frequently and updated infrequently, you may want to consider creating a statistics table that is updated via triggers when making changes to the table.

answered Feb 25 '11 at 16:58

Jordan Ryan Moore

6,877
2
26
27

score -6 · Answer 4 · answered Feb 25 '11 at 16:53

-6

mysql_num_rows may be useful to you.

answered Feb 25 '11 at 16:53

steve

849
2
9
15

3

Doesn't that just give the number of rows in a buffered result set? I am looking for the number of rows in the full table. – Wige Feb 25 '11 at 16:56

MySQL get the number of rows in an innodb table

4 Answers4

Linked