How MySQL Query Hints Impact Performance

MySQL has a small number of query hints that can affect performance. There are hints that affect the total query and those that affect how individual table indexes are used.

Total Query Hints

All of the total query hints occur directly after the SELECT keyword. These options include SQL_CACHE, SQL_NO_CACHE, SQL_SMALL_RESULT, SQL_BIG_RESULT, SQL_BUFFER_RESULT, SQL_CALC_FOUND_ROWS, and HIGH_PRIORITY. None of these hints affect the use of any specific table index. At this time we will not be discussing any of these in more detail.

Only the STRAIGHT_JOIN query hint has an effect on how indexes are used for query execution. This hint is used to inform the optimizer to execute a query execution plan in the order the tables are specified in the query. Here is an example:

mysql> EXPLAIN SELECT album.name, artist.name, album.first_released
    -> FROM artist INNER JOIN album USING (artist_id)
    -> WHERE album.name = 'Greatest Hits'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: album
         type: ref
possible_keys: artist_id,name,name_release
          key: name
      key_len: 257
          ref: const
         rows: 904
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: artist
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
...
mysql> EXPLAIN SELECT STRAIGHT_JOIN album.name,artist.name,album.first_released
    -> FROM artist INNER JOIN album USING (artist_id)
    -> WHERE album.name = 'Greatest Hits'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: artist
         type: index
possible_keys: PRIMARY
         key: name
      key_len: 257
          ref: NULL
         rows: 586756
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: album
         type: ref
possible_keys: artist_id,name,name_release
          key: artist_id
...

You can see in the first query the optimizer chose to join on the album table first. In the second query with the STRAIGHT_JOIN the optimizer was forced to join the names in the order the tables were specified. While the query uses an index for both tables, the second query has to process a much larger set of rows and is less efficient in this example.

Index Hints

With the exception of the STRAIGHT_JOIN query hint, all index hints are applied for each table in a join statement. You can elect to define a USE, IGNORE, or FORCE list of indexes per table. You can also elect to restrict the use of the index to the JOIN, the ORDER BY, or the GROUP BY portion of a query. After each table in your query you can specify the following syntax:

   USE {INDEX|KEY}
       [{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
   | IGNORE {INDEX|KEY}
       [{FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
   | FORCE {INDEX|KEY}
       [{FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

mysql> EXPLAIN SELECT artist_id, name, country_id
    -> FROM artist WHERE founded = 1980 AND type='Band'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: artist
         type: ref
possible_keys: founded,founded_2,type,type_2
          key: founded
      key_len: 2
          ref: const
         rows: 1216
        Extra: Using where
1 row in set (0.01 sec)

In this query, the optimizer had a choice of several indexes but chose the founded index.

In the next example, we instruct the optimizer to use a specific index:

mysql> EXPLAIN SELECT artist_id, name, country_id
    -> FROM artist USE INDEX (type)
    -> WHERE founded = 1980 AND type='Band'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: artist
         type: ref
possible_keys: type
          key: type
      key_len: 1
          ref: const
         rows: 186720
        Extra: Using where

In this query we see the index specified was used.

We can also ask the optimizer to ignore an index:

mysql> EXPLAIN SELECT artist_id, name, country_id
    -> FROM artist IGNORE INDEX (founded)
    -> WHERE founded = 1980 AND type='Band'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: artist
         type: ref
possible_keys: founded_2,type,type_2
          key: founded_2
      key_len: 2
          ref: const
         rows: 1216
        Extra: Using where

You can provide multiple index names, and multiple index hints:

mysql> EXPLAIN SELECT artist_id, name, country_id
    -> FROM artist IGNORE INDEX (founded,founded_2)
    ->             USE INDEX (type_2)
    -> WHERE founded = 1980 AND type='Band'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: artist
         type: ref
possible_keys: type_2
          key: type_2
      key_len: 1
          ref: const
         rows: 177016
        Extra: Using where

For more information see http://dev.mysql.com/doc/refman/5.5/en/index-hints.html.

The use of MySQL hints does not have an effect on changing the entire execution path, causing you then to specify multiple hints. The USE INDEX hint forces MySQL to choose from one of the specified indexes. The FORCE INDEX has the effect of influencing the cost based optimizer to prefer an index scan over a full table scan.

CAUTION

Adding hints to SQL queries comes at a great risk. While this might help a query, over time the volume of data, for example, can change the query effectiveness. Changes in adding or revising indexes on tables will not affect a hard coded SQL statement that has specified a specific index. You should use hints only as a last resort.