select t1.empOid,t1.empId,t1.name,t1.nameLang2,t5.name,group_concat(t6.pcode),t3.companyName,t4.typeName,t2.contractType,group_concat(distinct t2.contractStartDate),group_concat(distinct t2.contractEndDate),t2.probationDays,t2.probationExpDate,t1.workCity,t6.pcodeOid from hrs_employee t1 left join hrs_empOffer t2 on t1.empOid=t2.empOid left join hrs_company t3 on t1.companyOid=t3.companyOid left join hrs_empType t4 on t1.empTypeOid=t4.empTypeOid left join hrs_department t5 on t1.depOid=t5.depOid left join (select p3.empId,p1.pcode,p1.pcodeOid,p5.empoid from cnf_pcode p1, cnf_prjInfo p2, cnf_prjMemberTier p3, (select empid,(case when terminatedate is null then curdate() else date_sub(terminatedate, interval 1 day) end) date from hrs_employee) p4, hrs_employee p5 where p1.pcodeOid=p2.pcodeOid and p2.prjOid=p3.prjOid and p3.empid=p4.empid and p4.empid=p5.empid and p3.execDate<p4.date and p3.expireDate>p4.date) t6 on t1.empoid=t6.empoid group by t1.empoid;额 好吧 表有点多 前半部分 select t1.empOid,t1.empId,t1.name,t1.nameLang2,t5.name,group_concat(t6.pcode),t3.companyName,t4.typeName,t2.contractType,group_concat(distinct t2.contractStartDate),group_concat(distinct t2.contractEndDate),t2.probationDays,t2.probationExpDate,t1.workCity,t6.pcodeOid from hrs_employee t1 left join hrs_empOffer t2 on t1.empOid=t2.empOid left join hrs_company t3 on t1.companyOid=t3.companyOid left join hrs_empType t4 on t1.empTypeOid=t4.empTypeOid left join hrs_department t5 on t1.depOid=t5.depOid
t1 和另外t2 t3 t4 t5四张表外连接 查询正常 0.01s 后半部分 select p3.empId,p1.pcode,p1.pcodeOid,p5.empoid from cnf_pcode p1, cnf_prjInfo p2, cnf_prjMemberTier p3, (select empid,(case when terminatedate is null then curdate() else date_sub(terminatedate, interval 1 day) end) date from hrs_employee) p4, hrs_employee p5 where p1.pcodeOid=p2.pcodeOid and p2.prjOid=p3.prjOid and p3.empid=p4.empid and p4.empid=p5.empid and p3.execDate<p4.date and p3.expireDate>p4.date
也是几张表连接 查询正常 0.01s的样子 为什么合在一起要花0.3s 怎么改进
t1 和另外t2 t3 t4 t5四张表外连接 查询正常 0.01s 后半部分 select p3.empId,p1.pcode,p1.pcodeOid,p5.empoid from cnf_pcode p1, cnf_prjInfo p2, cnf_prjMemberTier p3, (select empid,(case when terminatedate is null then curdate() else date_sub(terminatedate, interval 1 day) end) date from hrs_employee) p4, hrs_employee p5 where p1.pcodeOid=p2.pcodeOid and p2.prjOid=p3.prjOid and p3.empid=p4.empid and p4.empid=p5.empid and p3.execDate<p4.date and p3.expireDate>p4.date
也是几张表连接 查询正常 0.01s的样子 为什么合在一起要花0.3s 怎么改进
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+--------+------------------------------------------------------+--------------------------+---------+------------------------+------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2319 | |
| 1 | PRIMARY | t2 | ref | hrs_EmpOffer_Employee | hrs_EmpOffer_Employee | 5 | lvstrial.t1.empOid | 1 | |
| 1 | PRIMARY | t3 | eq_ref | PRIMARY | PRIMARY | 4 | lvstrial.t1.companyOid | 1 | |
| 1 | PRIMARY | t4 | eq_ref | PRIMARY | PRIMARY | 4 | lvstrial.t1.empTypeOid | 1 | |
| 1 | PRIMARY | t5 | eq_ref | PRIMARY | PRIMARY | 4 | lvstrial.t1.depOid | 1 | |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2215 | |
| 2 | DERIVED | <derived3> | ALL | NULL | NULL | NULL | NULL | 2278 | |
| 2 | DERIVED | p3 | ref | cnf_PrjMemberTier_PrjMember,cnf_PrjMemberTier_PrjMbr | cnf_PrjMemberTier_PrjMbr | 63 | p4.empid | 1 | Using where |
| 2 | DERIVED | p2 | eq_ref | PRIMARY,cnf_PrjInfo_FinanceInfo | PRIMARY | 4 | lvstrial.p3.prjOid | 1 | |
| 2 | DERIVED | p1 | eq_ref | PRIMARY | PRIMARY | 4 | lvstrial.p2.pcodeOid | 1 | |
| 3 | DERIVED | hrs_employee | ALL | NULL | NULL | NULL | NULL | 2319 | |
+----+-------------+--------------+--------+------------------------------------------------------+--------------------------+---------+------------------------+------+-------------+
这是执行计划