Skip navigation links
7.2.6 Index Merge Optimization »
Section Navigation      [Toggle]

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 Sección 7.2.5.1, “Método de acceso a rango para índices simples” 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' does not 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 is: 

    ('foo', 10, 10)
   < (key_part1, key_part2, key_part3)
      < ('foo', +inf, +inf)

    It is possible that the created interval contains 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 is: 

    (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 displays two key part lengths for the following condition: 

    key_part1 >= 1 AND key_part2 < 2

    But in fact, the condition is converted to this: 

    key_part1 >= 1 AND key_part2 IS NOT NULL

Sección 7.2.5.1, “Método de acceso a rango para índices simples” 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.

Ésta es una traducción del manual de referencia de MySQL, que puede encontrarse en dev.mysql.com. El manual de referencia original de MySQL está escrito en inglés, y esta traducción no necesariamente está tan actualizada como la versión original. Para cualquier sugerencia sobre la traducción y para señalar errores de cualquier tipo, no dude en dirigirse a mysql-es@vespito.com.