0

I am trying to understand indexing in RDBMS, and I am having hard time understanding Index Merge Intersection optimization while executing SQL query. Let's take this query as example

SELECT * FROM innodb_table
  WHERE primary_key < 10 AND key_col1 = 20;

Suppose we have two indices, one for each key column. How does using index merge benefit us here?

For e.g. we can use index of primary_key column to do range scan, and then do a linear scan of intermediate results to get the expected output.

How can Index Merge give us better performance?

Max
  • 9,100
  • 25
  • 72
  • 109
  • with one of the two keys being a primary key, you shouldn't be seeing an index merge, at least on any recent version – ysth Aug 07 '20 at 16:46

2 Answers2

0

What makes you think it gives better performance? Sometimes it may; it would depend a lot on the cardinality of the indexes and particular values/ranges being searched.

In practice, it often means you should pick which index will perform better and add an index hint.

ysth
  • 96,171
  • 6
  • 121
  • 214
  • I can't think of a scenario where index merge will be preferable over linear scanning? Can you give me an example? – Max Aug 07 '20 at 14:32
  • several low cardinality keys being index merged where a very low number of rows will be selected and each table row is quite large? – ysth Aug 07 '20 at 16:44
0

For WHERE primary_key < 10 AND key_col1 = 20, provide

INDEX(key_col1, primary_key)

in that order. Discussion:

  • Put = columns first in the index; one 'range' last.
  • Index merge might be usable without the above 'composite' index, but it will not be as efficient.
  • It would be specifically "index merge intersect" (as opposed to "... union").
  • I have yet to find a case where index merge intersect is faster than a suitable composite index.

How would merge work?

  1. Gather a list of PKs satisfying primary_key < 10
  2. Gather a list of PKs satisfying key_col1 = 20
  3. "merge" those two lists ("AND").
  4. Use the PKs to look up the row(s) (SELECT *).

How would the composite key work?

  1. Using the index's BTree, locate the first 'row' in the index with key_col1 = 20; it will have the smallest primary_key.
  2. Reach into the table to get SELECT * using the PK.
  3. Move on to the next row in the index.
  4. Repeat steps 2, 3 until hitting 10.

Without the composite index, probably this is what the optimizer will do:

  1. Start at the beginning of the table (no index being used)
  2. Ignore row if key_col1 = 20 is false; else deliver row
  3. repeat until primary_key < 10

EXPLAIN SELECT ... will tell you which method it chose.

As for index merge union...

  • It is only(?) used with OR.
  • It is rarely used.
  • Sometimes reformulating the query to use UNION instead of OR is a better option.
Rick James
  • 135,179
  • 13
  • 127
  • 222