CODEWITHSUNDEEP
mysqldatabase-performance
vy32
vy32

Reputation: 29645

why is mysql select count(1) taking so long?

When I first started using MySQL, a select count(*) or select count(1) was almost instantaneous. But I'm now using version 5.6.25 hosted at Dreamhost, and it's taking 20-30 seconds, sometimes, to do a select count(1). However, the second time it's fast---like the index is cached---but not super fast, like the data are coming from just the metadata index.

Anybody understand what's going on, and why it has changed?

mysql> select count(1) from times;
+----------+
| count(1) |
+----------+
|  1511553 |
+----------+
1 row in set (22.04 sec)

mysql> select count(1) from times;
+----------+
| count(1) |
+----------+
|  1512007 |
+----------+
1 row in set (0.54 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.25-log |
+------------+
1 row in set (0.00 sec)

mysql>

Upvotes: 1

Views: 754

Answers (1)

Solarflare
Solarflare

Reputation: 11106

I guess when you first started, you used MyISAM, and now you are using InnoDB. InnoDB just doesn't store this information. See documentation: Limits on InnoDB Tables

InnoDB does not keep an internal count of rows in a table because concurrent transactions might “see” different numbers of rows at the same time. To process a SELECT COUNT(*) FROM t statement, InnoDB scans an index of the table, which takes some time if the index is not entirely in the buffer pool. To get a fast count, you have to use a counter table you create yourself and let your application update it according to the inserts and deletes it does. If an approximate row count is sufficient, SHOW TABLE STATUS can be used. See Section 9.5, “Optimizing for InnoDB Tables”.

So when your index is entirely in the buffer pool after the (slower) first query, the second query is fast again.

MyISAM doesn't need to care about problems that concurrent transactions might create, because it doesn't support transactions, and so select count(*) from t will just look up and return a stored value very fast.

Upvotes: 4

Related Questions