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

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



Or, the alternative:


// 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 question is related to mysql performance

The answer is

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:


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).

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

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"];

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).



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

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


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

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


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.

Try this:

    table_rows "Rows Count"

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

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.

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


In my benchmark test: 0.448s

enter image description here

This query takes 4.835s


enter image description here

count * takes 25.675s

SELECT count(*) FROM `orders`;

enter image description here

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.

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


This was much faster for me than


Questions with mysql tag:

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client php mysqli_connect: authentication method unknown to the client [caching_sha2_password] phpMyAdmin on MySQL 8.0 Authentication plugin 'caching_sha2_password' cannot be loaded Error loading MySQLdb Module 'Did you install mysqlclient or MySQL-python?' select rows in sql with latest date for each ID repeated multiple times How to find MySQL process list and to kill those processes? Access denied; you need (at least one of) the SUPER privilege(s) for this operation Import data.sql MySQL Docker Container PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers Hibernate Error executing DDL via JDBC Statement Your password does not satisfy the current policy requirements MySql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Laravel: PDOException: could not find driver Default password of mysql in ubuntu server 16.04 #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’ Job for mysqld.service failed See "systemctl status mysqld.service" Laravel Migration Error: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by MySQL Error: : 'Access denied for user 'root'@'localhost' Unable to start the mysql server in ubuntu How to turn on/off MySQL strict mode in localhost (xampp)? How to store Emoji Character in MySQL Database ERROR 1698 (28000): Access denied for user 'root'@'localhost' What is the meaning of <> in mysql query? The Response content must be a string or object implementing __toString(), "boolean" given after move to psql Xampp-mysql - "Table doesn't exist in engine" #1932 #1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by MySQL fails on: mysql "ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded" How to insert TIMESTAMP into my MySQL table? How to create a foreign key in phpmyadmin JPA Hibernate Persistence exception [PersistenceUnit: default] Unable to build Hibernate SessionFactory PHP: Inserting Values from the Form into MySQL #1292 - Incorrect date value: '0000-00-00' WooCommerce: Finding the products in database ERROR 1067 (42000): Invalid default value for 'created_at' SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost' SQL query to check if a name begins and ends with a vowel MySQL: When is Flush Privileges in MySQL really needed? Error in MySQL when setting default value for DATE or DATETIME

Questions with performance tag:

Why is 2 * (i * i) faster than 2 * i * i in Java? What is the difference between spark.sql.shuffle.partitions and spark.default.parallelism? How to check if a key exists in Json Object and get its value Why does C++ code for testing the Collatz conjecture run faster than hand-written assembly? Most efficient way to map function over numpy array The most efficient way to remove first N elements in a list? Fastest way to get the first n elements of a List into an Array Why is "1000000000000000 in range(1000000000000001)" so fast in Python 3? pandas loc vs. iloc vs. at vs. iat? Android Recyclerview vs ListView with Viewholder Android studio takes too much memory Increasing Heap Size on Linux Machines Replacing a 32-bit loop counter with 64-bit introduces crazy performance deviations with _mm_popcnt_u64 on Intel CPUs Swift Beta performance: sorting arrays Is Laravel really this slow? Are list-comprehensions and functional functions faster than "for loops"? Why is printing "B" dramatically slower than printing "#"? What is the runtime performance cost of a Docker container? Apache Spark: map vs mapPartitions? Controlling fps with requestAnimationFrame? Array vs ArrayList in performance Why shouldn't I use PyPy over CPython if PyPy is 6.3 times faster? apache server reached MaxClients setting, consider raising the MaxClients setting Split a large dataframe into a list of data frames based on common value in column Fastest way to determine if record exists for or while loop to do something n times Array vs. Object efficiency in JavaScript Python readlines() usage and efficient practice for reading What is a "cache-friendly" code? Most efficient way to concatenate strings in JavaScript? Get the second largest number in a list in linear time Why is it faster to check if dictionary contains the key, rather than catch the exception in case it doesn't? Java check if boolean is null High CPU Utilization in java application - why? How to pass values across the pages in ASP.net without using Session Java: int[] array vs int array[] Fastest way to ping a network range and return responsive hosts? List of all unique characters in a string? JavaScript style.display="none" or jQuery .hide() is more efficient? How to convert a huge list-of-vector to a matrix more efficiently? Fastest way to check a string is alphanumeric in Java Command-line Tool to find Java Heap Size and Memory Used (Linux)? When to use CouchDB over MongoDB and vice versa Is < faster than <=? Fastest way to check if a string matches a regexp in ruby? Getting HTTP code in PHP using curl Measure the time it takes to execute a t-sql query How to write a large buffer into a binary file in C++, fast? How to reduce the image size without losing quality in PHP postgresql COUNT(DISTINCT ...) very slow