Stack Stack - 1 year ago 129
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:


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


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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download