Stack Stack - 6 months ago 23
MySQL Question

The ordering of indexes group

I read somewhere there is a different between these two indexes groups:


  • ADD KEY id_user (id_user,seen);

  • ADD KEY id_user (seen,id_user);



Well is there? If yes so their order should be based on what parameter?




In reality I have these two queries on one table:

Query1:

SELECT * FROM mytable WHERE id_user = :id AND seen IS NULL


Query2:

SELECT * FROM mytable WHERE id_user = :id AND timestamp > :tm


So what kind of indexes would be proper in my case? Currently I have three separated indexed on
id_user
,
seen
,
timestamp
columns.

Answer

Both your queries have an equality condition on id_user. Hence, either can take advantage of an index where id_user is the first key in the index. So, I would recommend the first index you mention.

MySQL has good documentation on multi-column indexes. I would suggest you start there to learn about them.

Your query can take advantage of indexes on (id_user, seen) and (id_user, timestamp). Probably, the first key is most important. You should try this different indexes and see which best meet your performance goals.