CODEWITHSUNDEEP
mysqlindexingrdbms
Sahil
Sahil

Reputation: 9496

How does Index Merge intersection provide optimization in mysql/rdbms?

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?

Upvotes: 0

Views: 1404

Answers (2)

Rick James
Rick James

Reputation: 142528

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.

Upvotes: 0

ysth
ysth

Reputation: 98433

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.

Upvotes: 0

Related Questions