Network Programming in .NET

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.