Databases Reference
In-Depth Information
DELAYED
This hint is for use with
INSERT
and
REPLACE
. It lets the statement to which it is
applied return immediately and places the inserted rows into a buffer, which will
be inserted in bulk when the table is free. This is most useful for logging and similar
applications where you want to insert a lot of rows without making the client wait,
and without causing I/O for each statement. There are many limitations; for
example, delayed inserts are not implemented in all storage engines, and
LAST
_INSERT_ID()
doesn't work with them.
STRAIGHT_JOIN
This hint can appear either just after the
SELECT
keyword in a
SELECT
statement, or
in any statement between two joined tables. The first usage forces all tables in the
query to be joined in the order in which they're listed in the statement. The second
usage forces a join order on the two tables between which the hint appears.
The
STRAIGHT_JOIN
hint is useful when MySQL doesn't choose a good join order,
or when the optimizer takes a long time to decide on a join order. In the latter case,
the thread will spend a lot of time in the “statistics” state, and adding this hint will
reduce the search space for the optimizer.
You can use
EXPLAIN
to see what order the optimizer would choose, then rewrite
the query in that order and add
STRAIGHT_JOIN
. This is a good idea as long as you
don't think the fixed order will result in bad performance for some
WHERE
clauses.
You should be careful to revisit such queries after upgrading MySQL, however,
because new optimizations might appear that will be defeated by
STRAIGHT_JOIN
.
SQL_SMALL_RESULT
and
SQL_BIG_RESULT
These hints are for
SELECT
statements. They tell the optimizer how and when to
use temporary tables and sort in
GROUP BY
or
DISTINCT
queries.
SQL_SMALL_RESULT
tells the optimizer that the result set will be small and can be put into indexed
temporary tables to avoid sorting for the grouping, whereas
SQL_BIG_RESULT
indi-
cates that the result will be large and that it will be better to use temporary tables
on disk with sorting.
SQL_BUFFER_RESULT
This hint tells the optimizer to put the results into a temporary table and release
table locks as soon as possible. This is different from the client-side buffering we
described previously. Server-side buffering can be useful when you don't use
buffering on the client, because it lets you avoid consuming a lot of memory on the
client and still release locks quickly. The trade-off is that the server's memory is
used instead of the client's.
SQL_CACHE
and
SQL_NO_CACHE
These hints instruct the server that the query either is or is not a candidate for
caching in the query cache. See the next chapter for details on how to use them.
SQL_CALC_FOUND_ROWS
This hint isn't strictly an optimizer hint. It doesn't tell MySQL to plan the query
differently. Instead, it provides extra functionality by changing what the query
