[mysql] MySQL: Fastest way to count number of rows

Which way to count a number of rows should be faster in MySQL?

This:

SELECT COUNT(*) FROM ... WHERE ...

Or, the alternative:

SELECT 1 FROM ... WHERE ...

// and then count the results with a built-in function, e.g. in PHP mysql_num_rows()

One would think that the first method should be faster, as this is clearly database territory and the database engine should be faster than anybody else when determining things like this internally.

This query (which is similar to what bayuah posted) shows a nice summary of all tables count inside a database: (simplified version of stored procedure by Ivan Cachicatari which I highly recommend).

SELECT TABLE_NAME AS 'Table Name', TABLE_ROWS AS 'Rows' FROM information_schema.TABLES WHERE TABLES.TABLE_SCHEMA = '`YOURDBNAME`' AND TABLES.TABLE_TYPE = 'BASE TABLE'; 

Example:

+-----------------+---------+
| Table Name      | Rows    |
+-----------------+---------+
| some_table      |   10278 |
| other_table     |     995 |

Try this:

SELECT
    table_rows "Rows Count"
FROM
    information_schema.tables
WHERE
    table_name="Table_Name"
AND
    table_schema="Database_Name";

I've always understood that the below will give me the fastest response times.

SELECT COUNT(1) FROM ... WHERE ...

I did some benchmarks to compare the execution time of COUNT(*) vs COUNT(id) (id is the primary key of the table - indexed).

Number of trials: 10 * 1000 queries

Results: COUNT(*) is faster 7%

VIEW GRAPH: benchmarkgraph

My advice is to use: SELECT COUNT(*) FROM table

I handled tables for the German Government with sometimes 60 million records.

And we needed to know many times the total rows.

So we database programmers decided that in every table is record one always the record in which the total record numbers is stored. We updated this number, depending on INSERT or DELETE rows.

We tried all other ways. This is by far the fastest way.

Great question, great answers. Here's a quick way to echo the results if anyone is reading this page and missing that part:

$counter = mysql_query("SELECT COUNT(*) AS id FROM table");
$num = mysql_fetch_array($counter);
$count = $num["id"];
echo("$count");

After speaking with my team-mates, Ricardo told us that the faster way is:

show table status like '<TABLE NAME>' \G

But you have to remember that the result may not be exact.

You can use it from command line too:

$ mysqlshow --status <DATABASE> <TABLE NAME>

More information: http://dev.mysql.com/doc/refman/5.7/en/show-table-status.html

And you can find a complete discussion at mysqlperformanceblog

This is the best query able to get the fastest results.

SELECT SQL_CALC_FOUND_ROWS 1 FROM `orders`;
SELECT FOUND_ROWS();

In my benchmark test: 0.448s

This query takes 4.835s

SELECT SQL_CALC_FOUND_ROWS * FROM `orders`;
SELECT FOUND_ROWS();

count * takes 25.675s

SELECT count(*) FROM `orders`;

If you need to get the count of the entire result set you can take following approach:

SELECT SQL_CALC_FOUND_ROWS * FROM table_name LIMIT 5;
SELECT FOUND_ROWS();

This isn't normally faster than using COUNT albeit one might think the opposite is the case because it's doing the calculation internally and doesn't send the data back to the user thus the performance improvement is suspected.

Doing these two queries is good for pagination for getting totals but not particularly for using WHERE clauses.

EXPLAIN SELECT id FROM .... did the trick for me. and I could see the number of rows under rows column of the result.

A count(*) statement with a where condition on the primary key returned the row count much faster for me avoiding full table scan.

SELECT COUNT(*) FROM ... WHERE <PRIMARY_KEY> IS NOT NULL;

This was much faster for me than

SELECT COUNT(*) FROM ...

Perhaps you may want to consider doing a SELECT max(Id) - min(Id) + 1. This will only work if your Ids are sequential and rows are not deleted. It is however very fast.