SELECT
Syntax
1096
By specifying
USE INDEX (index_list)
, you can tell MySQL to use only one of the named indexes
to find rows in the table. The alternative syntax
IGNORE INDEX (index_list)
can be used to tell
MySQL to not use some particular index or indexes. These hints are useful if
EXPLAIN
shows that
MySQL is using the wrong index from the list of possible indexes.
You can also use
FORCE INDEX
, which acts like
USE INDEX (index_list)
but with the addition
that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is
no way to use one of the given indexes to find rows in the table.
Each hint requires the names of indexes, not the names of columns. The name of a
PRIMARY KEY
is
PRIMARY
. To see the index names for a table, use
SHOW INDEX
.
An
index_name
value need not be a full index name. It can be an unambiguous prefix of an index
name. If a prefix is ambiguous, an error occurs.
USE INDEX
,
IGNORE INDEX
, and
FORCE INDEX
affect only which indexes are used when MySQL
decides how to find rows in the table and how to do the join. They do not affect whether an index is
used when resolving an
ORDER BY
or
GROUP BY
clause. As of MySQL 5.0.40, the optional
FOR JOIN
clause can be added to make this explicit.
Examples:
SELECT * FROM table1 USE INDEX (col1_index,col2_index)
WHERE col1=1 AND col2=2 AND col3=3;
SELECT * FROM table1 IGNORE INDEX (col3_index)
WHERE col1=1 AND col2=2 AND col3=3;
For
FULLTEXT
searches, index hints do not work before MySQL 5.0.74. As of 5.0.74, index hints work
as follows:
• For natural language mode searches, index hints are silently ignored. For example,
IGNORE
INDEX(i)
is ignored with no warning and the index is still used.
For boolean mode searches, index hints are honored.
13.2.8.4.
UNION
Syntax
SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]
UNION
is used to combine the result from multiple
SELECT
statements into a single result set.
The column names from the first
SELECT
statement are used as the column names for the results
returned. Selected columns listed in corresponding positions of each
SELECT
statement should have
the same data type. (For example, the first column selected by the first statement should have the
same type as the first column selected by the other statements.)
If the data types of corresponding
SELECT
columns do not match, the types and lengths of the columns
in the
UNION
result take into account the values retrieved by all of the
SELECT
statements. For
example, consider the following:
mysql>
SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a |
| bbbbbbbbbb |
+---------------+
(In some earlier versions of MySQL, only the type and length from the first
SELECT
would have been
used and the second row would have been truncated to a length of 1.)
Содержание 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 ...