TiDB is an open-source, cloud-native, distributed, MySQL-Compatible database for elastic scale and real-time analytics. Try AI-powered Chat2Query free at : https://www.pingcap.com/tidb-serverless/
create table t(a int, b int, c int, d int,e int, index ibc(b,c), primary key(a,d));
explain select * from t use index (ibc) where (a,b) in ((1,1),(2,2),(3,3)) and c >= 10 and c <= 50 order by c desc limit 10;
Current result:
> explain select * from t use index (ibc) where (a,b) in ((1,1),(2,2),(3,3)) and c >= 10 and c <= 50 order by c desc limit 10;
+----------------------------------+---------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+---------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TopN_9 | 0.00 | root | | test.t.c:desc, offset:0, count:10 |
| └─IndexLookUp_17 | 0.00 | root | | |
| ├─TopN_16(Build) | 0.00 | cop[tikv] | | test.t.c:desc, offset:0, count:10 |
| │ └─Selection_15 | 0.00 | cop[tikv] | | ge(test.t.c, 10), le(test.t.c, 50), or(and(eq(test.t.a, 1), eq(test.t.b, 1)), or(and(eq(test.t.a, 2), eq(test.t.b, 2)), and(eq(test.t.a, 3), eq(test.t.b, 3)))) |
| │ └─IndexRangeScan_13 | 30.00 | cop[tikv] | table:t, index:ibc(b, c) | range:[1,1], [2,2], [3,3], keep order:false, stats:pseudo |
| └─TableRowIDScan_14(Probe) | 0.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+----------------------------------+---------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
The optimal range should be [1 10,1 50], [2 10,2 50], [3 10,3 50]
Enhancement
Current result:
The optimal range should be
[1 10,1 50], [2 10,2 50], [3 10,3 50]