Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 执行计划不正常
[Overview] SQL execution plan is abnormal, causing the statement to execute too slowly
[Background] The health score of other worksheet tables is 99
[TiDB Version] V5.4.1
Generally, it should first filter according to create_time, resulting in a result set of 500 rows. Then sort, but this execution plan sorts the entire set first and then filters, causing the SQL to execute too slowly.
Related indexes:
count(1) is for testing purposes, not for actual business queries, right? What is the first column? Try removing the order by clause.
Since it’s already counted, why do we still need to add order by?
This execution plan first performs a full table scan, then filters based on the filter conditions, and finally sorts the results. This is the expected order. However, this execution plan does not use an index, and considering you mentioned the result set is 500, the expected number of result set entries seen from the execution plan is 3.94, which is quite a discrepancy. This could be due to inaccurate statistics.
Your SQL is mainly slow due to a full table scan. A full table scan is similar to Oracle predicate crossing. You can try collecting statistics again and test it. What are you mainly testing? If the type is already equal, why do you still need to order by? 
Original sentence:
select * from worksheet where type = 1 and create_time >= ‘2022-10-13 11:52:25’ order by create_time desc, type limit 10
Analyze and recollect statistics, the issue is resolved, but the table’s health score is 99.
That’s right, but the health score is 99, which is hard to prevent.
This is similar to Oracle’s predicate pushdown, where queries for the latest time may result in an inaccurate execution plan.
In the case of an inaccurate execution plan, you can consider binding the execution plan. Refer to this: SQL Plan Management (SPM) | PingCAP Docs
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: Hacker_5KEgzcj2
|
username: 
|
username: 
|
username: 
|
username: 
|
username: