MySQL 8.0.20: Index-Level Optimizer Hints

MySQL introduced optimizer hints in version 5.7 and greatly extended the feature in MySQL 8. One thing that has been missing though is the ability to specify index hints using the syntax of optimizer hints. This has been improved of in MySQL 8.0.20 with the introduction of index-level optimizer hints for the FORCE and IGNORE versions of the index hints. This blog will look at the new index hint syntax.

The Short Story

To make a long story short, consider this query in 8.0.19 and earlier:

SELECT ci.CountryCode, co.Name AS Country, ci.Name AS City, ci.District
  FROM world.country co IGNORE INDEX (Primary)
       INNER JOIN world.city ci FORCE INDEX FOR ORDER BY (CountryCode)
                  ON ci.CountryCode = co.Code
 WHERE co.Continent = 'Asia'
 ORDER BY ci.CountryCode, ci.ID;

This query has two index hints, IGNORE INDEX in the second line and USE INDEX FOR ORDER BY in the third line.

In MySQL 8.0.20, you can write the query as:

SELECT /*+ NO_INDEX(co PRIMARY) ORDER_INDEX(ci CountryCode) */
       ci.CountryCode, co.Name AS Country, ci.Name AS City, ci.District
  FROM world.country co
       INNER JOIN world.city ci
                  ON ci.CountryCode = co.Code
 WHERE co.Continent = 'Asia'
 ORDER BY ci.CountryCode, ci.ID;

Let's take a look which index-level optimizer hints that have been added and how they map to the old index hints.

The Details

There are four pairs of new index hints which all maps back to the old style hints as in the below table.

The new hints support all of the usual features of optimizer hints such as specifying the query block for a hint, adding them inline in subqueries, etc. For the full details, see the manual and the release notes (which has an extensive description of the new hints).

If you need to specify multiple index hints, there are two ways to accomplish it depending on whether the indexes are on the same table or not. Consider a query on the world.city table where you will not allow neither the primary key nor the CountryCode index to be used. You can accomplish that as in this example:

SELECT /*+ NO_INDEX(ci PRIMARY, CountryCode) */
       ID, CountryCode, Name, District, Population
  FROM world.city ci
 WHERE Population > 1000000;

On the other hand, if the indexes are on different tables, then you will have to specify multiple hints. Let's say you want to force the optimizer to choose the hash join algorithm when joining the country and city tables in the world database by ignoring the primary key on the country table and the CountryCode index on the city table (effectively forcing the join not to use an index irrespective of the join order). In this case, you can use the NO_INDEX() hint twice, once on each table:

SELECT /*+ NO_INDEX(co PRIMARY) NO_INDEX(ci CountryCode) */
       ci.CountryCode, co.Name AS Country, ci.Name AS City, ci.District
  FROM world.country co
       INNER JOIN world.city ci ON ci.CountryCode = co.Code
 WHERE co.Continent = 'Asia';

How About USE INDEX?

The old index hint syntax also includes the USE INDEX variant, but none of the new index-level optimizer hints corresponds to it. So, what do you do if you want to specify the softer USE INDEX rather than FORCE INDEX? First of all, in that case there is a good chance, you do not need the index hint at all, so try to remove it and verify whether the optimizer uses the same query plan. If so, it is better to remove it, so the optimizer can use the optimal join strategy as new optimizer features become available or the data changes.

If you really need the USE INDEX hint, the simplest is to continue to use the old syntax. However, be aware that you cannot mix the old and new syntax hints (in that case, the old hints are ignored).

Alternative, you can simulate USE INDEX by specifying all other applicable indexes in a NO_INDEX() optimizer hint. For example, consider the world.countrylanguage table:

mysql> SHOW CREATE TABLE countrylanguage\G
*************************** 1. row ***************************
       Table: countrylanguage
Create Table: CREATE TABLE `countrylanguage` (
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `Language` char(30) NOT NULL DEFAULT '',
  `IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
  `Percentage` float(4,1) NOT NULL DEFAULT '0.0',
  PRIMARY KEY (`CountryCode`,`Language`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.0008 sec)

If you want to find all languages spoken in Australia and for some reason want to either use the CountryCode index or no index at all, then you can tell the optimizer to ignore the primary key:

SELECT /*+ NO_INDEX(cl PRIMARY) */
       CountryCode, Language, IsOfficial, Percentage
  FROM world.countrylanguage cl
 WHERE CountryCOde = 'AUS';

That is equivalent of the old syntax hints with USE INDEX (CountryCode):

SELECT CountryCode, Language, IsOfficial, Percentage
  FROM world.countrylanguage cl USE INDEX (CountryCode)
 WHERE CountryCOde = 'AUS';

While this works the same and is simple enough with just one alternative index, it is not so easy in the general case. To be sure your NO_INDEX() optimizer hint is the same as the USE INDEX index hint, you will need to fetch the list of indexes and add them to the NO_INDEX() hint each time you execute the query. That is the reason for the suggestion to keep using the old style index hint if you rely on USE INDEX.