Hi Jay,
Not immediately sure what your problem is - suspect that is might just be that your indexes don't fit into memory very well, and that the index on 'user' is bigger so a larger proportion of it is not buffered in memory.
What's your key_buffer_size? Is it set to something large enough to fit both of these indexes into? (might not be possible with only 512MB RAM on a 100M row table, I'm not sure..)
The 3-30 second query time is pretty awful for that kind of query, which suggests lots of disk activity is going on. Is the data very fragmented do you think? When was the last time you ran optimize? What does your system's disk activity look like when you're running the query. (see: vmstat/iostat)
Is the table the static row MyISAM type? If not and it's dynamic, look to see if you can change your column definitions to allow it to be static. I believe that you'll see some performance gains.
Also, don't use
select *
unless you have to - if you can pull out just a few cols you might get some benefit.
BTW - when you say your cols are 6 or 7 digit INT - you might be getting confused about they way MySQL deals with this - INTEGERs are always stored as the same size (4 bytes) - the 'length' you specify is just the padding/display size. It's more than confusing though, I admit. See:
http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
(so the 'key_len' is 4 for a 4 byte INT in both cases)
BTW the following info would help to work out what's going on:
show create table ratings;
show table status like 'ratings';
show index from ratings;
HTH,
Toasty
-----------------------------------------
email: 'toasty'*3 at gmail