Multiple-Column Indexes
693
MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries
that test just the first column, the first two columns, the first three columns, and so on. If you specify the
columns in the right order in the index definition, a single composite index can speed up several kinds
of queries on the same table.
A multiple-column index can be considered a sorted array, the rows of which contain values that are
created by concatenating the values of the indexed columns.
Note
As an alternative to a composite index, you can introduce a column that is
“hashed” based on information from other columns. If this column is short,
reasonably unique, and indexed, it might be faster than a “wide” index on many
columns. In MySQL, it is very easy to use this extra column:
SELECT * FROM
tbl_name
WHERE
hash_col
=MD5(CONCAT(
val1
,
val2
))
AND
col1
=
val1
AND
col2
=
val2
;
Suppose that a table has the following specification:
CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name)
);
The
name
index is an index over the
last_name
and
first_name
columns. The index can be used
for lookups in queries that specify values in a known range for combinations of
last_name
and
first_name
values. It can also be used for queries that specify just a
last_name
value because that
column is a leftmost prefix of the index (see
Section 8.5.2, “Multiple-Column Indexes”
). Therefore, the
name
index is used for lookups in the following queries:
SELECT * FROM test WHERE last_name='Widenius';
SELECT * FROM test
WHERE last_name='Widenius' AND first_name='Michael';
SELECT * FROM test
WHERE last_name='Widenius'
AND (first_name='Michael' OR first_name='Monty');
SELECT * FROM test
WHERE last_name='Widenius'
AND first_name >='M' AND first_name < 'N';
However, the
name
index is not used for lookups in the following queries:
SELECT * FROM test WHERE first_name='Michael';
SELECT * FROM test
WHERE last_name='Widenius' OR first_name='Michael';
Suppose that you issue the following
SELECT
statement:
mysql>
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
If a multiple-column index exists on
col1
and
col2
, the appropriate rows can be fetched directly.
If separate single-column indexes exist on
col1
and
col2
, the optimizer attempts to use the Index
Merge optimization (see
Section 8.3.1.4, “Index Merge Optimization”
), or attempts to find the most
restrictive index by deciding which index excludes more rows and using that index to fetch the rows.
If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer
to find rows. For example, if you have a three-column index on
(col1, col2, col3)
, you have
indexed search capabilities on
(col1)
,
(col1, col2)
, and
(col1, col2, col3)
.
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...