Getting row count for table in MySQL?

Question

Can someone tell me what it evaluates better in MySQL when it comes to finding the number of rows in a table: is it better to do

SELECT COUNT(*) FROM TABLE_NAME 

or see the number of rows in the TABLE table in INFORMATION_SCHEMA ?

Will it be a frequent operation to count the number of page pages for pages?

I must add that the tables will contain only a few thousand rows.

thanks

Martin O'Shea.

Answer 1

In MyISAM this query:

 SELECT COUNT(*) FROM TABLE_NAME 

is instantaneous since it is stored in the table metadata, so it almost frees this query and will always have the correct result.

In InnoDB this query will count the rows one by one, which may take some time.

So, if you do not need the exact COUNT(*) value, you can request INFORMATION_SCHEMA .

Answer 2

I will also consider using SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() if you find COUNT(*) too slowly.