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-
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.