MySQL: how to index an "OR" clause
- by JoséMi
I'm executing the following query
SELECT COUNT(*)
FROM table
WHERE field1='value' AND (field2 = 1000 OR field3 = 2000)
There is one index over field1 and another composited over field2&field3.
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