mysql or and 索引,MySQL：如何索引“ OR”子句

小编典典

分解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 >= 1000

UNION

SELECT * 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 = 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)

2020-05-17