Ignore index optimizer hint doesn't work with sub queries

Application environment:

There is none. Accessing TiDB directly via mysql-client.

TiDB version:

select @@version;
+--------------------+
| @@version          |
+--------------------+
| 5.7.25-TiDB-v6.5.0 |
+--------------------+

Problem:

ignore index used in queries with sub queries does not work as expected.

When using ignore index with sub-query it is ignored/does not nothing.

EXPLAIN SELECT COUNT(DISTINCT u0_.device_id) AS sclr_0 FROM   daily_traffic_stats d1_  ignore index(ping_at_idx) LEFT JOIN user_vid_devices u0_ ON d1_.vid = u0_.vid WHERE   d1_.vid IN (     SELECT       u2_.vid   FROM       user_vid_devices u2_ force index(user_id_vid_idx)     WHERE       u2_.user_id = '0ec7d9f4-0c07-4c8b-a5d5-f8ad5d84cb51'   )   AND d1_.ping_at > '2023-01-27 08:11:54' LIMIT   1;
+---------------------------------------------+----------+-----------+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                          | estRows  | task      | access object                             | operator info                                                                                                                                                                                                               |
+---------------------------------------------+----------+-----------+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Limit_22                                    | 1.00     | root      |                                           | offset:0, count:1                                                                                                                                                                                                           |
| └─StreamAgg_24                              | 1.00     | root      |                                           | funcs:count(distinct hourly_stats.user_vid_devices.device_id)->Column#33                                                                                                                                                    |
|   └─IndexJoin_120                           | 48.96    | root      |                                           | left outer join, inner:IndexLookUp_119, outer key:hourly_stats.daily_traffic_stats.vid, inner key:hourly_stats.user_vid_devices.vid, equal cond:eq(hourly_stats.daily_traffic_stats.vid, hourly_stats.user_vid_devices.vid) |
|     ├─HashJoin_100(Build)                   | 48.65    | root      |                                           | inner join, equal:[eq(hourly_stats.user_vid_devices.vid, hourly_stats.daily_traffic_stats.vid)]                                                                                                                             |
|     │ ├─IndexLookUp_106(Build)              | 1.93     | root      |                                           |                                                                                                                                                                                                                             |
|     │ │ ├─IndexRangeScan_104(Build)         | 1.93     | cop[tikv] | table:u2_, index:user_id_vid_idx(user_id) | range:["0ec7d9f4-0c07-4c8b-a5d5-f8ad5d84cb51","0ec7d9f4-0c07-4c8b-a5d5-f8ad5d84cb51"], keep order:false                                                                                                                     |
|     │ │ └─TableRowIDScan_105(Probe)         | 1.93     | cop[tikv] | table:u2_                                 | keep order:false                                                                                                                                                                                                            |
|     │ └─IndexLookUp_112(Probe)              | 43154.06 | root      |                                           |                                                                                                                                                                                                                             |
|     │   ├─IndexRangeScan_110(Build)         | 43154.06 | cop[tikv] | table:d1_, index:ping_at_idx(ping_at)     | range:(2023-01-27 08:11:54,+inf], keep order:false                                                                                                                                                                          |
|     │   └─TableRowIDScan_111(Probe)         | 43154.06 | cop[tikv] | table:d1_                                 | keep order:false                                                                                                                                                                                                            |
|     └─IndexLookUp_119(Probe)                | 48.65    | root      |                                           |                                                                                                                                                                                                                             |
|       ├─IndexRangeScan_117(Build)           | 48.65    | cop[tikv] | table:u0_, index:PRIMARY(vid)             | range: decided by [eq(hourly_stats.user_vid_devices.vid, hourly_stats.daily_traffic_stats.vid)], keep order:false                                                                                                           |
|       └─TableRowIDScan_118(Probe)           | 48.65    | cop[tikv] | table:u0_                                 | keep order:false                                                                                                                                                                                                            |
+---------------------------------------------+----------+-----------+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set (0.001 sec)

When sub query is removed ignore index is applied.

 EXPLAIN SELECT  COUNT(DISTINCT vid) AS sclr_0 FROM   daily_traffic_stats d1_  ignore index(ping_at_idx) WHERE d1_.ping_at > '2023-01-27 08:11:54' LIMIT   1;
+------------------------------+---------------+-----------+---------------+--------------------------------------------------------------------------+
| id                           | estRows       | task      | access object | operator info                                                            |
+------------------------------+---------------+-----------+---------------+--------------------------------------------------------------------------+
| Limit_11                     | 1.00          | root      |               | offset:0, count:1                                                        |
| └─StreamAgg_13               | 1.00          | root      |               | funcs:count(distinct hourly_stats.daily_traffic_stats.vid)->Column#21    |
|   └─TableReader_19           | 43872.08      | root      |               | data:Selection_18                                                        |
|     └─Selection_18           | 43872.08      | cop[tikv] |               | gt(hourly_stats.daily_traffic_stats.ping_at, 2023-01-27 08:11:54.000000) |
|       └─TableFullScan_17     | 1117734277.00 | cop[tikv] | table:d1_     | keep order:false                                                         |
+------------------------------+---------------+-----------+---------------+--------------------------------------------------------------------------+
5 rows in set (0.001 sec)

