7

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.

Improve this question
1
  • For a few thousand rows select count(*) will work nearly instantaneously. Commented Jun 19, 2009 at 14:47

2 Answers 2

Reset to default
18

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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.
1

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.