Home > Uncategorized > Optimizing index_merge in #MySQL for performance

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.

Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: