Performance of two similar Selects

[Jay Klein]

Hello,
I have a table with 5 columns, 2 indices other than the primary key, and 100 million rows. The program accessing this table performs only SELECTs, and a lot of them in a sequential manner, primarily of two types: one matching on one index, the other on the other index. One type executes in under a tenth of a second, while the other takes 3-30 seconds. So I'm trying to figure out the difference between them. Here's the EXPLAIN output for each:

The slow one:
mysql> EXPLAIN SELECT * FROM ratings WHERE user = 2361492 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ratings
type: ref
possible_keys: user
key: user
key_len: 4
ref: const
rows: 131
Extra:
1 row in set (0.00 sec)

The fast one:
mysql> EXPLAIN SELECT * FROM ratings WHERE movie_id = 2000 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ratings
type: ref
possible_keys: movie_id
key: movie_id
key_len: 4
ref: const
rows: 31161
Extra:
1 row in set (0.27 sec)

Despite the much greater number of rows in this particular example, the second query executed in 0.79 seconds, and the first one in 2.45. Generally the number of rows returned/examined is similar between the two types, between a few hundred and a few thousand. In the slow query log, I saw that for the slow queries the number of rows examined was the same as the number returned.

The only differences I can see between the two indexes/queries are:

1. movie_id is a 6 digit int ranging from 0 to 18000 or so with no gaps, while 'user' is a 7 digit int with gaps.

2. Not really a difference, but I was surprised to see that for both columns the 'key_len' is shorter than the column length.

Could either of these things produce this behavior? It will be quite time-consuming to drop an index and recreate it with different settings, so I want to have an idea that it might work before I try it. Thanks a lot for any help you can offer.

This is MySQL 5.0.21 on Mac OS X with 512MB RAM.