MySQL: how to index an “OR” clause

by

The typical way to break up OR predicates is with UNION.

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

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)

More Related Contents:

  1. WHERE vs HAVING
  2. Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?
  3. Is it possible to have function-based index in MySQL?
  4. When should I use a composite index?
  5. How do MySQL indexes work?
  6. Detect if value is number in MySQL
  7. Best type of indexing when there is LIKE clause [duplicate]
  8. Does MySQL index foreign key columns automatically?
  9. What does the KEY keyword mean?
  10. Is the primary key automatically indexed in MySQL?
  11. Difference between Key, Primary Key, Unique Key and Index in MySQL
  12. MySQL indexes – what are the best practices?
  13. What is cardinality in MySQL?
  14. How do I get MySQL to use an INDEX for view query?
  15. DOs and DONTs for Indexes [closed]
  16. MySQL performance: multiple tables vs. index on single table and partitions
  17. Mysql covering vs composite vs column index
  18. Is there any performance gain in indexing a boolean field?
  19. MySQL – ORDER BY values within IN()
  20. Does Order of Fields of Multi-Column Index in MySQL Matter
  21. MySQL load data infile – acceleration?
  22. Create an index on a huge MySQL production table without table locking
  23. MySQL Integer vs DateTime index
  24. Can MySQL use multiple indexes for a single query?
  25. How to fix MySql: index column size too large (Laravel migrate)
  26. MySQL optimizing INSERT speed being slowed down because of indices
  27. Would it help to add index to BIGINT column in MySQL?
  28. MySQL – why not index every field?
  29. Workaround in mysql for partial Index or filtered Index?
  30. mySQL returns all rows when field=0

Leave a Comment