Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 针对这条SQL, TiDB 的查询速度较慢,是什么引起的?
[TiDB Usage Environment] Production Environment
[TiDB Version] 6.5.1
The query speed of TiDB for the same SQL is 20 times faster than MySQL.
SELECT c.PERSONID, count(CALENDARDATE) AS 'workdays'
FROM ATDADJUSTTIMEORDER c
INNER JOIN (select * from AtdTimeOrderCalendar where CALENDARDATE BETWEEN '2023-07-01' AND '2023-09-30') d ON d.TIMEORDERID=c.TIMEORDERID
INNER JOIN psnaccount p3 ON p3.PERSONID=c.PERSONID
WHERE d.CALENDARDATE >= p3.attendondate
AND BEGINDATE <= '2023-07-01' AND c.ENDDATE >= '2023-09-30'
AND d.CALENDARDATE <= p3.DIMISSIONDATE
AND d.CALENDARTYPE = 1
GROUP BY personid
This is the execution plan
Bro, are you from the Shock Department?
Praising your ability to create titles that attract people to click in
Could you please share the table structure and index details?
The results you found, why are the IDs different?
CREATE TABLE psnaccount
(
PERSONID
varchar(40) NOT NULL DEFAULT ‘’ COMMENT ‘Person ID’,
PRIMARY KEY (PERSONID
) /*T![clustered_index] NONCLUSTERED */,
UNIQUE KEY EMPLOYEEID
(EMPLOYEEID
),
KEY ix_psnaccount_TPB
(TRUENAME
,PERSONID
,BRANCHID
),
KEY ix_psnaccount_PERSONID
(PERSONID
),
KEY ix_psnaccount_TRUENAME
(TRUENAME
),
KEY ix_psnaccount_BE
(BRANCHID
,EMPLOYEEID
),
KEY ix_psnaccount_EA
(EMPLOYEEID
,ATTENDONDATE
),
KEY idx_ATTENDONDATE
(ATTENDONDATE
),
KEY idx_DIMISSIONDATE
(DIMISSIONDATE
),
KEY idx_EmployeeTypeId
(EMPLOYEETYPEID
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
CREATE TABLE `atdadjusttimeorder` (
`begindate` date NOT NULL COMMENT 'Start Date',
`enddate` date NOT NULL COMMENT 'End Date',
`PERSONID` varchar(40) NOT NULL DEFAULT '' COMMENT 'Person ID',
`TIMEORDERID` varchar(40) DEFAULT '' COMMENT 'Shift ID',
PRIMARY KEY (`Id`) /*T![clustered_index] NONCLUSTERED */,
KEY `ATDADJUSTTIMEORDER_Main` (`PERSONID`,`begindate`,`enddate`),
KEY `ix_TIMEORDERID` (`TIMEORDERID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
CREATE TABLE atdtimeordercalendar
(
calendardate
date NOT NULL COMMENT ‘Event Date’,
TIMEORDERID
varchar(40) NOT NULL DEFAULT ‘’ COMMENT ‘Shift ID’,
CALENDARTYPE
int(11) DEFAULT ‘0’ COMMENT ‘Event Type’,
PRIMARY KEY (Id
) /*T![clustered_index] NONCLUSTERED */,
KEY ix_calendardate
(calendardate
),
KEY ix_TIMEORDERID
(TIMEORDERID
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
I deleted the useless fields.
There should be no sorting.
Are the results different? Are the data on both sides the same?
The data is the same, synchronized using DM.