Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 针对这条SQL, TiDB 的查询速度较慢，是什么引起的？
|
username: 等一分钟
[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.
|
username: 
|
username: 
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
Praising your ability to create titles that attract people to click in 
Could you please share the table structure and index details?
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
|
username: 
|
username: 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
Are the results different? Are the data on both sides the same?
|
username: