Open wwar opened 4 years ago
I do not have a specific use-case in mind yet, which may have alternative fixes to loose-index-scan.
For similar queries mentioned in the description, TiDB can use the hint use index(b)
to let it read data from index b
. If this table has many columns, we may slightly benefit from this hint.
I think it would help to document particular cases where TiDB may be much slower, this being one of them.
Thanks for your advice here, we will consider documenting some particular cases.
From the MySQL document about Loose Index Scan, there are some queries that can benefit from the Loose Index Scan. The document also lists the requirements and examples about queries that can utilize Loose Index Scan.
To support the Loose Index Scan on TiDB, we may need to modify:
IMO, it's not hard to implement.
But considered that we already have TiFlash to speed up OLAP queries. Maybe the use case for this optimization is a little narrow.
Feature Request
Is your feature request related to a problem? Please describe:
I have been looking into edge cases where TiDB could perform significantly worse than MySQL, which causes regressions for applications migrating. Consider the following test case:
Describe the feature you'd like:
In MySQL the select distinct is considerably faster. Using MySQL 8.0.18 with EXPLAIN ANALYZE:
The optimization loose index scan is applying here. Because the key
b
is low cardinality, it scans it in a way that repositions the pointer after each row is read to find rows > that value. Thus, only 8 rows needed to be read from the table.Executing the two queries in TiDB shows that there is very little difference between them:
Describe alternatives you've considered:
There was one previous mention of loose index scan in https://github.com/pingcap/tidb/issues/5896
I do not have a specific use-case in mind yet, which may have alternative fixes to loose-index-scan. This was discovered in generic research into potential regressions.
Teachability, Documentation, Adoption, Migration Strategy:
I think it would help to document particular cases where TiDB may be much slower, this being one of them. With work being done on index merge.. there should not be very many of them.