5.4 Influencing MySQL with Hints
Many relational database servers
implement some notion of hints—a simple syntax for providing
additional information to the underlying SQL engine and query
optimizer. Sometimes you may need to do this to work around a bug or
improve performance. Let's have a quick look at the
various hints that can influence MySQL's query
processing. As you saw with the query cache, hints in MySQL often
appear right after the SELECT keyword:
SELECT SQL_CACHE * FROM mytable ...
But as you'll see, that's not
always the case.
If you're worried about code portability because
your SQL may need to run on a database server other than MySQL, you
can often enclose hints within comments so that
they'll be ignored by other servers—or older
versions of MySQL itself:
SELECT /*! SQL_CACHE */ * FROM mytable ...
5.4.1 Join Order
MySQL normally doesn't
care about the order in which you list tables in your queries. It
examines the possibilities and decides which table to read first,
second, and so on. Once in a while, you might find that MySQL
isn't handling a multitable join very well. After
looking at the EXPLAIN output for the query, you
realize that it's accessing the tables in a less
than optimal order.
If you think you can do a better job of optimizing the join order
than MySQL has done, you can use the STRAIGHT_JOIN
hint in place of a comma or JOIN keyword in your
query:
SELECT * FROM table1 STRAIGHT_JOIN table2 WHERE ...
Doing so forces MySQL to join the tables in the order they appear in
your query, regardless of the order it would otherwise decide to use.
5.4.2 Index Usage
MySQL
provides several index-related hints to cover cases when
you'd like more control over the indexes it
considers.
To provide a list of indexes you'd like MySQL to
consider, ignoring all others, add USE INDEX after
the table name in the query:
SELECT * FROM mytable USE INDEX (mod_time, name) ...
If you simply want MySQL to ignore one or more indexes, use
IGNORE INDEX instead:
SELECT * FROM mytale IGNORE INDEX (priority) ...
To force MySQL to use a particular index, use FORCE
INDEX in the query:
SELECT * FROM mytable FORCE INDEX (mod_time) ...
In doing so, you're telling MySQL to ignore any
decisions it might otherwise have made about the best way to find the
data you've asked for. It will disobey that request
only if the index you specify can't possibly be used
to resolve the query.
5.4.3 Result Sizes
A
set of hints also exists to tell MySQL that you'd
like the resulting rows to be handled in a particular way. Like most
hints, you really shouldn't be using them unless you
know they help. Overusing them will likely cause performance problems
sooner or later.
When dealing with a large number of rows that may take a bit of time
for the client to consume, consider using
SQL_BUFFER_RESULT. Doing so tells MySQL to store the result
in a temporary table, thus freeing up any locks much sooner.
The
SQL_BIG_RESULT hint tells MySQL that there will be a
large number of rows coming back. When MySQL sees this hint, it can
make more aggressive decisions about using disk-based temporary
tables. It will also be less likely to build an index on the
temporary table for the purpose of sorting the results.
5.4.4 Query Cache
As
noted at the beginning of this chapter, the query cache stores the
results of frequently executed SELECT queries in
memory for fast retrieval. MySQL provides opt-in and opt-out hints
that can be used to control whether or not a query's
results are cached.
By using SQL_CACHE, you ask MySQL to cache the results of
this query. If the query_cache_type is set to 1,
this hint has no affect because all SELECT queries
are cached by default. If query_cache_type is set
to 2, however, the cache is enabled, but queries are cached only on
request. Using SQL_CACHE covers this case.
On the flip side, SQL_NO_CACHE asks MySQL not to
cache the results of a query. Because this is an opt-out request, it
works for query_cache_type 1 or 2.
|