-
Jorgen Loland authored
dives if there are many ranges For queries of the type SELECT * FROM t1 WHERE a IN(<list of values>) the range optimizer will estimate the cost of reading rows that qualify. There are two possibilities: 1) If there is a unique index on 'a', the row estimate is 1 2) Otherwise, two dives into the index, one at range start and one at range end, are done and the number of rows in the range is used as estimate. However, when there are a great number of values in IN(), the time used to estimate the number of rows may easily exceed the time it takes to execute the query. This WL is for reducing query optimization time by replacing one index inspection per range with already calculated index statistics for equality ranges. Since the index statistics are faster in use but less accurate than inspecting the index, different users may have different criteria for when to use which. A SESSION variable (eq-range-index-dive-limit) is therefore added as well: If the number of equality ranges is equal to or higher than this value, index statistics is used for the equality ranges. The default is that index statistics is used if there are 10 or more equality ranges.
Jorgen Loland authoreddives if there are many ranges For queries of the type SELECT * FROM t1 WHERE a IN(<list of values>) the range optimizer will estimate the cost of reading rows that qualify. There are two possibilities: 1) If there is a unique index on 'a', the row estimate is 1 2) Otherwise, two dives into the index, one at range start and one at range end, are done and the number of rows in the range is used as estimate. However, when there are a great number of values in IN(), the time used to estimate the number of rows may easily exceed the time it takes to execute the query. This WL is for reducing query optimization time by replacing one index inspection per range with already calculated index statistics for equality ranges. Since the index statistics are faster in use but less accurate than inspecting the index, different users may have different criteria for when to use which. A SESSION variable (eq-range-index-dive-limit) is therefore added as well: If the number of equality ranges is equal to or higher than this value, index statistics is used for the equality ranges. The default is that index statistics is used if there are 10 or more equality ranges.
Loading