MySQL Forums
Forum List  »  Docs

Re: Using index for skip scan
Posted by: Apkleads apkleads
Date: October 06, 2022 03:57AM

Rather than restricting the search path using a predicate from the statement, Skip Scans are initiated by probing the index for distinct values of the prefix column. Each of these distinct values is then used as a starting point for a regular index search. The result is several separate searches of a single index that, when combined, eliminate the affect of the prefix column. Essentially, the index has been searched from the second level down.

The optimizer uses statistics to decide if a skip scan would be more efficient than a full table scan.


This approach is advantageous because:

It reduces the number of indexes needed to support a range of queries. This increases performance by reducing index maintenance and decreases wasted space associated with multiple indexes.
The prefix column should be the most discriminating and the most widely used in queries. These two conditions do not always go hand in hand which makes the decision difficult. In these situations skip scanning reduces the impact of makeing the "wrong" decision.

Options: ReplyQuote


Subject
Views
Written By
Posted
298
t k
March 03, 2022 04:32AM
197
May 15, 2022 02:50PM
173
May 15, 2022 03:25PM
Re: Using index for skip scan
53
October 06, 2022 03:57AM
19
November 02, 2022 10:12PM


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.