分解
OR谓词的典型方法是使用
UNIOn。
请注意，您的示例不太适合您的索引。即使您
field1从谓词中省略了，您也将拥有
field2 >= 1000 OR field3 >=2000，它不能使用索引。如果您在
(field1,field2)和
(field1,field3)或（
field2或）上
field3分别有索引，您将获得一个相当快速的查询。
SELECt COUNT(*) FROM(SELECt * FROM table WHERe field1 = 'value' AND field2 >= 1000UNIOnSELECt * FROM table WHERe field1 = 'value' AND field3 >= 2000) T
请注意，您必须为派生表提供别名，这就是为什么子查询被别名为的原因
T。
一个真实的例子。列名和表名已匿名！
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 = value1UNIOn 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 = value1UNIOn ALL SELECt * FROM table WHERe columnB = value2) T;+----------+| COUNT(*) |+----------+| 2015 |+----------+1 row in set (0.12 sec)
