I have a large-ish InnoDB table (about 800MB), and I'd like to count rows. I know SELECT COUNT(*) has issues in InnoDB, but one of the solutions I've seen is to create a secondary index, which is much faster than chewing through the PRIMARY, at least for row counts.

I've copied the table contents to a second server. The server's faster, and of course the copy resulted in a nice, compact table that hasn't suffered deletes and updates. So on the new server everything's nice and fast, but something about the behavior of the old server still puzzles me:

I have a PRIMARY index, and another index (call it index2) already defined on the table. On the old server (MySQL 5.0.27), I can EXPLAIN SELECT COUNT(*) FROM myTable and see that it will use the PRIMARY index. The query takes over a minute. If I SELECT COUNT(*) FROM myTable USE INDEX(index2), it takes ~100ms. If I put WHERE someCol > lowest_possible_value it also chooses the faster index.

On the new server (MySQL 5.1.52), the same EXPLAIN statement tells me it's going to use index2, without any hinting. A simple SELECT COUNT(*) FROM myTable is very fast, on the order of 20-30ms. I can run SELECT COUNT(*) FROM myTable USE INDEX(PRIMARY), to force it use the "bad" index, and it takes longer -- only 3-4 seconds, but as I said there are other differences to account for that, and that's still more than a hundred times slower than the "good" (non-PRIMARY) index.

Why is the newer MySQL instance picking the "right" index? Can I do something on the old server to reproduce this behavior? I'd like to avoid a full upgrade just now, but it's not out of the question.


Two possibilities:

  1. The fact that it's a new table means that the statistics that MySQL uses to try and estimate which index is the right one to use are different.
  2. The newer version of MySQL is smarter.

Fixing the former may be as simple as an ANALYZE TABLE; fixing the latter is just a package upgrade.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.