Edit Question - KnowledgeHub

Title Body

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 (EDIT AFTER TRYING SOLUTION PROPOSED) 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; is a lot slower than execute: SELECT COUNT(*) FROM table WHERE (columnA = value1 AND columnB = value2) OR (columnA = value1 AND columnC = value3) Having two composited index: index1 (columnA,columnB) index2 (columnA,columnC) Interesting enough is that asking Mysql to "explain" the query it's taking always index1 on both cases and index2 is not used. If I change the indexes to: index1 (columnB,columnA) index2 (columnC,columnA) And the query to: SELECT COUNT(*) FROM table WHERE (columnB = value2 AND columnA = value1) OR (columnC = value3 AND columnA = value1) Then it's the fastest way I've found Mysql works.

Tags (comma-separated) Cancel