Expand description
§Index selection cost matrix
column_idx | 0 | 1 | 2 | 3 | 4 | remark |
|---|---|---|---|---|---|---|
| Equal | 1 | 1 | 1 | 1 | 1 | |
| In | 10 | 8 | 5 | 5 | 5 | take the minimum value with actual in number |
| Range(Two) | 600 | 50 | 20 | 10 | 10 | RangeTwoSideBound like a between 1 and 2 |
| Range(One) | 1400 | 70 | 25 | 15 | 10 | RangeOneSideBound like a > 1, a >= 1, a < 1 |
| All | 4000 | 100 | 30 | 20 | 10 |
index cost = cost(match type of 0 idx)
* cost(match type of 1 idx)
* ... cost(match type of the last idx)§Example
Given index order key (a, b, c)
- For
a = 1 and b = 1 and c = 1, its cost is 1 = Equal0 * Equal1 * Equal2 = 1 - For
a in (xxx) and b = 1 and c = 1, its cost is In0 * Equal1 * Equal2 = 10 - For
a = 1 and b in (xxx), its cost is Equal0 * In1 * All2 = 1 * 8 * 50 = 400 - For
a between xxx and yyy, its cost is Range(Two)0 = 600 - For
a = 1 and b between xxx and yyy, its cost is Equal0 * Range(Two)1 = 50 - For
a = 1 and b > 1, its cost is Equal0 * Range(One)1 = 70 - For
a = 1, its cost is 100 = Equal0 * All1 = 100 - For no condition, its cost is All0 = 4000
With the assumption that the most effective part of a index is its prefix,
cost decreases as column_idx increasing.
For index order key length > 5, we just ignore the rest.