7

I have a query on my database as such:

SELECT * FROM expenses WHERE user_id = ? AND dated_on = ?

I have added an index to the table on both the user_id and dated_on columns. When I inspect the indexes using SHOW INDEXES FROM expenses, there are two lines -- one with a seq_in_index value of 1, the other with a seq_in_index value of 2.

My question is, if I then submit a query which uses only one of the two WHERE clauses, e.g.:

SELECT * FROM expenses WHERE user_id = ?

Is there any benefit to creating another index which solely indexes the user_id column, or will the user_id/dated_on index described above be used just as efficiently?

Finally, how about if use the query:

SELECT * FROM expenses WHERE dated_on = ?

How does the seq_in_index value of 2 affect index use and performance in this situation?

Improve this question

2 Answers 2

Reset to default
8

MySQL can use any left portion of an index.

In your example SELECT * FROM expenses WHERE user_id = ? will use the index but SELECT * FROM expenses WHERE dated_on = ? won't.

For a 3-column index A, B, C, WHERE A = ? AND B = ? will use an index over A and B, but WHERE A = ? AND C = ? will only use an index on A

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

5

If your index on user_id and dated_on is really in that order (user_id first), then it will be used for a user_id query also. You can check by using EXPLAIN to see the actual strategy for the query.

Improve this answer

1 Comment

I'm actually interested to know how the seq_in_index value changes the query performance, so I've edited the question accordingly

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.