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.