In the same environment, MySQL execution takes 0.8 seconds, while TiDB takes nearly 10 seconds

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

Original topic: 在相同环境下MYSQL执行需要0.8,tidb需要接近10秒

| username: TiDBer_0mjTpEPL

[Test Environment for TiDB]
[TiDB Version]
[Reproduction Path] With 700,000 records, there is a significant performance difference between MySQL and TiDB during SQL queries. The test environment has no load.
[Encountered Issue:]
[Resource Configuration] 3 machines with 8 cores and 16GB RAM each
SQL Statement:

SELECT
	tsd.* 
FROM
	t_system_diagnosis tsd
LEFT JOIN t_combined_diagnosis_rel tcdr ON tsd.union_id = tcdr.ref_diagnosis_id 
WHERE
	tcdr.ref_conclusion_id = '934749606573506560'

Table Data Volume,


Table Structure


Table Indexes


MySQL Execution Plan

TiDB Execution Plan

MySQL Execution Result and Time

TiDB Execution Result and Time

| username: zhanggame1 | Original post link

How about trying to analyze the table?

| username: TiDBer_0mjTpEPL | Original post link

Already done.

| username: Kongdom | Original post link

Try changing the left join to an inner join.

| username: 像风一样的男子 | Original post link

TiDB did not use the index and performed a full table scan. This is not normal.

| username: TiDBer_0mjTpEPL | Original post link

The result is the same.

| username: 像风一样的男子 | Original post link

Are you sure tidb ref_conclusion_id has an index?

| username: TiDBer_0mjTpEPL | Original post link

All possible indexes have been created, and the index fields all have values, but they are just not being used. I don’t know why.

| username: TiDBer_0mjTpEPL | Original post link

It was already established before, but it didn’t work.

| username: 小龙虾爱大龙虾 | Original post link

Please send the SQL text and the explain analyze text. The screenshot is incomplete and missing a lot of information at the end.

| username: TiDBer_0mjTpEPL | Original post link

/*
 Navicat Premium Data Transfer

 Source Server         : 192.168.9.80Tidb
 Source Server Type    : MySQL
 Source Server Version : 50725
 Source Host           : 192.168.9.80:4000
 Source Schema         : temos_v2_5_2

 Target Server Type    : MySQL
 Target Server Version : 50725
 File Encoding         : 65001

 Date: 30/11/2023 10:13:49
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_combined_diagnosis_rel
-- ----------------------------
DROP TABLE IF EXISTS `t_combined_diagnosis_rel`;
CREATE TABLE `t_combined_diagnosis_rel`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `ref_diagnosis_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Intelligent diagnosis uid',
  `ref_conclusion_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Combined diagnosis conclusion uid',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `index_unine_conclusion`(`ref_diagnosis_id`, `ref_conclusion_id`) USING BTREE COMMENT 'Unique index',
  INDEX `conclusion_index_rel`(`ref_conclusion_id`) USING BTREE,
  INDEX `index_diagnosis_id`(`ref_diagnosis_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Combined intelligent diagnosis relationship table' ROW_FORMAT = Compact;

-- ----------------------------
-- Table structure for t_system_diagnosis
-- ----------------------------
DROP TABLE IF EXISTS `t_system_diagnosis`;
CREATE TABLE `t_system_diagnosis`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `ref_device_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Device ID',
  `ref_point_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Measurement point ID',
  `feature_code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Feature value code',
  `ref_diagnosis_task_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Diagnosis task ID',
  `content` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Diagnosis conclusion',
  `fault_category` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Fault category',
  `fault_type` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Fault type',
  `level` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Fault level',
  `create_time` bigint(20) NULL DEFAULT NULL COMMENT 'Creation time',
  `union_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Business primary key',
  `status` int(11) NULL DEFAULT NULL COMMENT 'Fault diagnosis status',
  `status_description` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Fault diagnosis status description',
  `ref_tenant_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Tenant Uid',
  `mp_loc` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Measurement point location',
  `component` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Component',
  `decision_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Decision table Uid',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `union_id_index`(`union_id`) USING BTREE,
  UNIQUE INDEX `union_id_index_system`(`union_id`) USING BTREE,
  INDEX `index_point_uid`(`ref_point_id`) USING BTREE,
  INDEX `index_system_diagnosis_ref_device_id`(`ref_device_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6180007 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'System diagnosis conclusion' ROW_FORMAT = Compact;

SET FOREIGN_KEY_CHECKS = 1;
| username: 小龙虾爱大龙虾 | Original post link

Is there no index on the unionid of TSD?

| username: TiDBer_0mjTpEPL | Original post link

There is an index.

| username: TiDBer_0mjTpEPL | Original post link

I have encountered the same problem. I found that the issue was due to the incorrect configuration of the max_allowed_packet parameter in the MySQL client. After adjusting this parameter, the problem was resolved. You can try setting max_allowed_packet to a larger value, such as 64M, in the MySQL client configuration file.

| username: 小龙虾爱大龙虾 | Original post link

The character set and collation are different.

| username: TiDBer_0mjTpEPL | Original post link

The problem is that there is a huge performance gap, and currently, I don’t know how to optimize it. It shouldn’t be this slow theoretically.

| username: 小龙虾爱大龙虾 | Original post link

±-----------------------------------±--------±----------±----------------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
±-----------------------------------±--------±----------±----------------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| IndexJoin_20 | 12.50 | root | | inner join, inner:IndexLookUp_19, outer key:test.t_combined_diagnosis_rel.ref_diagnosis_id, inner key:test.t_system_diagnosis.union_id, equal cond:eq(test.t_combined_diagnosis_rel.ref_diagnosis_id, test.t_system_diagnosis.union_id) |
| ├─IndexLookUp_38(Build) | 10.00 | root | | |
| │ ├─IndexRangeScan_36(Build) | 10.00 | cop[tikv] | table:tcdr, index:conclusion_index_rel(ref_conclusion_id) | range:[“934749606573506560”,“934749606573506560”], keep order:false, stats:pseudo |
| │ └─TableRowIDScan_37(Probe) | 10.00 | cop[tikv] | table:tcdr | keep order:false, stats:pseudo |
| └─IndexLookUp_19(Probe) | 10.00 | root | | |
| ├─Selection_18(Build) | 10.00 | cop[tikv] | | not(isnull(test.t_system_diagnosis.union_id)) |
| │ └─IndexRangeScan_16 | 10.00 | cop[tikv] | table:tsd, index:union_id_index(union_id) | range: decided by [eq(test.t_system_diagnosis.union_id, test.t_combined_diagnosis_rel.ref_diagnosis_id)], keep order:false, stats:pseudo |
| └─TableRowIDScan_17(Probe) | 10.00 | cop[tikv] | table:tsd | keep order:false, stats:pseudo |
±-----------------------------------±--------±----------±----------------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

| username: 小龙虾爱大龙虾 | Original post link

You changed the collation, it’s faster now.

| username: TiDBer_0mjTpEPL | Original post link

I don’t understand, could you please explain how to modify it?

| username: TiDBer_0mjTpEPL | Original post link

Should CHARACTER SET and COLLATE be consistent?