Luciano Nascimento Luciano Nascimento - 7 months ago 14
SQL Question

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?

Answer

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.

Comments