I have a table of 200k entries with columns of INT's. I want to create an index to make queries faster. This is the query I would like to execute: SELECT A,B,C,D,E FROM table WHERE A=23 and (B=45 or C=43). I created the following indexes: B, ACD, C, ABC.
我有一张200k的表格，里面有INT的列。我想创建一个索引以使查询更快。这是我想执行的查询:从A=23和(B=45或C=43)的表中选择A、B、C、D、E。我创建了以下索引:B、ACD、C、ABC。
With the EXPLAIN command I found that MySQL chooses the index ACD. So I kept populating the table with more values and I realized that MySQL was switching between the indexes above (not always the same one).
使用EXPLAIN命令，我发现MySQL选择索引ACD。所以我不断地用更多的值填充这个表，我意识到MySQL在上面的索引之间进行切换(并不总是相同的)。
Since there are many inserts, having various indexes will cause performance issues and we can assume that this table is accessed by other queries that require different columns where every index makes sense.
由于有许多插入，拥有不同的索引将导致性能问题，我们可以假设这个表被其他查询访问，这些查询需要不同的列，每个索引都有意义。
I am aware of the USE INDEX(), but I would like to understand if we should trust MySQL to choose the right index.
我知道USE INDEX()，但是我想知道我们是否应该信任MySQL来选择正确的索引。
2 个解决方案
#1
6
Because of the OR in the SQL statement, MySQL is simply getting the first index that includes A, which is ACD.
由于SQL语句中的OR, MySQL只获得第一个包含A的索引，即ACD。
I came to the conclusion that the way to solve this issue using an INDEX is to make two separate queries. SELECT A,B,C,D,E FROM table WHERE A=23 AND B=45 which will use the INDEX ABC and then SELECT A,B,C,D,E FROM table WHERE A=23 AND C=43 which will use INDEX ACD. This can all be done in one step with (...) UNION (...), which turns out to be quicker and only uses INDEX's.
我得出的结论是，使用索引来解决这个问题的方法是进行两个单独的查询。从A=23和B=45的表中选择A、B、C、D、E，使用索引ABC，然后从A=23和C=43的表中选择A、B、C、D、E，使用索引ACD。这可以通过(…)一步完成。UNION(…)，它更快，只使用INDEX's。
#2
1
Mysql can only use the 'left most' part of a composite index. Ie, if your where clause looks like
Mysql只能使用复合索引的“最左”部分。如果你的where子句是这样的
WHERE A=1 AND B=2
then MySQL can use an index on A or AB, but AB would be best. ACB could not be used as the index is split by another column
MySQL可以在A或AB上使用索引，但AB最好。不能使用ACB，因为索引被另一列分割
With your given WHERE clause, I don't think the MySQL query engine can utilize multiple indexes. I would make an AB and AC and using FORCE INDEX, see which one is faster.
有了给定的WHERE子句，我认为MySQL查询引擎不能使用多个索引。我会做一个AB和AC用力指数，看看哪个更快。
A three column composite key won't help in this case as you are looking up against A and one other column. This sort of key would help if your query was using AND instead of OR. An index on ABC would be useful for
在这种情况下，三列组合键不起作用，因为您正在查找A和另一列。如果您的查询正在使用，而不是OR，那么此类键将会有所帮助。ABC上的索引将会有用
WHERE A=1 AND B=2 AND C=3
#1
6
Because of the OR in the SQL statement, MySQL is simply getting the first index that includes A, which is ACD.
由于SQL语句中的OR, MySQL只获得第一个包含A的索引，即ACD。
I came to the conclusion that the way to solve this issue using an INDEX is to make two separate queries. SELECT A,B,C,D,E FROM table WHERE A=23 AND B=45 which will use the INDEX ABC and then SELECT A,B,C,D,E FROM table WHERE A=23 AND C=43 which will use INDEX ACD. This can all be done in one step with (...) UNION (...), which turns out to be quicker and only uses INDEX's.
我得出的结论是，使用索引来解决这个问题的方法是进行两个单独的查询。从A=23和B=45的表中选择A、B、C、D、E，使用索引ABC，然后从A=23和C=43的表中选择A、B、C、D、E，使用索引ACD。这可以通过(…)一步完成。UNION(…)，它更快，只使用INDEX's。
#2
1
Mysql can only use the 'left most' part of a composite index. Ie, if your where clause looks like
Mysql只能使用复合索引的“最左”部分。如果你的where子句是这样的
WHERE A=1 AND B=2
then MySQL can use an index on A or AB, but AB would be best. ACB could not be used as the index is split by another column
MySQL可以在A或AB上使用索引，但AB最好。不能使用ACB，因为索引被另一列分割
With your given WHERE clause, I don't think the MySQL query engine can utilize multiple indexes. I would make an AB and AC and using FORCE INDEX, see which one is faster.
有了给定的WHERE子句，我认为MySQL查询引擎不能使用多个索引。我会做一个AB和AC用力指数，看看哪个更快。
A three column composite key won't help in this case as you are looking up against A and one other column. This sort of key would help if your query was using AND instead of OR. An index on ABC would be useful for
在这种情况下，三列组合键不起作用，因为您正在查找A和另一列。如果您的查询正在使用，而不是OR，那么此类键将会有所帮助。ABC上的索引将会有用
WHERE A=1 AND B=2 AND C=3