Q:

MySQL Query Performance (InnoDB)

I have a InnoDB table with ~50MB (~500.000 entries).
Table Structure: iduser_idinfo

Querys:
I would like to get the next entry of this user that has the ID higher than 10:
SELECT * FROM `table` WHERE `user_id`=1 AND `id` > 10 LIMIT 1
 (Response: ~0.5ms)

I would like to get the next entry of this user that has the ID higher than 300000:
SELECT * FROM `table` WHERE `user_id`=1 AND `id` > 300000 LIMIT 1
 (Response: ~215ms)

I tried several index, but no one decreased the response time:
ALTER TABLE table ADD INDEX (user_id, id);

How can I improve the performance in this case?

A:

Provided that id is your PRIMARY KEY, you should create this index:
ALTER TABLE table ADD INDEX (user_id);

and add an ORDER BY condition to your query:
SELECT  *
FROM    `table`
WHERE   `user_id` = 1
        AND `id` > 300000
ORDER BY
        id
LIMIT   1

The index will be in fact on (user_id, id), as the primary key columns missing in the index keys are implicitly a trailing part of every InnoDB secondary index.

