The same SQL query has inconsistent execution plans in the Dashboard slow query and the SQL editor, resulting in slow query performance

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

Original topic: 同一条SQL,在Dashboard慢查询中的执行计划和SQL编辑器自己执行的不一致导致查询过慢

| username: TiDBer_E3pRgGAy

[TiDB Usage Environment] Production Environment
[TiDB Version] 6.5.5
[Attachment: Screenshot/Log/Monitoring]
As shown in the figure below, the SQL execution plan seen in the slow query on the TiDB Dashboard console does not use the index, but when the SQL is copied and executed, it does use the index;


| username: h5n1 | Original post link

Manually collect table statistics, or use SPM to fix the execution plan.

| username: LuoBlack | Original post link

Check if there are any changes in the table’s health status?

| username: TiDBer_小阿飞 | Original post link

One is TableFullScan, and the other is TableRowIDScan.

| username: h5n1 | Original post link

Try connecting to the database using the MySQL command line and test it. You added a LIMIT in the tool execution.

| username: TiDBer_E3pRgGAy | Original post link

Okay, I didn’t notice it just now.

| username: TiDBer_E3pRgGAy | Original post link

Indeed, if there is no limit constraint, it will not use the index.

| username: 大飞哥online | Original post link

The tool has added a default limit, please take note of this.

| username: Fly-bird | Original post link

This question has been discussed a lot in the forum, and it’s basically like this, covering a wide range of topics.

| username: TiDBer_E3pRgGAy | Original post link

No, it indeed doesn’t use the index now.

| username: TiDBer_E3pRgGAy | Original post link

Okay, thank you.

| username: TiDBer_E3pRgGAy | Original post link

Is there any way to troubleshoot and handle this situation where it still performs a full table scan even though there is an index?

| username: h5n1 | Original post link

Test: Manually collect table statistics, then check the execution plan. Online solution: Use SPM to fix the execution plan, create global binding.

| username: TiDBer_E3pRgGAy | Original post link

After I manually ANALYZE the table, the SQL indeed uses the index.

| username: TiDBer_E3pRgGAy | Original post link

Does using SPM to fix execution plans have any impact on overall database performance?

| username: h5n1 | Original post link

No impact, just thinking that after adjustments like indexing, the binding might become invalid.

| username: TiDBer_E3pRgGAy | Original post link

Okay, re-binding should fix the issue.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.