Unable to retrieve results using min and limit 1

Translated
1

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

Original topic: min 和limit 1无法查询出结果。

| username: sladenew

[TiDB Usage Environment] Production Environment
[TiDB Version] 5.7.25-TiDB-v5.1.1 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
[Encountered Problem]
[Reproduction Path] What operations were performed to encounter the problem
[Problem Phenomenon and Impact]

Table Structure:

CREATE TABLE snapshot (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘Primary Key’,
tag_id bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘tagid’,
tag_version bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘Data Version’,
PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */
)

Problem:

  1. select min(id) from snapshot and SELECT * FROM snapshot limit 1 cannot produce results.
  2. However, select max(id) from snapshot produces results in about 50 milliseconds.
  3. When running select min(id) from snapshot, the tikv-details-rocksdb-kv seek operations shows an increase in next and next_found.
  4. The entire table has approximately 1.2 billion rows.
  5. Suspected to be related to GC? But no data deletion operations have been performed in the last two days. GC configuration:
    ±-----------------------±-------+
    | Variable_name | Value |
    ±-----------------------±-------+
    | tidb_gc_concurrency | -1 |
    | tidb_gc_enable | ON |
    | tidb_gc_life_time | 10m0s |
    | tidb_gc_run_interval | 10m0s |
    | tidb_gc_scan_lock_mode | LEGACY |
    ±-----------------------±-------+

[Attachment]

2
| username: h5n1 | Original post link

Take a look at the explain plans for these two SQL queries.

3
| username: sladenew | Original post link

select min(id) explain

image
image1197×144 5.91 KB

SELECT * FROM snapshot limit 1 explain

image
image826×157 5.6 KB

4
| username: h5n1 | Original post link

The min(id) should normally convert to a primary key index to fetch the top 1, so you need to check the execution plan. A full table scan with limit 1 not returning results might be due to GC issues causing the historical versions of the table not being cleaned up. You can use explain analyze and wait for the execution to complete to see the difference between total_keys and processed_keys.

5
| username: sladenew | Original post link
  1. The current issue is that even during off-peak hours, it takes more than 10 minutes to execute without yielding any results, so the difference between total_keys and processed_keys cannot be obtained.
  2. I do suspect a GC issue, but there has been no data deletion in the past two days, and the GC time is set to 10 minutes.
6
| username: h5n1 | Original post link

Try forcing that SQL to use the primary key and check the execution plan. It’s not about deleting in the past two days; the bug has caused old data to not be cleaned up for a long time. An upgrade is needed.

7
| username: sladenew | Original post link
  1. I have tested FORCE_INDEX, but it still doesn’t work.
  2. I will upgrade the version first and then test it again.
8
| username: system | Original post link

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