Now: Select COUNT(*) using an index *period*

[Jason Collison]

Followup:

[BTW the temp path issue was solved by RTFM. *blush* Although the docs do say the temp table is created in the same dir as the original table. (And it was, in fact). No clue on that one.]

But back to the real issue, dropping the second column from the multicolumn index did help, but not by much. Just due to fewer bytes to read when doing the brute-force index scan, I'm assuming. And yes, countrynum could be declared as a TINYINT, but in my table it's not really for a country, and besides, making it a few bytes smaller would only delay the inevitable.

The more I think about this, the more I think something screwy is going on. Asking for say a count of customers by state/country (or really any indexed column) is quite common, and I find it hard to believe I've been blissfully unaware of this behaviour all these years. (I've been working with MySQL ever since I gave up the single-threaded MSQL way back when.) Granted, I haven't personally needed 2+ million rows per unique indexed value too often in the past, but I'm sure a lot of you guys have.

Server version is 4.1.7 and on this particular machine I installed it via a freebsd.org binary package.