Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 通过id排序查询和非id字段查询时间有很大差异,测试tidb中表都有这种情况,一直基于MySQL方式创建表,是创建表SQL问题还是其他问题?(id和非id字段都有索引)(走行存和列存都有这种情况)
[TiDB Usage Environment] Production Environment
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: There is a significant difference in query time between sorting by id and querying by non-id fields. This situation is observed in all tables tested in TiDB. Tables were always created based on MySQL methods. Is it an issue with the table creation SQL or something else? (Both id and non-id fields have indexes)
The query SQL and table creation statement are as follows:
EXPLAIN ANALYZE SELECT
id,
bid_id,
UNIX_TIMESTAMP(publish_time) AS publish_stamp,
title,
views,
bid_summary
FROM
Call_Accounting.news
WHERE
STATUS = 1
AND 1 = 1
AND city = 500200
AND s_subscopeclass = 0701
ORDER BY
publish_time DESC
LIMIT
0, 4;
Table creation statement:
CREATE TABLE news
(
id
bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘Identifier’,
title
text DEFAULT NULL COMMENT ‘News Title’,
detail
text DEFAULT NULL COMMENT ‘News Details’,
href
varchar(200) DEFAULT NULL COMMENT ‘Source URL’,
bid_id
varchar(50) DEFAULT NULL COMMENT ‘Bid Identifier ID’,
publish_time
datetime DEFAULT NULL COMMENT ‘Publish Time’,
toptype
varchar(50) DEFAULT NULL COMMENT ‘Primary Information Type’,
subtype
varchar(50) DEFAULT NULL COMMENT ‘Secondary Information Type’,
s_topscopeclass
varchar(50) DEFAULT NULL COMMENT ‘Primary Industry Classification’,
s_subscopeclass
varchar(50) DEFAULT NULL COMMENT ‘Secondary Industry Classification’,
budget
double DEFAULT NULL COMMENT ‘Budget Amount’,
bidamount
double DEFAULT NULL COMMENT ‘Bid Amount’,
area
varchar(30) DEFAULT NULL COMMENT ‘Province’,
city
varchar(50) DEFAULT NULL COMMENT ‘City’,
views
int(11) DEFAULT NULL COMMENT ‘Views’,
page_title
varchar(300) DEFAULT NULL COMMENT ‘Page Title’,
bid_keywords
varchar(100) DEFAULT NULL COMMENT ‘Bid Keywords’,
bid_summary
text DEFAULT NULL COMMENT ‘Bid Summary’,
status
tinyint(4) DEFAULT ‘0’ COMMENT ‘0 Not Usable\n1 Usable\n2 Deleted Information’,
district
varchar(50) DEFAULT NULL COMMENT ‘District’,
copeclass
varchar(20) DEFAULT NULL COMMENT ‘Goods, Services, Engineering’,
buyer
varchar(100) DEFAULT NULL COMMENT ‘Purchasing Unit’,
comeintime
datetime DEFAULT NULL COMMENT ‘Storage Time’,
winner
text DEFAULT NULL COMMENT ‘Winning Unit’,
PRIMARY KEY (id
) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=4530001;
]
[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]