Query performance is slow, not as fast as MySQL

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

Original topic: 查询性能慢,没有mysql快

| username: TiDBer_FMWXa7ja

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version] 6.5
[Reproduction Path]
[Encountered Issues]

  1. Compared two SQL statements with MySQL, and the query efficiency is not as good as MySQL. MySQL is deployed on a single machine, while TiDB is deployed in a cluster. Theoretically, TiDB should be faster by utilizing MPP (Massively Parallel Processing) for concurrent computation. I want to know if others have similar issues?

  2. We are using TiDB to replace MySQL partly to solve the problem of large data volumes, as TiDB can scale horizontally, and partly to address the slow query speed of MySQL when table data volumes become large. Shouldn’t TiDB’s multi-node sharding and MPP parallel computation be more efficient than MySQL’s single-node?
    [Resource Configuration] MySQL single machine deployment, TiDB with 3 PD nodes and 6 KV nodes. The server configurations are the same.
    [Attachments: Screenshots/Logs/Monitoring]

  3. Simple SQL (Note: rcc_d_houseMeterHistoricalData table has 150 million records)

  4. Complex SQL (Note: rcc_d_houseMeterHistoricalData table has 150 million records)

| username: xfworld | Original post link

According to your idea, you can compare the execution plans (based on the provided data volume, simple query [index hit without table lookup] TiDB returns in seconds…)

You surely know how much optimization space MySQL has.
As for TiDB’s optimization and usage rules, you still need to study more… :upside_down_face:

| username: hey-hoho | Original post link

Is your screenshot from MySQL or TiDB? Please provide the comparison data. There is a lot of room for optimization.

| username: TiDBer_FMWXa7ja | Original post link

I took a screenshot of TiDB, and the query speed of MySQL can be faster by about 3 to 5 seconds.

| username: 人如其名 | Original post link

Post the explain analyze information of these two SQLs for review.
explain analyze

| username: buddyyuan | Original post link

Without posting the execution plan, there’s no way to make a comparison. Having one more index or one less index, or inconsistent statistics, can all potentially affect execution speed.

| username: TiDBer_FMWXa7ja | Original post link

  1. Simple SQL execution plan

  2. Complex SQL execution plan

| username: 人如其名 | Original post link

The execution plan information is incomplete.
Total execution time of the TableReader operator: 15.2s, with a total of 345 copTasks. The average execution time for each copTask (on the TiDB side) is 167.9ms, so it is estimated that the parallelism is around 4-5.
Did you adjust the tidb_distsql_scan_concurrency parameter? The default is 15. You can check and post it here.

show variables like 'tidb_distsql_scan_concurrency';
| username: TiDBer_FMWXa7ja | Original post link

Is this time meaningful? It feels like the results vary greatly each time.

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

Could you also post the execution plan for MySQL? Is it possible that MySQL has an index while TiDB does not?

| username: TiDBer_FMWXa7ja | Original post link

There is an index, and the condition planTime is the primary key.

| username: xfworld | Original post link

What is the structure of this table? Clustered or non-clustered?

| username: TiDBer_FMWXa7ja | Original post link

Clustered Index

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

But your TiDB is not using the index, it’s doing a full table scan… Are the statistics on your TiDB up to date?

| username: BraveChen | Original post link

Let me help you with SQL optimization :melting_face:

| username: TiDBer_FMWXa7ja | Original post link

tableRangeScan is a range scan, not a full table scan, right?

| username: TiDBer_FMWXa7ja | Original post link

Thank you, boss. Let’s get started :smiley:

| username: BraveChen | Original post link

Could you please upload the SQL, the complete execution plan, and the related table structures?

| username: 考试没答案 | Original post link

The MPP framework requires TiFlash nodes, right? You haven’t deployed these types of nodes, have you?

| username: 考试没答案 | Original post link