Best way to count rows from mysql database

Question

After facing a slow loading time issue with a mysql query, I'm now looking the best way to count rows numbers. I have stupidly used mysql_num_rows() function to do this and now realized its a worst way to do this. I was actually making a Pagination to make pages in PHP. I have found several ways to count rows number. But I'm looking the faster way to count it.

The table type is MyISAM

So the question is now

Which is the best and faster to count -

1. `SELECT count(*) FROM 'table_name'`

2. `SELECT TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES WHERE table_schema =  'database_name'
AND table_name LIKE  'table_name'`

3. `SHOW TABLE STATUS LIKE 'table_name'`

4. `SELECT FOUND_ROWS()`

If there are others better way to do this, please let me know them as well. If possible please describe along with the answer- why it is best and faster. So I could understand and can use the method based on my requirement.

Thanks.

Answer 1

Quoting the MySQL Reference Manual on COUNT

COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. For example:

mysql> SELECT COUNT(*) FROM student; 

This optimization applies only to MyISAM tables only, because an exact row count is stored for this storage engine and can be accessed very quickly. For transactional storage engines such as InnoDB, storing an exact row count is more problematic because multiple transactions may be occurring, each of which may affect the count.

Also read this question MySQL - Complexity of: SELECT COUNT(*) FROM MyTable;