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

Info Catalog

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

(mysql.info.gz) Range optimization

 
 7.2.5.2 Range Access Method for Multiple-Part Indexes
 .....................................................
 
 Range conditions on a multiple-part index are an extension of range
 conditions for a single-part index.  A range condition on a
 multiple-part index restricts index records to lie within one or
 several key tuple intervals. Key tuple intervals are defined over a set
 of key tuples, using ordering from the index.
 
 For example, consider a multiple-part index defined as `key1(KEY_PART1,
 KEY_PART2, KEY_PART3)', and the following set of key tuples listed in
 key order:
 
      KEY_PART1  KEY_PART2  KEY_PART3
        NULL       1          'abc'
        NULL       1          'xyz'
        NULL       2          'foo'
         1         1          'abc'
         1         1          'xyz'
         1         2          'abc'
         2         1          'aaa'
 
 The condition `KEY_PART1 = 1' defines this interval:
 
      (1, -inf, -inf) <= (KEY_PART1, KEY_PART2, KEY_PART3) < (1, +inf, +inf)
 
 The interval covers the 4th, 5th, and 6th tuples in the preceding data
 set and can be used by the range access method.
 
 By contrast, the condition `KEY_PART3 = 'abc'' does not define a single
 interval and cannot be used by the range access method.
 
 The following descriptions indicate how range conditions work for
 multiple-part indexes in greater detail.
 
    * For `HASH' indexes, each interval containing identical values can
      be used.  This means that the interval can be produced only for
      conditions in the following form:
 
               KEY_PART1 CMP CONST1
           AND KEY_PART2 CMP CONST2
           AND ...
           AND KEY_PARTN CMP CONSTN;
 
      Here, CONST1, CONST2, ... are constants, CMP is one of the `=',
      `<=>', or `IS NULL' comparison operators, and the conditions cover
      all index parts. (That is, there are N conditions, one for each
      part of an N-part index.)
 
      See  Range access single-part for the definition of what is
      considered to be a constant.
 
      For example, the following is a range condition for a three-part
      `HASH' index:
 
           KEY_PART1 = 1 AND KEY_PART2 IS NULL AND KEY_PART3 = 'foo'
 
    * For a `BTREE' index, an interval might be usable for conditions
      combined with `AND', where each condition compares a key part with
      a constant value using `=', `<=>', `IS NULL', `>', `<', `>=',
      `<=', `!=', `<>', `BETWEEN', or `LIKE 'PATTERN'' (where
      `'PATTERN'' doesn't start with a wildcard).  An interval can be
      used as long as it is possible to determine a single key tuple
      containing all records that match the condition (or two intervals
      if `<>' or `!=' is used).  For example, for this condition:
 
           KEY_PART1 = 'foo' AND KEY_PART2 >= 10 AND KEY_PART3 > 10
 
      The single interval will be:
 
           ('foo', 10, 10)
              < (KEY_PART1, KEY_PART2, KEY_PART3)
                 < ('foo', +inf, +inf)
 
      It is possible that the created interval will contain more records
      than the initial condition.  For example, the preceding interval
      includes the value `('foo', 11, 0)', which does not satisfy the
      original condition.
 
    * If conditions that cover sets of records contained within
      intervals are combined with `OR', they form a condition that
      covers a set of records contained within the union of their
      intervals. If the conditions are combined with `AND', they form a
      condition that covers a set of records contained within the
      intersection of their intervals.  For example, for this condition
      on a two-part index:
 
           (KEY_PART1 = 1 AND KEY_PART2 < 2)
           OR (KEY_PART1 > 5)
 
      The intervals will be:
 
           (1, -inf) < (KEY_PART1, KEY_PART2) < (1, 2)
           (5, -inf) < (KEY_PART1, KEY_PART2)
 
      In this example, the interval on the first line uses one key part
      for the left bound and two key parts for the right bound. The
      interval on the second line uses only one key part. The `key_len'
      column in the `EXPLAIN' output indicates the maximum length of the
      key prefix used.
 
      In some cases, `key_len' may indicate that a key part was used, but
      that might be not what you would expect.  Suppose that KEY_PART1
      and KEY_PART2 can be `NULL'.  Then the `key_len' column will
      display two key part lengths for the following condition:
 
           KEY_PART1 >= 1 AND KEY_PART2 < 2
 
      But in fact, the condition will be converted to this:
 
           KEY_PART1 >= 1 AND KEY_PART2 IS NOT NULL
 
 
  Range access single-part describes how optimizations are
 performed to combine or eliminate intervals for range conditions on
 single-part index.  Analogous steps are performed for range conditions
 on multiple-part keys.
 

Info Catalog

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

(mysql.info.gz) Range optimization