Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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