Are full count queries really so slow on a large MySQL InnoDB tables?

Question

We have a large tables with millions of entrys. A full count is pretty slow, see code below. Is this quite common for a MySQL InnoDB table? Is there no way to accelerate this? Even with the query cache it's still "slow". I also wonder, why the count on "communication" table with 2.8 mio entries is slower than the count on "transaction" with 4.5 mio entries.

I'know that it's much faster with a where clause. I just want to know if the bad performance is normal.

We are using Amazon RDS MySQL 5.7 with an m4.xlarge (4 CPU, 16 GB RAM, 500 GB Storage). I've also already tried bigger instances with more CPU and RAM, but there is no big change on the query times.

mysql> SELECT COUNT(*) FROM transaction;
+----------+
| COUNT(*) |
+----------+
|  4569880 |
+----------+
1 row in set (1 min 37.88 sec)

mysql> SELECT COUNT(*) FROM transaction;
+----------+
| count(*) |
+----------+
|  4569880 |
+----------+
1 row in set (1.44 sec)

mysql> SELECT COUNT(*) FROM communication;
+----------+
| count(*) |
+----------+
|  2821486 |
+----------+
1 row in set (2 min 19.28 sec)

Leukas

Possible duplicate of How to optimize COUNT(*) performance on InnoDB by using index – Cordon

More duplicates: https://mcmap.net/q/714145/-optimize-mysql-count-query , https://mcmap.net/q/364985/-mysql-count-performance-on-very-big-tables and https://mcmap.net/q/745960/-should-i-avoid-count-all-together-in-innodb/2469308 – Cordon

Answer 1

In addition to what Bill says...

Smallest index

InnoDB picks the 'smallest' index for doing COUNT(*). It could be that all of the indexes of communication are bigger than the smallest of transaction, hence the time difference. When judging the size of an index, include the PRIMARY KEY column(s) with any secondary index:

PRIMARY KEY(id),   -- INT (4 bytes)
INDEX(flag),       -- TINYINT (1 byte)
INDEX(name),       -- VARCHAR(255) (? bytes)

For measuring size, the PRIMARY KEY has big since it includes (due to clustering) all the columns of the table. INDEX(flag) is "5 bytes". INDEX(name) probably averages a few dozen bytes. SELECT COUNT(*) will clearly pick INDEX(flag).

Apparently transaction has a 'small' index, but communication does not.

TEXT/BLOG columns are sometimes stored "off-record". Hence, they do not count in the size of the PK index.

Query Cache

If the "Query cache" is turned on, the second running of a query may be immensely faster than the first. But that is only if there were no changes to the table in the mean time. Since any change to the table invalidates all QC entries for that table, the QC is rarely useful in production systems. By "faster" I mean on the order of 0.001 seconds; not 1.44 seconds.

The difference between 1m38s and 1.44s is probably due to what was cached in the buffer_pool -- the general caching area for InnoDB. The first run probably found none of the 'smallest' index in RAM so it did a lot of I/O, taking 98 seconds to fetch all 4.5M rows of that index. The second run found all that data cached in the buffer_pool, so it ran at CPU speed (no I/O), hence much faster.

Good Enough

In situations like this, I question the necessity of doing the COUNT(*) at all. Notice how you said "2.8 mio entries", as if 2 significant digits was "good enough". If you are displaying the count to users on a UI, won't that be "good enough"? If so, one solution to the performance is to do the count once a day and store it some place. This would allow instantaneous access to a "good enough" value.

There are other techniques. One is to keep the counter updated, either with active code, or with some form of Summary Table.

Throwing hardware at it

You already found that changing the hardware did not help.

• The 98s was as fast as any of RDS's I/O offerings can run.
• The 1.44s was as fast as any one RDS CPU can run.
• MySQL (and its variants) do not use more than one CPU per query.
• You had enough RAM so the entire 'small' index would fit in the buffer_pool until your second SELECT COUNT(*).. (Too little RAM would have led the second running to be very slow.)

Pipeline

wow, Bill and Rick, thanks a lot for this detailled answer! I understand now the issue. I would select both posts as "answert" but unfortunately I can only select one ;) – Leukas

Answer 2

This is the downside of using a database storage engine that supports multi-versioning concurrency control (MVCC).

InnoDB allows your query to be isolated in a transaction, without blocking other concurrent clients who are reading and writing rows of data. Those concurrent updates don't affect the view of data your transaction has.

But what is the count of rows in the table, given that many of the rows are in progress of being added or deleted while you're doing the count? The answer is fuzzy.

Your transaction shouldn't be able to "see" row versions that were created after your transaction started. Likewise, your transaction should count rows even if someone else has requested they be deleted, but they did so after your transaction started.

The answer is that when you do a SELECT COUNT(*) — or any other type of query that needs to examine many rows — InnoDB has to visit every row, to see which is the current version of that row visible to your transaction's view of the database, and count it if it's visible.

In a table that doesn't support transactions or concurrent updates, like MyISAM, the storage engine keeps the total count of rows as metadata for the table. This storage engine can't support multiple threads updating rows concurrently, so the total count of rows is less fuzzy. So when you request SELECT COUNT(*) from a MyISAM table, it just returns the count of rows it has in memory (but this isn't useful if you do SELECT COUNT(*) with a WHERE clause to count some subset of rows by some condition, so it has to actually count them in that case).

In general, most people find InnoDB's support for concurrent updates is worth a lot, and they are willing to sacrifice the optimization of SELECT COUNT(*).

Chicory

wow, Bill and Rick, thanks a lot for this detailled answer! I understand now the issue. I would select both posts as "answert" but unfortunately I can only select one ;) – Leukas