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
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.
There are four pairs of new index hints which all maps back to the old style hints as in the below table.
|New Hint||Old Hint|
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
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
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