Why is count(*) query slow on some tables but not on others?

Question

I've got a mysql database running on a wamp server that I'm using to do frequent pattern mining of Flickr data. In the process of loading the data into the database, I ran a count query to determine how many images I had already loaded. I was surprised that it took 3 minutes 49 sec for

select count(*) from image;

In a separate table, "concept", I am storing a list of tags that users give their images. A similar query on the "concept" table took 0.8 sec. The mystery is that both tables have around 200,000 rows. select count(*) from image; returns 283,890 and select count(*) from concept; returns 213,357.

Here's the description of each table

Screenshot of mysql console with table descriptions

Clearly the "image" table has larger rows. I thought that perhaps "image" was too big to hold in memory based on this blog post, so I also tested the size of the tables using code from this answer.

SELECT table_name AS "Tables", 
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME"
ORDER BY (data_length + index_length) DESC;

"image" is 179.98 MB, "concept" is 15.45 MB

I'm running mysql on a machine with 64 GB of RAM, so both these tables should easily fit. What am I missing that is slowing down my queries? And how can I fix it?

What storage engine are the two tables using? Is `image` innoDB? — Interrobang, Mar 04 '15 at 18:42
What if you do count(id) instead does that change the time needed to execute? — Aziz, Mar 04 '15 at 18:44
@Interrobang Yes, both are innoDB. Is this a bad choice for "image"? — Cecilia, Mar 04 '15 at 18:45
@Aziz I tried using count(id), it took 3 minutes 13 sec for "image", so only 30 sec better. — Cecilia, Mar 04 '15 at 18:46
@BK435 I think this is what Aziz asked. See my previous comment. If you mean something different, can you give more detail? — Cecilia, Mar 04 '15 at 19:06
@BK435 Sorry for the confusion. The query result that I reported to Aziz was actually for count(url), on the PK. I just assumed that his question was about the PK and not the field "id". I didn't think I needed an auto incremented field because "url" is a unique value for each row. — Cecilia, Mar 04 '15 at 19:19
That performance is so bad you're probably benchmarking the time it takes for MySQL to load the table from disk. If you ran it a second time it will likely be cached and much, much faster. Be sure you run the queries several times, and change the order in which you run them. — Schwern, Mar 04 '15 at 19:25

Marcus Adams · Accepted Answer · 2015-03-04T20:06:00.003

When performing SELECT COUNT(*) on an InnDB table, MySQL must scan through an index to count the rows. In this case, your only index is the primary (clustered) index, so MySQL scans through that.

For the clustered index, the actual table data is stored there as well. Not including overhead, your image table is approximately 1973 bytes per row (I'm assuming a single-byte character set for both primary key columns). That's about 8 records max per (16k) page, so about 35,486 pages. Your comcept table is approximately 257 bytes per row. That's about 63 records per page, so about 3,386 pages. That's a huge difference in the amount of data that must be scanned.

It has to read each page entirely because the pages may not be entirely full.

Then, performance wise, perhaps some of those pages are in memory and some are not. There are also some marginal differences due to MySQL's 15/16 preference, but all numbers above should be considered approximations.

Solution

Adding a secondary index to the larger table should yield approximately the same performance for SELECT COUNT(*) as the smaller table. Of course, with another index to update, updates will be a bit slower.

For improved performance, shorten your primary key because secondary indexes include the indexed column(s) and the full primary key.

If you only need an estimated number of rows, you can use the rows value from one of the following, which uses the table statistics instead of scanning the index:

SHOW TABLE STATUS LIKE 'image'

or

EXPLAIN SELECT COUNT(*) FROM image

There will be some tradeoff if you add a secondary index. Updates will take longer, but SELECT COUNT(*) will be faster. To improve even more, use a smaller primary key because each record in a secondary index has the indexed column(s) and the primary key. — Marcus Adams, Mar 04 '15 at 19:55
So I replaced the primary key with an auto-incrementing integer primary key, and the result is super speedy, 0.05 sec. Thanks! — Cecilia, Mar 04 '15 at 23:51
Now, you see why I mentioned having an AI PK value as first column in your table, regardless if your varchar field is unique. IMHO, It is good practice, unless you can make the case, to put AI PK value as first column in all your tables. — BK435, Mar 05 '15 at 19:41

score 2 · Answer 2 · answered Mar 04 '15 at 19:29

2

If you're looking for a ballpark number rather than an exact count, then the Rows column from show table status may be good enough. It's not always accurate for InnoDB tables, but it seems like you're probably ok with a rough estimate anyway.

answered Mar 04 '15 at 19:29

dpw

1,576
9
14

Why is count(*) query slow on some tables but not on others?

2 Answers2