Getting row count for a table in MySQL?

Question

Can anyone tell me which is he better appraoch in MySQL whin it comes to finding the row count of a table: is it better to do

SELECT COUNT(*) FROM TABLE_NAME

or to look up the row count in the table TABLE in the INFORMATION_SCHEMA?

This would be a frequent operation for calculating pagination counts for pages?

I should add that tables will only have a few thousand rows at most.

Thanks

Martin O'Shea.

Solution 1

In MyISAM, this query:

SELECT  COUNT(*)
FROM    TABLE_NAME

is instant, since it's kept in the table metadata, so it's almost free to issue this query and it will always get the correct result.

In InnoDB, this query will count rows one-by-one which could take some time.

So if you don't need exact value of COUNT(*), you may query INFORMATION_SCHEMA.

Solution 2

I'd also consider using SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() if you find COUNT(*) too slow.

Comments

[Admin]

I think that given that I need to calculate pagination using row count / pagination count, on InnoDB tables the best way is to use the INFORMATION_SCHEMA. Counting would only take milliseconds but that's still an overhead if several users are accessing the same table for browsing.