Oracle 5.0, Справочное руководство

Страница: 714 / 3232

How MySQL Uses Indexes
694
MySQL cannot use the index to perform lookups if the columns do not form a leftmost prefix of the
index. Suppose that you have the
SELECT
statements shown here:
SELECT * FROM tbl_name WHERE col1= val1 ;
SELECT * FROM tbl_name WHERE col1= val1 AND col2= val2 ;
SELECT * FROM tbl_name WHERE col2= val2 ;
SELECT * FROM tbl_name WHERE col2= val2 AND col3= val3 ;
If an index exists on
(col1, col2, col3)
, only the first two queries use the index. The third and
fourth queries do involve indexed columns, but
(col2)
and
(col2, col3)
are not leftmost prefixes
of
(col1, col2, col3)
.
8.5.3. How MySQL Uses Indexes
Indexes are used to find rows with specific column values quickly. Without an index, MySQL must
begin with the first row and then read through the entire table to find the relevant rows. The larger the
table, the more this costs. If the table has an index for the columns in question, MySQL can quickly
determine the position to seek to in the middle of the data file without having to look at all the data. If a
table has 1,000 rows, this is at least 100 times faster than reading sequentially. If you need to access
most of the rows, it is faster to read sequentially, because this minimizes disk seeks.
Most MySQL indexes (
PRIMARY KEY
,
UNIQUE
,
INDEX
, and
FULLTEXT
) are stored in B-trees.
Exceptions are that indexes on spatial data types use R-trees, and that
MEMORY
tables also support
hash indexes.
Strings are automatically prefix- and end-space compressed. See Section 13.1.8, “
CREATE INDEX
Syntax” .
In general, indexes are used as described in the following discussion. Characteristics specific to hash
indexes (as used in
MEMORY
tables) are described at the end of this section.
MySQL uses indexes for these operations:
• To find the rows matching a
WHERE
clause quickly.
• To eliminate rows from consideration. If there is a choice between multiple indexes, MySQL normally
uses the index that finds the smallest number of rows.
• To retrieve rows from other tables when performing joins. MySQL can use indexes on columns
more efficiently if they are declared as the same type and size. In this context,
VARCHAR
and
CHAR
are considered the same if they are declared as the same size. For example,
VARCHAR(10)
and
CHAR(10)
are the same size, but
VARCHAR(10)
and
CHAR(15)
are not.
Comparison of dissimilar columns may prevent use of indexes if values cannot be compared directly
without conversion. Suppose that a numeric column is compared to a string column. For a given
value such as
1
in the numeric column, it might compare equal to any number of values in the string
column such as
'1'
,
' 1'
,
'00001'
, or
'01.e1'
. This rules out use of any indexes for the string
column.
• To find the
MIN()
[971] or
MAX()
[971] value for a specific indexed column
key_col
. This is
optimized by a preprocessor that checks whether you are using
WHERE key_part_N = constant
on all key parts that occur before
key_col
in the index. In this case, MySQL does a single key
lookup for each
MIN()
[971] or
MAX()
[971] expression and replaces it with a constant. If all
expressions are replaced with constants, the query returns at once. For example:
SELECT MIN( key_part2 ),MAX( key_part2 )
FROM tbl_name WHERE key_part1 =10;
• To sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key (for
example,
ORDER BY key_part1, key_part2
). If all key parts are followed by
DESC
, the key is
read in reverse order. See Section 8.3.1.11, “
ORDER BY
Optimization” , and Section 8.3.1.12, “
GROUP
BY
Optimization” .