TDB subqueries are much slower than on MySQL

translator_bot · June 23, 2024, 12:32am

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

Original topic: TDB 子查询比在MySQL 上慢很多

| username: TiDBer_Z93z8JhD

To improve efficiency, please provide the following information. A clear problem description will help resolve the issue faster:
【TiDB Usage Environment】

【Overview】 Scenario + Problem Overview
Importing MySQL table into TiDB, TiDB query results are much slower than MySQL
【Background】 Actions taken
Tried adjusting parameters like tidb_distsql_scan_concurrency, tidb_executor_concurrency, tidb_index_join_batch_size, etc., but to no avail
【Phenomenon】 Business and database phenomena

【Problem】 Current issue encountered
SQL execution time is much slower on TiDB compared to MySQL
【Business Impact】

【TiDB Version】
6.1
【Application Software and Version】

【Attachments】 Relevant logs and configuration information

SQL statement:
SELECT t.blockcode, t.blockname, t.blocktype, MIN(t.tradday) tradday, t.cnt, t.stocks
FROM (
SELECT a.* FROM block_stocks_info a WHERE 2 >= (
SELECT COUNT(*)
FROM block_stocks_info b
WHERE a.blockcode = b.blockcode AND a.tradday <= b.tradday
) ORDER BY a.blockcode, a.tradday
) t GROUP BY blockcode;

Execution time on MySQL: 14s
Execution time on TiDB: 30s

translator_bot · June 23, 2024, 12:32am

| username: h5n1 | Original post link

There is a significant difference between actrows and estrows evaluations. Manually collect the table’s statistics first and then take a look.
Upload a more comprehensive execution plan to see if there are many historical version reads related to tikv_task statistics.
This SQL looks awkward; it has an order by after count. It is recommended to rewrite it.