(mysql.info.gz) Range access single-part

Info Catalog

(mysql.info.gz) Range optimization

(mysql.info.gz) Range optimization

(mysql.info.gz) Range access multi-part

 
 7.2.5.1 Range Access Method for Single-Part Indexes
 ...................................................
 
 For a single-part index, index value intervals can be conveniently
 represented by corresponding conditions in the `WHERE' clause, so we'll
 talk about "range conditions" instead of intervals.
 
 The definition of a range condition for a single-part index is as
 follows:
 
    * For both `BTREE' and `HASH' indexes, comparison of a key part with
      a constant value is a range condition when using the `=', `<=>',
      `IN', `IS NULL', or `IS NOT NULL' operators.
 
    * For `BTREE' indexes, comparison of a key part with a constant
      value is a range condition when using the `>', `<', `>=', `<=',
      `BETWEEN', `!=', or `<>' operators, or `LIKE 'PATTERN'' (where
      `'PATTERN'' doesn't start with a wildcard).
 
    * For all types of indexes, multiple range conditions combined with
      `OR' or `AND' form a range condition.
 
 
 "Constant value" in the preceding descriptions  means one of the
 following:
 
    * A constant from the query string
 
    * A column of a `const' or `system' table from the same join
 
    * The result of an uncorrelated subquery
 
    * Any expression composed entirely from subexpressions of the
      preceding types
 
 Here are some examples of queries with range conditions in the `WHERE'
 clause:
 
      SELECT * FROM t1 WHERE KEY_COL > 1 AND KEY_COL < 10;
 
      SELECT * FROM t1 WHERE KEY_COL = 1 OR KEY_COL IN (15,18,20);
 
      SELECT * FROM t1 WHERE KEY_COL LIKE 'ab%' OR KEY_COL BETWEEN
      'bar' AND 'foo';
 
 Note that some non-constant values may be converted to constants during
 the constant propagation phase.
 
 MySQL tries to extract range conditions from the `WHERE' clause for
 each of the possible indexes. During the extraction process, conditions
 that can't be used for constructing the range condition are dropped,
 conditions that produce overlapping ranges are combined, and conditions
 that produce empty ranges are removed.
 
 For example, consider the following statement, where `key1' is an
 indexed column and `nonkey' is not indexed:
 
      SELECT * FROM t1 WHERE
         (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
         (key1 < 'bar' AND nonkey = 4) OR
         (key1 < 'uux' AND key1 > 'z');
 
 The extraction process for key `key1' is as follows:
 
   1. Start with original `WHERE' clause:
 
           (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
           (key1 < 'bar' AND nonkey = 4) OR
           (key1 < 'uux' AND key1 > 'z')
 
   2. Remove `nonkey = 4' and `key1 LIKE '%b'' because they cannot be
      used for a range scan. The right way to remove them is to replace
      them with `TRUE', so that we don't miss any matching records when
      doing the range scan. Having replaced them with `TRUE', we get:
 
           (key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
           (key1 < 'bar' AND TRUE) OR
           (key1 < 'uux' AND key1 > 'z')
 
   3. Collapse conditions that are always true or false:
 
         * `(key1 LIKE 'abcde%' OR TRUE)'  is always true
 
         * `(key1 < 'uux' AND key1 > 'z')' is always false
 
      Replacing these conditions with constants, we get:
 
           (key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
 
      Removing unnecessary `TRUE' and `FALSE' constants, we obtain
 
           (key1 < 'abc') OR (key1 < 'bar')
 
   4. Combining overlapping intervals into one yields the final
      condition to be used for the range scan:
 
           (key1 < 'bar')
 
 
 In general (and as demonstrated in the example), the condition used for
 a range scan is less restrictive than the `WHERE' clause. MySQL will
 perform an additional check to filter out rows that satisfy the range
 condition but not the full `WHERE' clause.
 
 The range condition extraction algorithm can handle nested `AND'/`OR'
 constructs of arbitrary depth, and its output doesn't depend on the
 order in which conditions appear in `WHERE' clause.
 

Info Catalog

(mysql.info.gz) Range optimization

(mysql.info.gz) Range optimization

(mysql.info.gz) Range access multi-part