Skip to content
  • Jorgen Loland's avatar
    7a9d8311
    WL#5957: Range optimizer: use statistics instead of index · 7a9d8311
    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.
    7a9d8311
    WL#5957: Range optimizer: use statistics instead of index
    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.
Loading