Search code examples
mysqlinnodb

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?

Solution

  • 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.