Optimizing index_merge in #MySQL for performance
If you have a query in MySQL that uses an index merge, i.e. that is you are querying on multiple unconnected indexes on the same table, here is a performance tweak, that in my case changed a query from 40 seconds to 0.254 seconds, by reorganising the query with a subquery.
So, My original query was like this:
SELECT *
FROM data
WHERE ( l1 = 'no-match' )
OR ( l2 = 'X'
AND ( f1 = 'Y'
OR p = 'Z' ) ) Where “No-Match” is value that didn’t match anything in the table, and X,Y,Z were values that did match something. In the Execution plan, this was being executed as an index_merge, since all the columns had indexes on them, but not connected, but it had a very high cost;
Type Name Cost Rows
table data (index_merge) 92689.48 84079
However, by re-writing the query as a sub-query as follows;
select * from (
select * from data where
(
L1 = 'no-match' OR L2 = 'X'
)
) data2
where
f1 = 'Y' OR p= 'Z'
The index_merge was drastically reduced;
Type Name Cost Rows
table data (index_merge) 1189.36 918
And most importantly, the time was reduced to a fraction of the overall cost. I’d also argue that the SQL was a bit easier to read also.
