The same SQL query, in different sessions, one uses an index and takes less than 1 second, while the other does a full table scan and takes more than 50 seconds

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 同一个sql，在不用的session里面，一个走索引花了不到1秒的时间，一个不走索引全表扫描花了50多秒

| username: tidb_bruce

To improve efficiency, please provide the following information. Clear problem descriptions can be resolved faster:
[TiDB Usage Environment]
Production
[Overview] Scenario + Problem Overview
The same SQL query, in different sessions, one uses an index and takes less than 1 second, while the other does a full table scan and takes more than 50 seconds.

[Background] Operations performed

[Phenomenon] Business and database phenomena

[Problem] Current issue encountered

[Business Impact]

[TiDB Version]

[Application Software and Version]

[Attachments] Relevant logs and configuration information

  • TiUP Cluster Display information
  • TiUP Cluster Edit config information

Monitoring (https://metricstool.pingcap.com/)

  • TiDB-Overview Grafana monitoring
  • TiDB Grafana monitoring
  • TiKV Grafana monitoring
  • PD Grafana monitoring
  • Corresponding module logs (including logs 1 hour before and after the issue)

If the question is related to performance optimization or troubleshooting, please download the script and run it. Please select all and copy-paste the terminal output results for upload.

2
| username: ddhe9527 | Original post link

This is caused by an unstable execution plan. You can try manually collecting the table’s statistics to see if it improves. If it remains unstable, you need to consider binding the SQL execution plan.

3
| username: tidb_bruce | Original post link

Okay, let me first take a look at the content you provided.

4
| username: tidb_bruce | Original post link

After manually collecting statistics for the table and achieving a table health of 99%, the issue is resolved.

5
| username: system | Original post link

This topic was automatically closed 1 minute after the last reply. No new replies are allowed.