Help Solve Slow Reading Issue

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

Original topic: 帮忙解决一下读慢

| username: tidb狂热爱好者

mysql> show create table t_asset \G
*************************** 1. row ***************************
Table: t_asset
Create Table: CREATE TABLE t_asset (
id bigint(20) NOT NULL,
ast_type int(11) NOT NULL COMMENT ‘id’,
act_id bigint(20) NOT NULL COMMENT ‘id’,
ccy_id int(11) NOT NULL COMMENT ‘id’,
uid bigint(20) NOT NULL COMMENT ‘user id’,
avail decimal(36,18) DEFAULT NULL COMMENT ‘available’,
trd_hold decimal(36,18) DEFAULT NULL COMMENT ‘’,
non_trd_hold decimal(36,18) DEFAULT NULL COMMENT ‘frozen’,
iso_avail decimal(36,18) DEFAULT NULL COMMENT ‘gold’,
iso_hold decimal(36,18) DEFAULT NULL COMMENT ‘frozen’,
net_debt decimal(36,18) DEFAULT NULL COMMENT ‘net debt’,
version bigint(20) NOT NULL DEFAULT ‘0’,
c_time bigint(20) DEFAULT NULL,
u_time bigint(20) DEFAULT NULL,
state tinyint(4) DEFAULT NULL,
eq decimal(36,18) DEFAULT NULL,
net_mgn decimal(36,18) DEFAULT NULL,
order_lost decimal(36,18) DEFAULT NULL,
asset_value decimal(36,18) DEFAULT NULL,
debt decimal(36,18) DEFAULT NULL,
mm decimal(36,18) DEFAULT NULL,
upl decimal(36,18) DEFAULT NULL,
PRIMARY KEY (uid,id) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY IDX_UNI_ACT_AST_CCY (act_id,ast_type,ccy_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

SELECT
id,
ast_type,
act_id,
ccy_id,
uid,
avail,
trd_hold,
non_trd_hold,
iso_avail,
iso_hold,
net_debt,
version,
c_time,
u_time,
state,
eq,
net_mgn,
order_lost,
asset_value,
debt,
mm,
upl
FROM
t_asset
WHERE
(id > 240000000014681264)
ORDER BY
id
LIMIT
1000;

| username: tidb狂热爱好者 | Original post link

Is this the modification?

SELECT
    floor((t.row_num - 1) / 1000) + 1 AS page_num,
    min(t.id) AS start_key,
    max(t.id) AS end_key,
    count(*) AS page_size
FROM (
    SELECT id, row_number() OVER (ORDER BY id) AS row_num
    FROM books
) t
GROUP BY page_num
ORDER BY page_num;
| username: Jellybean | Original post link

You can post the execution plan for us to review together, and you can also check the statement analysis results in the Dashboard.

| username: zhanggame1 | Original post link

The primary key is PRIMARY KEY (uid, id).
WHERE (id > 240000000014681264) will be slow, so add an index on id or use id, uid as the primary key.

| username: TiDBer_RjzUpGDL | Original post link

Add an index to the ID.

| username: zhaokede | Original post link

The ID is only part of the composite primary key, not an index. A full table scan definitely won’t be fast.

| username: miya | Original post link

I checked the existing indexes and they are not applicable. You can create a unique index specifically for the ID. Then, in the subquery, first retrieve the ID, and then query the specific data information. Since a non-equal query is used, it will definitely perform a range scan. We can reduce I/O, allowing it to perform a faster range scan with a smaller memory footprint. Then, use the ID to perform a point query.

| username: wangkk2024 | Original post link

Check the plan and see where it is slow.

| username: xiaoqiao | Original post link

Check the execution plan to see where it is slow.

| username: 友利奈绪 | Original post link

Check the execution plan and create a composite index accordingly.

| username: 小于同学 | Original post link

You can post the execution plan for us to review together, or you can check the statement analysis results in the Dashboard.

| username: Soysauce520 | Original post link

You can try creating an index on the id to see the effect.

| username: 这里介绍不了我 | Original post link

Try adding an index to the ID and perform a delayed join.

| username: 我是人间不清醒 | Original post link

Please provide a screenshot of the execution plan using “explain analyze”.

| username: QH琉璃 | Original post link

Take a look at the execution plan.

| username: dba远航 | Original post link

Please share the execution plan for review.