CODEWITHSUNDEEP
mysqlcountinnodb
Wige
Wige

Reputation: 3918

MySQL get the number of rows in an innodb table

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.

Upvotes: 10

Views: 13343

Answers (4)

Galz
Galz

Reputation: 6842

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';

Upvotes: 24

Zimbabao
Zimbabao

Reputation: 8250

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'];

Upvotes: 8

Jordan Ryan Moore
Jordan Ryan Moore

Reputation: 6887

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.

Upvotes: 4

steve
steve

Reputation: 879

mysql_num_rows may be useful to you.

Upvotes: -6

Related Questions