Skip navigation links
7.2.6.3 The Index Merge Sort-Union Access Algorithm »
Section Navigation      [Toggle]

7.2.6.2. The Index Merge Union Access Algorithm

The applicability criteria for this algorithm are similar to those for the Index Merge method intersection algorithm. The algorithm can be employed when the table's WHERE clause was converted to several range conditions on different keys combined with OR, and each condition is one of the following:

  • In this form, where the index has exactly N parts (that is, all index parts are covered): 

    key_part1=const1 AND key_part2=const2 ... AND key_partN=constN

  • Any range condition over a primary key of an InnoDB table.

  • A condition for which the Index Merge method intersection algorithm is applicable.

Examples: 

SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3;

SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR
  (key3='foo' AND key4='bar') AND key5=5;

User Comments

Posted by Nathan B on September 4 2007 10:21pm[Delete] [Edit]

The example provides the following query:

SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3;

At first, I thought this was a fantastic way to get away from having to use UNION's to get the same result. However, my own tests have shown me that this kind of query will not always use the indexes the same way a true UNION query will.

In my situation, the table I tested with resulted in the index-merge-union version not using any indexes in certain situations, resulting in a full table scan, vs. the regular UNION that _did_ use the indexes.

So when thinking of executing a query like this:

SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3;

, ask yourself if you should really just stick to the old-school version:

(SELECT * FROM t1 WHERE key1=1)
UNION
(SELECT * FROM t1 WHERE key2=2)
UNION
(SELECT * FROM t1 WHERE key3=3)

Though perhaps the full table scan the first query does is a "good thing"? Maybe MySQL figures a single full-table scan will be faster than union-ing 3 different lookups? Hrm...

Add your own comment.