How to get the number of rows in a MySQL version 4 table without using the count (*) query?

Question

I know how to get Number of Rows in a table in MySQL version 5 and later.

MySQL 5

select table_rows from tables where table_name = 'tableName';

I want to know how to get this in MySQL 4 because the count (*) query takes 4-5 minutes in large tables.

Answer 1

SELECT COUNT(*) indicates that the database engine is still iterating over individual fields or each record. Since you only care about the number of lines, you can try @ dj2 and do

SELECT COUNT (1) FROM <TableName>

But I wonder if you get better performance by referring to the primary key by querying your cluster index.

SELECT COUNT (<PrimaryKey>) FROM <TableName>

Answer 2

INFORMATION_SCHEMA , which contains table_rows , is not available for MySql 4.0. It is best to optimize count () by doing count () on the indexed key.