Re: COUNT(*) very slowly in large InnoDB table

[Dominik Leiner]

Hi!

Thanks a lot for this answer. I always thought that COUNT(*) was generally optimized - never considered that this is only true for MyISAM tables.

Of course I had run the queries multiple times to give caching a chance. The 8.4 seconds were the result after 4 or 5 runs.

Your guess about COUNT(project) was perfectly right: 1.2 instead of 8.7 seconds. I'm impressed. That is because this is a secondary index? For me this is contra-intuitive, I had bet that COUNT(*) should be the quickest...


Also right was your guess about the indices size: the "Index_length" is about 325M. This is 3-times 100M? I tried even more cache (512M), but the COUNT(*) did not improve.

> But, if you have a WHERE clause, all bets are off.

That was what I wondered so much about: Why the query using WHERE was not slower than the COUNT(*) without a WHERE. However, if MySQL needs to load the whole 2.9 GB to check the number of rows, things become more clear. Just why then is it so fast do query a single entry by 'id'? Does that not also need the index? Or is it just that some rows are cached?

Thanks for your help
Dominik



Edited 3 time(s). Last edit at 07/15/2010 09:58AM by Dominik Leiner.