CODEWITHSUNDEEP
mysqlsqlindexing
Martin AJ
Martin AJ

Reputation: 6707

The ordering of indexes group

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

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.

Upvotes: 0

Views: 34

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

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.

Upvotes: 1

C14L
C14L

Reputation: 12558

Most likely, an index on user_id is enough. But if you have many repetitions of user_ids, then you can make two index groups

ADD KEY id_user1 (id_user, seen);
ADD KEY id_user2 (id_user, timestamp);

The select will first use user_id to reduce the number or matches, then the second field in the index, either timestamp or seen.

For a select that looks for user_id and seen, these two indexes

ADD KEY id_user1 (id_user,seen);
ADD KEY id_user2 (seen,id_user);

will have mostly the same result.

In any case, you should use EXPLAIN SELECT * FROM ... to check the execution of slow queries, so you can see exactly where it is slow, and if your indexes are actually used as expected.

Upvotes: 1

Related Questions