MySQL Forums
Forum List  »  General

Index Merge Optimization Question
Posted by: WeiQuan Wu
Date: July 18, 2023 11:31PM

Index Merge Intersection Access Algorithm
This access algorithm is applicable when a WHERE clause is converted to several range conditions on different keys combined with AND, and each condition is one of the following:

An N-part expression of 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 the primary key of an InnoDB table.

May I ask why there is the first limitation? Some people say that only in this case, the result set queried based on the secondary index is sorted by the primary key value, and the efficiency of finding the intersection after sorting is high. But even if the result set is not sorted according to the primary key value, the time required for sorting is much smaller than that for directly returning to the table without merging, because returning to the table is a disk IO operation.

Options: ReplyQuote

Written By
Index Merge Optimization Question
July 18, 2023 11:31PM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.