The same goes for other optimizer hints such as force index, use index

Could you try

EXPLAIN SELECT /*+ IGNORE_INDEX(d1_, ping_at_idx) */ COUNT(DISTINCT u0_.device_id) AS sclr_0 FROM   daily_traffic_stats d1_  LEFT JOIN user_vid_devices u0_ ON d1_.vid = u0_.vid WHERE   d1_.vid IN (     SELECT       u2_.vid   FROM       user_vid_devices u2_ force index(user_id_vid_idx)     WHERE       u2_.user_id = '0ec7d9f4-0c07-4c8b-a5d5-f8ad5d84cb51'   )   AND d1_.ping_at > '2023-01-27 08:11:54' LIMIT   1;

Thanks for fast reply.

EXPLAIN SELECT /*+ IGNORE_INDEX(d1_, ping_at_idx) */ COUNT(DISTINCT u0_.device_id) AS sclr_0 FROM   daily_traffic_stats d1_  LEFT JOIN user_vid_devices u0_ ON d1_.vid = u0_.vid WHERE   d1_.vid IN (     SELECT       u2_.vid   FROM       user_vid_devices u2_ force index(user_id_vid_idx)     WHERE       u2_.user_id = '0ec7d9f4-0c07-4c8b-a5d5-f8ad5d84cb51'   )   AND d1_.ping_at > '2023-01-27 08:11:54' LIMIT   1;
+---------------------------------------------+----------+-----------+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                          | estRows  | task      | access object                             | operator info                                                                                                                                                                                                               |
+---------------------------------------------+----------+-----------+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Limit_22                                    | 1.00     | root      |                                           | offset:0, count:1                                                                                                                                                                                                           |
| └─StreamAgg_24                              | 1.00     | root      |                                           | funcs:count(distinct hourly_stats.user_vid_devices.device_id)->Column#33                                                                                                                                                    |
|   └─IndexJoin_120                           | 48.96    | root      |                                           | left outer join, inner:IndexLookUp_119, outer key:hourly_stats.daily_traffic_stats.vid, inner key:hourly_stats.user_vid_devices.vid, equal cond:eq(hourly_stats.daily_traffic_stats.vid, hourly_stats.user_vid_devices.vid) |
|     ├─HashJoin_100(Build)                   | 48.65    | root      |                                           | inner join, equal:[eq(hourly_stats.user_vid_devices.vid, hourly_stats.daily_traffic_stats.vid)]                                                                                                                             |
|     │ ├─IndexLookUp_106(Build)              | 1.93     | root      |                                           |                                                                                                                                                                                                                             |
|     │ │ ├─IndexRangeScan_104(Build)         | 1.93     | cop[tikv] | table:u2_, index:user_id_vid_idx(user_id) | range:["0ec7d9f4-0c07-4c8b-a5d5-f8ad5d84cb51","0ec7d9f4-0c07-4c8b-a5d5-f8ad5d84cb51"], keep order:false                                                                                                                     |
|     │ │ └─TableRowIDScan_105(Probe)         | 1.93     | cop[tikv] | table:u2_                                 | keep order:false                                                                                                                                                                                                            |
|     │ └─IndexLookUp_112(Probe)              | 53666.27 | root      |                                           |                                                                                                                                                                                                                             |
|     │   ├─IndexRangeScan_110(Build)         | 53666.27 | cop[tikv] | table:d1_, index:ping_at_idx(ping_at)     | range:(2023-01-27 08:11:54,+inf], keep order:false                                                                                                                                                                          |
|     │   └─TableRowIDScan_111(Probe)         | 53666.27 | cop[tikv] | table:d1_                                 | keep order:false                                                                                                                                                                                                            |
|     └─IndexLookUp_119(Probe)                | 48.65    | root      |                                           |                                                                                                                                                                                                                             |
|       ├─IndexRangeScan_117(Build)           | 48.65    | cop[tikv] | table:u0_, index:PRIMARY(vid)             | range: decided by [eq(hourly_stats.user_vid_devices.vid, hourly_stats.daily_traffic_stats.vid)], keep order:false                                                                                                           |
|       └─TableRowIDScan_118(Probe)           | 48.65    | cop[tikv] | table:u0_                                 | keep order:false                                                                                                                                                                                                            |
+---------------------------------------------+----------+-----------+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set (0.001 sec)

Thanks for your feedback, we’ll take a closer look, and get back to you when we figure it out.

The SQL bindings also affect the optimizer hints, you can run show global bindings and show session bindings to check if there are bindings on this SQL.


I’ve created a simple case, and it shows the hint works.
Would you please give us the result of Use PLAN REPLAYER to Save and Restore the On-Site Information of a Cluster | PingCAP Docs to help us to find the reason?
You can send the dump result via privately way.

Hello,

Thanks @timeandfate, it seems that we’ve left a global binding that interfered with optimizer hints. Removed global binding and now use index/ignore index works as expected.

Thank you @Misaka @timeandfate

1 Like

If you encounter the similar situation in the future, you can use

explain format='verbose' select xxxx

to find out if there are any binding with this SQL statement, it will show in the warning section.

1 Like