Open mzhang77 opened 1 week ago
mysql> show stats_meta;
+-------------------+--------------------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time | Modify_count | Row_count |
+-------------------+--------------------------+----------------+---------------------+--------------+-----------+
| test | i | | 2024-09-11 00:17:55 | 6378996 | 36 |
| test | d | | 2024-09-11 00:17:55 | 280170547 | 36 |
+-------------------+--------------------------+----------------+---------------------+--------------+-----------+
table d
only has 36 rows, so full table scan table d
is still very fast. However per test, using hint inl_join(d)
is still more than 10 times faster than the default plan optimizer chooses. Also the defaut plan chosen by optimizer is risky since statistics can be inaccurate. The execution plan using hint inl_join(d)
is safer when data grows and statistics becomes out of date.
replayer_OUvHRwpy4C5HFJ3gMfpyUg==_1726071458482602000.zip uploading a plan replayer dump for reproducing the issue
Bug Report
1. Minimal reproduce step (Required)
2. What did you expect to see? (Required)
Execution plan should use index nested loop join on table
d
because there is a where predicate on an indexed column on tablei
.3. What did you see instead (Required)
4. What is your TiDB version? (Required)
mysql> select @@version; +--------------------+ | @@version | +--------------------+ | 8.0.11-TiDB-v7.5.1 | +--------------------+ 1 row in set (0.00 sec)