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