MySQL: Fastest way to count number of rows

mysql performance
Franz picture Franz · Feb 20, 2011 · Viewed 213k times · Source

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.

Answer

Mārtiņš Briedis picture Mārtiņš Briedis · Feb 20, 2011

When you COUNT(*) it takes in count column indexes, so it will be the best result. Mysql with MyISAM engine actually stores row count, it doensn't count all rows each time you try to count all rows. (based on primary key's column)

Using PHP to count rows is not very smart, because you have to send data from mysql to php. Why do it when you can achieve the same on the mysql side?

If the COUNT(*) is slow, you should run EXPLAIN on the query, and check if indexes are really used, and where should they be added.

The following is not the fastest way, but there is a case, where COUNT(*) doesn't really fit - when you start grouping results, you can run into problem, where COUNT doesn't really count all rows.

The solution is SQL_CALC_FOUND_ROWS. This is usually used when you are selecting rows but still need to know the total row count (for example, for paging). When you select data rows, just append the SQL_CALC_FOUND_ROWS keyword after SELECT:

SELECT SQL_CALC_FOUND_ROWS [needed fields or *] FROM table LIMIT 20 OFFSET 0;

After you have selected needed rows, you can get the count with this single query:

SELECT FOUND_ROWS();

FOUND_ROWS() has to be called immediately after the data selecting query.

In conclusion, everything actually comes down to how many entries you have and what is in the WHERE statement. You should really pay attention on how indexes are being used, when there are lots of rows (tens of thousands, millions, and up).

Related questions

MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET

What is main difference between INSERT INTO table VALUES .. and INSERT INTO table SET? Example: INSERT INTO table (a, b, c) VALUES (1,2,3) INSERT INTO table SET a=1, b=2, c=3 And what about performance of these two?

Read More
Best way to test if a row exists in a MySQL table

I'm trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this: SELECT COUNT(*) AS total FROM table1 WHERE ... and check to see if the total is non-zero or …

Read More
MySQL vs MongoDB 1000 reads

I have been very excited about MongoDb and have been testing it lately. I had a table called posts in MySQL with about 20 million records indexed only on a field called 'id'. I wanted to compare speed with MongoDB and …

Read More