Description:
The new index hint JOIN_INDEX, does not only impact what index is used for join, but also for e.g. range access. Cosider the following query:
mysql> explain select * from country join city on country.code = city.countrycode where country.code in ('CHN', 'IND', 'JPN') and city.population > 10000000;
+----+-------------+---------+------------+--------+-----------------+---------+---------+------------------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+--------+-----------------+---------+---------+------------------------+------+----------+------------------------------------+
| 1 | SIMPLE | city | NULL | range | CountryCode,idx | idx | 4 | NULL | 1 | 5.00 | Using index condition; Using where |
| 1 | SIMPLE | country | NULL | eq_ref | PRIMARY | PRIMARY | 3 | world.city.CountryCode | 1 | 100.00 | NULL |
+----+-------------+---------+------------+--------+-----------------+---------+---------+------------------------+------+----------+------------------------------------+
If we do not want the PRIMARY index of country to be used for the join (e.g., to force hash join to be used), we can try to use the hint JOIN_INDEX to avoid that:
mysql> explain select /*+ NO_JOIN_INDEX(country PRIMARY) */ * from country join city on country.code = city.countrycode where country.code in ('CHN', 'IND', 'JPN') and ci
ty.population > 10000000;
+----+-------------+---------+------------+-------+-----------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-----------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | city | NULL | range | CountryCode,idx | idx | 4 | NULL | 1 | 5.00 | Using index condition; Using where |
| 1 | SIMPLE | country | NULL | ALL | NULL | NULL | NULL | NULL | 239 | 0.42 | Using where; Using join buffer (hash join) |
+----+-------------+---------+------------+-------+-----------------+------+---------+------+------+----------+--------------------------------------------+
Note that this also prevents PRIMARY to be used as range access when country is input to hash join.
How to repeat:
Using the world database:
explain select * from country join city on country.code = city.countrycode where country.code in ('CHN', 'IND', 'JPN') and city.population > 10000000;
explain select /*+ NO_JOIN_INDEX(country PRIMARY) */ * from country join city on country.code = city.countrycode where country.code in ('CHN', 'IND', 'JPN') and city.population > 10000000;
Suggested fix:
Let JOIN_INDEX hint only affect choice of index for NL join. Let the general INDEX hint not affect the choice of index for NL join. Alternatively, introduce a new hint, e.g., NL_JOIN_INDEX.
Description: The new index hint JOIN_INDEX, does not only impact what index is used for join, but also for e.g. range access. Cosider the following query: mysql> explain select * from country join city on country.code = city.countrycode where country.code in ('CHN', 'IND', 'JPN') and city.population > 10000000; +----+-------------+---------+------------+--------+-----------------+---------+---------+------------------------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+--------+-----------------+---------+---------+------------------------+------+----------+------------------------------------+ | 1 | SIMPLE | city | NULL | range | CountryCode,idx | idx | 4 | NULL | 1 | 5.00 | Using index condition; Using where | | 1 | SIMPLE | country | NULL | eq_ref | PRIMARY | PRIMARY | 3 | world.city.CountryCode | 1 | 100.00 | NULL | +----+-------------+---------+------------+--------+-----------------+---------+---------+------------------------+------+----------+------------------------------------+ If we do not want the PRIMARY index of country to be used for the join (e.g., to force hash join to be used), we can try to use the hint JOIN_INDEX to avoid that: mysql> explain select /*+ NO_JOIN_INDEX(country PRIMARY) */ * from country join city on country.code = city.countrycode where country.code in ('CHN', 'IND', 'JPN') and ci ty.population > 10000000; +----+-------------+---------+------------+-------+-----------------+------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+-----------------+------+---------+------+------+----------+--------------------------------------------+ | 1 | SIMPLE | city | NULL | range | CountryCode,idx | idx | 4 | NULL | 1 | 5.00 | Using index condition; Using where | | 1 | SIMPLE | country | NULL | ALL | NULL | NULL | NULL | NULL | 239 | 0.42 | Using where; Using join buffer (hash join) | +----+-------------+---------+------------+-------+-----------------+------+---------+------+------+----------+--------------------------------------------+ Note that this also prevents PRIMARY to be used as range access when country is input to hash join. How to repeat: Using the world database: explain select * from country join city on country.code = city.countrycode where country.code in ('CHN', 'IND', 'JPN') and city.population > 10000000; explain select /*+ NO_JOIN_INDEX(country PRIMARY) */ * from country join city on country.code = city.countrycode where country.code in ('CHN', 'IND', 'JPN') and city.population > 10000000; Suggested fix: Let JOIN_INDEX hint only affect choice of index for NL join. Let the general INDEX hint not affect the choice of index for NL join. Alternatively, introduce a new hint, e.g., NL_JOIN_INDEX.