| Bug #99381 | ORDER_BY Index-level optimizer hint implies NO_JOIN_INDEX for second table | ||
|---|---|---|---|
| Submitted: | 28 Apr 2020 7:26 | Modified: | 29 Apr 2020 7:05 |
| Reporter: | Jesper Wisborg Krogh | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0.20 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[28 Apr 2020 7:26]
Jesper Wisborg Krogh
Workaround, to get the expected query plan back, you need to add a JOIN_INDEX() hint as well:
EXPLAIN
SELECT /*+ ORDER_INDEX(ci CountryCode) JOIN_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\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: co
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 239
filtered: 14.285715103149414
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: ci
partitions: NULL
type: ref
possible_keys: CountryCode
key: CountryCode
key_len: 3
ref: world.co.Code
rows: 18
filtered: 100
Extra: NULL
2 rows in set, 1 warning (0.0008 sec)
[29 Apr 2020 7:05]
MySQL Verification Team
Dear Jesper, Thank you for the report and feedback. regards, Umesh
Description: If you try to use the new index-level optimizer hints, then in some cases the optimizer hint is not used correctly by the optimizer. For example with a NO_INDEX() on the world.country table for the primary key and a ORDER_INDEX() on the world.city table for the CountryCode index, then the join is performed as a hash join. As comparison, the corresponding query using the old style index hints uses the CountryCode index for the join as well as the sorting. The issue seems only to happen for joins. For single table queries, the ORDER_BY() index hint behaves as expected. How to repeat: Old style index hints: EXPLAIN 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\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: co partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 239 filtered: 14.285715103149414 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: ci partitions: NULL type: ref possible_keys: CountryCode key: CountryCode key_len: 3 ref: world.co.Code rows: 18 filtered: 100 Extra: NULL 2 rows in set, 1 warning (0.0008 sec) Note that as per the JSON formatted output, the query cost of this plan is 857.45 with my index statistics. New index-level optimizer hint: EXPLAIN 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\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: co partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 239 filtered: 14.285715103149414 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: ci partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4188 filtered: 0.43103447556495667 Extra: Using where; Using join buffer (hash join) 2 rows in set, 1 warning (0.0007 sec) This query has a cost of 14947.06.