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.