Select COUNT(*) using key_part_1 performance

[Jason Collison]

In short, SELECT COUNT(*) FROM tbl_name WHERE key_part_one=constant yields extremely poor performance.

Consider this hypothetical table:
CREATE TABLE names
(
countrynum INTEGER(10) UNSIGNED NOT NULL,
surname VARCHAR(255) NOT NULL,
INDEX test_x (countrynum, surname)
);

-- (index intentionally not unique, this is oversimplified)


What I want to do SELECT COUNT(*) FROM names WHERE countrynum=1; and it takes an insane amount of time. I thought this would be extremely quick... obviously I was wrong.

I currently have about 2 million rows in such a table (I did run ANALYZE TABLE) and at present I'm only using 1 country for development purposes. EXPLAIN says it is using the index, but type=index, key_len=259, and rows=1815150. First, I don't understand why key_len is the full length of the key when it only should be using the first ~4 bytes. (Admittedly I'm fairly new to the EXPLAIN command.) Second, I don't know why it has to "examine" all those rows... seems to me this should be more quickly calculated from the index without actually opening it and stepping through millions of bytes. It's also unusual that the rows= value doesn't match the number of rows in the table. (There are substantially more rows in the table.)

What did I do wrong here? And if it just "can't" be done in this manner, is there any workaround short of splitting all countries into separate tables? (yuck!)

Thanks in advance!


P.S. While writing this I tried to create another index on countrynum only, hoping for at least _better_ performance. Alter table crashed with '/var filesystem full' even though I set the database path to /usr/local/blahblah. *sigh* Now I gotta go find out why mysql is trying to use /var as temporary space. If anyone has a quick answer I'd love it. This isn't my day. LOL