In most cases, MySQL’s optimizer chooses the fastest index option for queries automatically, but now and then it may hit a snag that slows your database queries to a crawl. You can use one of the three index hints — USE INDEX, IGNORE INDEX, or FORCE INDEX — to specify which indexes the optimizer uses or doesn’t use. However, there are many limitations to using the hints, and most query-processing problems can be resolved by making things simpler rather than by making them more complicated.
The right index makes all the difference in the performance of a database server. Indexes let your queries focus on the rows that matter, and they allow you to set your preferred search order. Covering indexes (also called index-only queries) speed things up by responding to database queries without having to access data in the tables themselves.
Unfortunately, MySQL’s optimizer doesn’t always choose the most-efficient query plan. As the MySQL Manual explains, you view the optimizer’s statement execution plan by preceding the SELECT statement with the keyword EXPLAIN. When this occurs, you can use index hints to specify which index to use for the query.
The three syntax options for hints are USE INDEX, IGNORE INDEX, and FORCE INDEX: The first instructs MySQL to use only the index listed; the second prevents MySQL from using the indexes listed; and the third has the same effect as the first option, but with the added limitation that table scans occur only when none of the given indexes can be used.
MySQL’s index_hint syntax lets you specify the index to be used to process a particular query. Source: MySQL Reference Manual
Why use FORCE INDEX at all? There may be times when you want to keep table scans to an absolute minimum. Any database is likely to field some queries that can’t be satisfied without having to access some data residing only in the table, and outside any index.
To modify the index hint, apply a FOR clause: FOR JOIN, FOR ORDER BY, or FOR GROUP BY. The first applies hints only when MySQL is choosing how to find table rows or process joins; while the second and third apply the hints only when sorting or grouping rows, respectively. Note that whenever a covering index is used to access the table, the optimizer ignores attempts by the ORDER BY and GROUP BY modifiers to have it ignore the covering index.
Are you sure you need that index hint?
Once you get the hang of using index hints to improve query performance, you may be tempted to overuse the technique. In a Stack Overflow post from July 2013, a developer wasn’t able to get MySQL to list his preferred index when he ran EXPLAIN. He was looking for a way to force MySQL to use that specific index for performance tests.
In this case, it was posited that no index hint was needed. Instead, he could just change the order of the specified indexes so that the left-most column in the index is used for row restriction. (While this approach is preferred in most situations, the particulars of the database in question made this solution impractical.)
SQL performance guru Markus Winand classifies optimizer hints as either restricting or supporting. Restricting hints Winand uses only reluctantly because they create potential points of failure in the future: a new index could be added that the optimizer can’t access, or an object name used as a parameter could be changed at some point.
Supporting hints add some useful information that make the optimizer run better, but Winand claims such hints are rare. For example, the query may be asking for only a specified number of rows in a result, so the optimizer can choose an index that would be impractical to run on all rows.
Troubleshooting the performance of your databases doesn’t get simpler than using the point-and-click interface of the Morpheus database-as-a-service (DBaaS). You can provision, deploy, and host heterogeneous MySQL, MongoDB, Redis, and ElasticSearch databases via Morpheus’s single dashboard. Each database instance includes a free full replica set and daily backups.
Morpheus is the first and only DBaaS to support SQL, NoSQL, and in-memory databases. The service lets you use a range of tools for monitoring and optimizing your databases. Visit the Morpheus site to create a free account.
