mysql - Mysql View 比 sql server 慢
问题描述
我使用 MYsql Workbench 将 Sql View 转换为 mysql View 并运行它，但不幸的是我的视图很慢。
这些是我对 MySql 的系统要求。
Windows 10
Xampp V3.2.4
Php 7.3.12
服务器版本：10.4.10-MariaDB - mariadb.org 二进制分发
服务器字符集：UTF-8 Unicode (utf8mb4)
MYsql Workbench 8.0
这些是我对 SqlServer 的系统要求。
Windows 10
sql server 2005
带图像的结果
sql server 结果
mysql 结果
这是 Mysql 查询
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `hrms_v_attendancedetails_current_prev` AS
SELECT
`e`.`compID` AS `compID`,
`e`.`compName` AS `compName`,
`e`.`compAddress` AS `compAddress`,
`e`.`empAtt` AS `empAtt`,
`ad`.`attDate` AS `attDate`,
`e`.`empID` AS `empID`,
`e`.`empExists` AS `empExists`,
`e`.`empNo` AS `empNo`,
`e`.`empDOJ` AS `empDOJ`,
`e`.`empName` AS `empName`,
`e`.`empBasicSalary` AS `BP`,
`e`.`empHrsMonth` AS `empHrsMonth`,
`e`.`empDaysMonth` AS `empDaysMonth`,
`e`.`empEPFNo` AS `empEPFNo`,
`e`.`empNameFull` AS `empNameFull`,
`e`.`empSex` AS `empSex`,
`e`.`etID` AS `etID`,
`e`.`etName` AS `etName`,
`e`.`sup_desigID` AS `sup_desigID`,
`e`.`desigName` AS `desigName`,
`e`.`sup_empID` AS `sup_empID`,
`e`.`vacID` AS `vacID`,
`e`.`intvID` AS `intvID`,
`e`.`empDOEntitlement` AS `empDOEntitlement`,
`e`.`empProbationPeriod` AS `empProbationPeriod`,
`e`.`empGraceTime` AS `empGraceTime`,
`e`.`empPaymentMode` AS `empPaymentMode`,
`e`.`actID` AS `actID`,
`e`.`actName` AS `actName`,
`e`.`cityID` AS `cityID`,
`e`.`div_cityID` AS `div_cityID`,
`e`.`empAddress` AS `empAddress`,
`e`.`empTempAddress` AS `empTempAddress`,
`e`.`empRoute` AS `empRoute`,
`e`.`empContPerson` AS `empContPerson`,
`e`.`empTempContPerson` AS `empTempContPerson`,
`e`.`empPhoneM` AS `empPhoneM`,
`e`.`empPhoneH` AS `empPhoneH`,
`e`.`empTempPhoneH` AS `empTempPhoneH`,
`e`.`empEmail` AS `empEmail`,
`e`.`empDOB` AS `empDOB`,
`e`.`empIDCardNo` AS `empIDCardNo`,
`e`.`empComments` AS `empComments`,
`e`.`empInsuranceNo` AS `empInsuranceNo`,
`e`.`empPayroll` AS `empPayroll`,
`e`.`empMachineID` AS `empMachineID`,
`e`.`empLastExitedDate` AS `empLastExitedDate`,
`e`.`deptName` AS `deptName`,
`ad`.`adInTime` AS `adInTime`,
`ad`.`adOutTime` AS `adOutTime`,
`ad`.`adOT` AS `OT`,
`ad`.`adDOT` AS `DOT`,
`ad`.`adTOT` AS `TOT`,
`ad`.`adOTMin` AS `OTMin`,
`ad`.`adDOTMin` AS `DOTMin`,
`ad`.`adTOTMin` AS `TOTMin`,
`ad`.`adLateTime` AS `adLateTime`,
`ad`.`adShortTime` AS `adShortTime`,
`ad`.`adEarlyTime` AS `adEarlyTime`,
`ad`.`adLateTime` + `ad`.`adShortTime` + `ad`.`adEarlyTime` AS `LM`,
`e`.`empAutoPresent` AS `empAutoPresent`,
`ad`.`adDateStatus` AS `adDateStatus`,
IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) AS `adDateStatus_1`,
CASE
WHEN
(LEFT(IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`),
2) = 'HX'
AND LOCATE('+',
IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`)) > 0)
THEN
SUBSTR(`ad`.`adDateStatus`,
1,
LOCATE(`ad`.`adDateStatus`, '+') - 1) + '+' + SUBSTR(IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`),
LOCATE(IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`),
'+') + 1,
5)
ELSE CASE
WHEN
(RIGHT(IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`),
2) = 'HX'
AND LOCATE('+',
IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`)) > 0)
THEN
SUBSTR(IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`),
1,
LOCATE(IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`),
'+') - 1) + '+' + SUBSTR(`ad`.`adDateStatus`,
LOCATE(`ad`.`adDateStatus`, '+') + 1,
2)
ELSE IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`)
END
END AS `adDateStatus_ReadOnly`,
CASE IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`)
WHEN 'P' THEN 1
ELSE CASE
WHEN
(IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HP'
OR IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HA+HP'
OR IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HP+HA'
OR IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HL(p)+HX'
AND `ad`.`adDateStatus` = 'HA+HP'
OR IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HX+HL(p)')
THEN
0.5
ELSE 0
END
END AS `P`,
CASE IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`)
WHEN 'A' THEN 1
ELSE CASE
WHEN
(IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HA'
OR IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HA+HP'
OR IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HP+HA'
OR IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HL(p)+HX'
AND `ad`.`adDateStatus` = 'A'
OR IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HX+HL(p)'
AND `ad`.`adDateStatus` = 'A')
THEN
0.5
ELSE 0
END
END AS `A`,
CASE IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`)
WHEN 'A' THEN 1
ELSE 0
END AS `FA`,
CASE
WHEN
(IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HA'
OR IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HA+HP'
OR IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HP+HA'
OR IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HL(p)+HA'
OR IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HA+HL(p)')
THEN
1
ELSE 0
END AS `HA`,
CASE IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`)
WHEN 'L(p)' THEN 1
ELSE 0
END AS `FL(p)`,
CASE
WHEN
(IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HL(p)+HX'
OR IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HL(p)'
OR IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HX+HL(p)')
THEN
1
ELSE 0
END AS `HL(p)`,
CASE
WHEN
IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HL(l)'
THEN
0.5
ELSE 0
END AS `HL(l)`,
CASE IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`)
WHEN 'L(p)' THEN 1
ELSE CASE
WHEN
(IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HL(p)+HX'
OR IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HL(p)'
OR IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HX+HL(p)')
THEN
0.5
ELSE 0
END
END AS `L(p)`,
CASE IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`)
WHEN 'L(l)' THEN 1
ELSE CASE
WHEN
(IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HL(l)+HX'
OR IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HX+HL(l)')
THEN
0.5
ELSE 0
END
END AS `L(l)`,
CASE IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`)
WHEN 'L(n)' THEN 1
ELSE CASE
WHEN
IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`) = 'HL(n)+HX'
THEN
0.5
ELSE 0
END
END AS `L(n)`,
CASE IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`)
WHEN 'SL' THEN 1
ELSE 0
END AS `SL`,
CASE IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`)
WHEN 'H' THEN 1
ELSE 0
END AS `H`,
CASE IFNULL(`adl`.`ladDateStatus`,
`ad`.`adDateStatus`)
WHEN 'DF' THEN 1
ELSE 0
END AS `DF`,
`e`.`deptID` AS `deptID`,
`e`.`desigID` AS `desigID`,
`ad`.`adInTime_Prev` AS `adIntime_Prev`,
`ad`.`adOutTime_Prev` AS `adOutTime_Prev`,
`ad`.`adssID_Prev` AS `adSSID_Prev`,
`ad`.`adOTApproved_Prev` AS `adOTApproved_Prev`,
`adl`.`ltName` AS `ltName`,
`adl`.`ltID` AS `ltID`,
`e`.`desigNoofAllotedEmps` AS `desigNoofAllotedEmps`,
`adl`.`ltFrequency` AS `ltFrequency`,
`adl`.`eltNOD` AS `eltNOD`,
`cd`.`htID` AS `htID`,
`e`.`deptCode` AS `deptCode`,
`e`.`desigCode` AS `desigCode`,
`ad`.`adPunch_2` AS `adPunch_2`,
`ad`.`adPunch_3` AS `adPunch_3`,
`ad`.`adPunch_4` AS `adPunch_4`,
`ad`.`adPunch_5` AS `adPunch_5`,
`ad`.`adEarlyOTApproved` AS `adEarlyOTApproved`,
`ad`.`adOTApproved` AS `adOTApproved`,
`ht`.`htName` AS `htName`,
`ht`.`htType` AS `htType`,
`e`.`compSysCompany` AS `compSysCompany`,
`e`.`compEmail` AS `compEmail`,
`e`.`compPhoneNo` AS `compPhoneNo`,
`e`.`egraID` AS `egraID`,
`e`.`egraCode` AS `egraCode`,
`e`.`egraName` AS `egraName`,
`ad`.`adLunch` AS `adLunch`,
`ad`.`adDinner` AS `adDinner`,
`ad`.`adLunch_Prev` AS `adLunch_Prev`,
`ad`.`adDinner_Prev` AS `adDinner_Prev`,
`e`.`relID` AS `relID`,
`e`.`relName` AS `relName`,
`e`.`empEarlyOT` AS `empEarlyOT`,
`ad`.`adRemarks` AS `adRemarks`,
`e`.`pgID` AS `pgID`,
`e`.`subID` AS `subID`,
`ad`.`created_by` AS `created_by`,
`ad`.`created_at` AS `created_at`,
`ad`.`edited_by` AS `edited_by`,
`ad`.`updated_at` AS `updated_at`,
`ad`.`ssID` AS `ssID`,
`ad`.`ssName` AS `ssName`,
`ad`.`ssType` AS `ssType`,
`ad`.`ssFromTime` AS `ssFromTime`,
`ad`.`ssToTime` AS `ssToTime`
FROM
(((`hrms_v_employee` `e`
JOIN `hrms_v_attendancedetails` `ad` ON (`e`.`compID` = `ad`.`compID`
AND `e`.`empID` = `ad`.`empID`))
LEFT JOIN (`hrms_tbl_holidaytype` `ht`
JOIN `hrms_tbl_calendardetails` `cd` ON (`ht`.`compID` = `cd`.`compID`
AND `ht`.`htID` = `cd`.`htID`)) ON (`ad`.`compID` = `cd`.`compID`
AND `ad`.`attDate` = `cd`.`caldetDate`))
LEFT JOIN `hrms_v_attendancedetails_leave` `adl` ON (`ad`.`compID` = `adl`.`compID`
AND `ad`.`attDate` = `adl`.`ladDate`
AND `ad`.`empID` = `adl`.`empID`))
WHERE
`ad`.`attDate` >= '11/01/2011'
解决方案
推荐阅读
- java - 有没有办法让我在扫描仪类定义的方法中分配变量 numA 并在不同的方法中使用它？
- blockchain - ERC20：转账金额超过限额，即使被批准
- python - 向量运算应该只改变向量，还是改变向量并返回运算结果？
- django - 如何让用户关联到另一个模型 DRF。ValueError：精确查找的 QuerySet 值必须使用切片限制为一个结果
- mysql - 无法在 M1 MacBook Big Sur 中安装 mysql2 0.5.3
- django - 如何在不使用 for 循环的情况下解决这个 django 查询问题？
- c# - 如何通过反射清除类中的所有静态事件处理程序？
- django - Django 的无限流响应在 apache 日志中记录 500
- python - Python：subprocess.call 和变体对于特定应用程序从执行的 .py 中失败，但不是从 CLI 中的 python
- google-cloud-firestore - Firestore 聚合 - 扇出和更新云功能