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.

Accepted Answer

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

Getting row count for a table in MySQL?

Solution

Problem