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.
我在user_id和dated_on列上添加了一个索引。当我使用SHOW INDEXES FROM expenses检查索引时,有两行 - 一行的seq_in_index值为1,另一行的seq_in_index值为2。
My question is, if I then submit a query which uses only one of the two WHERE clauses, e.g.:
我的问题是,如果我然后提交一个只使用两个WHERE子句中的一个的查询,例如:
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?
创建另一个索引单独索引user_id列有什么好处,或者上面描述的user_id / dated_on索引是否同样有效使用?
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?
在这种情况下,seq_in_index值2如何影响索引使用和性能?
2 个解决方案
#1
8
MySQL can use any left portion of an index.
MySQL可以使用索引的任何左侧部分。
In your example SELECT * FROM expenses WHERE user_id = ? will use the index but SELECT * FROM expenses WHERE dated_on = ? won't.
在您的示例SELECT * FROM expenses WHERE user_id =?将使用索引,但SELECT * FROM费用WHERE dated_on =?惯于。
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
对于3列索引A,B,C,WHERE A =? AND B =?将使用A和B上的索引,但WHERE A =?和C =?只会在A上使用索引
#2
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.
如果user_id和dated_on上的索引确实按此顺序(user_id优先),那么它也将用于user_id查询。您可以使用EXPLAIN进行检查以查看查询的实际策略。
#1
8
MySQL can use any left portion of an index.
MySQL可以使用索引的任何左侧部分。
In your example SELECT * FROM expenses WHERE user_id = ? will use the index but SELECT * FROM expenses WHERE dated_on = ? won't.
在您的示例SELECT * FROM expenses WHERE user_id =?将使用索引,但SELECT * FROM费用WHERE dated_on =?惯于。
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
对于3列索引A,B,C,WHERE A =? AND B =?将使用A和B上的索引,但WHERE A =?和C =?只会在A上使用索引
#2
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.
如果user_id和dated_on上的索引确实按此顺序(user_id优先),那么它也将用于user_id查询。您可以使用EXPLAIN进行检查以查看查询的实际策略。