Using LIMIT Query on a Single Table with TiFlash

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

Original topic: 使用limit查询单表走了tiflash

| username: CHENGX

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version]
[Reproduction Path] What operations were performed to encounter the issue
[Encountered Issue: Problem Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]


A limit 100 query on a large table at the AP node resulted in using TiFlash. The table has over 100GB of data, and the TiDB execution plan is not very accurate.

| username: tidb菜鸟一只 | Original post link

Have the statistics been collected? Also, this TiDB node won’t specifically go directly to TiFlash, right? Check with SHOW config WHERE NAME LIKE ‘%isolation-read.engines%’;

| username: 友利奈绪 | Original post link

This could be the reason why the index is not being hit.

| username: zhaokede | Original post link

There might be an issue with the statistics.

| username: DBAER | Original post link

Mark, it should be a statistics issue.

| username: TiDBer_QYr0vohO | Original post link

Mark

| username: CHENGX | Original post link

I reanalyzed, the node configuration is as follows:

| username: CHENGX | Original post link

Limit 100 should not use an index, right?

| username: dba远航 | Original post link

I remember there are limitations on using LIMIT.

| username: TIDB-Learner | Original post link

Check the progress of the TiFlash replica.

| username: virvle | Original post link

Check if the table has many columns and whether it is really necessary to use SELECT * to display all the columns.

| username: wangkk2024 | Original post link

How about checking the execution plan?

| username: xiaoqiao | Original post link

By default, there may be this issue.

| username: Soysauce520 | Original post link

Will it use TiFlash if you try limit 1?

| username: zhang_2023 | Original post link

Statistics are inaccurate.

| username: virvle | Original post link

Yes, inaccurate statistics can also have a significant impact.