MySQL:如何索引“OR"条款
我正在执行以下查询
SELECT COUNT(*)
FROM table
WHERE field1='value' AND (field2 >= 1000 OR field3 >= 2000)
field1 上有一个索引，field2&field3 上有另一个组合.
There is one index over field1 and another composited over field2&field3.
我看到 MySQL 总是选择 field1 索引，然后使用其他两个字段进行连接，这很糟糕，因为它需要连接 146.000 行.
I see MySQL always selects the field1 index and then makes a join using the other two fields which is quite bad because it needs to join 146.000 rows.
关于如何改进的建议?谢谢
Suggestions on how to improve this? Thanks
(尝试提出的解决方案后进行编辑)
(EDIT AFTER TRYING SOLUTION PROPOSED)
基于提出的解决方案，我在玩这个时在 Mysql 上看到了这个.
Based in the solution proposed I've seen this on Mysql when playing with this.
SELECT COUNT(*) FROM (SELECT * FROM table WHERE columnA = value1
UNION SELECT * FROM table WHERE columnB = value2) AS unionTable;
比执行慢很多:
SELECT COUNT(*)
FROM table
WHERE (columnA = value1 AND columnB = value2)
OR (columnA = value1 AND columnC = value3)
有两个复合索引:
index1 (columnA,columnB)
index2 (columnA,columnC)
有趣的是，要求 Mysql解释"它在这两种情况下总是采用 index1 的查询，并且不使用 index2.
Interesting enough is that asking Mysql to "explain" the query it's taking always index1 on both cases and index2 is not used.
如果我将索引更改为:
index1 (columnB,columnA)
index2 (columnC,columnA)
查询到:
SELECT COUNT(*)
FROM table
WHERE (columnB = value2 AND columnA = value1)
OR (columnC = value3 AND columnA = value1)
这是我发现 Mysql 工作最快的方法.
Then it's the fastest way I've found Mysql works.
推荐答案
分解 OR 谓词的典型方法是使用 UNION.
The typical way to break up OR predicates is with UNION.
请注意，您的示例不适合您的索引.即使您从谓词中省略了 field1，您也会有 field2 >= 1000 OR field3 >= 2000，它不能使用索引.如果您分别在 (field1, field2) 和 (field1,field3) 或 field2 或 field3 上有索引，你会得到一个相当快的查询.
Note that your example doesn't fit well with your indexes. Even if you omitted field1 from the predicate, you'd have field2 >= 1000 OR field3 >= 2000, which can't use an index. If you had indexes on (field1, field2) and (field1,field3) or field2 or field3 separately, you would get a reasonably fast query.
SELECT COUNT(*) FROM
(SELECT * FROM table WHERE field1 = 'value' AND field2 >= 1000
UNION
SELECT * FROM table WHERE field1 = 'value' AND field3 >= 2000) T
请注意，您必须为派生表提供别名，这就是子查询别名为T的原因.
Note that you have to provide an alias for the derived table, which is why the subquery is aliased as T.
一个真实的例子.列名和表名已匿名！
A real-world example. Column and table names have been anonymized!
mysql> SELECT COUNT(*) FROM table;
+----------+
| COUNT(*) |
+----------+
| 3059139 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM table WHERE columnA = value1;
+----------+
| COUNT(*) |
+----------+
| 1068 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM table WHERE columnB = value2;
+----------+
| COUNT(*) |
+----------+
| 947 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM table WHERE columnA = value1 OR columnB = value2;
+----------+
| COUNT(*) |
+----------+
| 1616 |
+----------+
1 row in set (9.92 sec)
mysql> SELECT COUNT(*) FROM (SELECT * FROM table WHERE columnA = value1
UNION SELECT * FROM table WHERE columnB = value2) T;
+----------+
| COUNT(*) |
+----------+
| 1616 |
+----------+
1 row in set (0.17 sec)
mysql> SELECT COUNT(*) FROM (SELECT * FROM table WHERE columnA = value1
UNION ALL SELECT * FROM table WHERE columnB = value2) T;
+----------+
| COUNT(*) |
+----------+
| 2015 |
+----------+
1 row in set (0.12 sec)
相关文章