Counting the total numbers of records in MySQL DB

Question

I have to count the total numbers of records from my database. I want to know the efficient/faster way for MySQL to do this. I can use:

(1)

$rslt=mysql_query("select * from table");
$count = mysql_num_rows($rslt);

or (2) by using the following query:

select count(*) from table;

Which QUERY to the database is efficient?

Usually the best way to find out which query is fastest is to just run it and see. — Cameron Skinner, Sep 14 '14 at 02:45
FYI, this doesn't count the number of records in the DB, just in one table of the DB. You'll have to repeat it for every table to get the total for the DB. — Barmar, Sep 14 '14 at 02:56
i meant to say that @Barmar I want to count the rows in the table "table" — Parveez Ahmed, Sep 14 '14 at 02:58

score 3 · Accepted Answer · answered Sep 14 '14 at 02:54

3

The first (select * from ...) will have to actually read the rows, while the second will return an int from the database. The second will be much faster with large tables.

answered Sep 14 '14 at 02:54

Jim Garrison

85,615
20
155
190

score 0 · Answer 2 · answered Sep 14 '14 at 02:59

If your table is really large I would expect the 2nd query to outperform the first.

It does not need to actually fetch a ton of data from DB to client. SELECT * is a horrible idea in such case. SELECT 1 would not have to load every field.
If your table is indexed on its primary key, RDBMS might be able to get the result excursively from index.

Counting the total numbers of records in MySQL DB

2 Answers2