mysql - 根据服务器上的执行时间和负载，以下哪一个是最佳查询

Question

这是我的两个 MySQL 查询，可以指导我哪一个是根据 MYSQl DATABase 使用的最佳查询

下面是我的两个 sql 查询

查询1)

 select cast(sum(G1.amount)as decimal(8,2)) as YTDRegularPay,cast(sum(b1.amount)as decimal(8,2))as YTDBonusPay 
 from tbl_employees_swc_grosswagedetails g1,tbl_employees_swc_grosswagedetails b1 
 where g1.empid=b1.empid 
 and g1.PayYear=b1.PayYear 
 and g1.PayperiodNumber=b1.PayperiodNumber  
 and g1.Fedtaxid=b1.Fedtaxid
 and g1.fedtaxid=998899889 
 and g1.payyear=2011 
 and g1.PayperiodNumber<=26 
 and g1.Wage_code='GRTT'
 and g1.Taxing_AuthType=b1.Taxing_AuthType 
 and g1.empid=1005 and b1.wage_code='GRSP' 
 and g1.taxing_AuthType='FED' ;

和查询2)

select abc.Amount as YTDRegularPay,def.Amount as YTDBonusPay
from (select Cast(sum(EG.Amount) as Decimal(8,2)) as Amount 
      from tbl_employees_swc_grosswagedetails EG 
      where EG.FedTaxID=998899889 
      and EG.EmpID=1005
      and PayYear=2011 
      and EG.PayPeriodNumber<=26 
      and EG.Wage_code='GRTT' 
      and Taxing_AuthType='FED') as abc,
(select Cast(sum(EG.Amount) as Decimal(8,2)) as Amount
from tbl_employees_swc_grosswagedetails EG 
where EG.FedTaxID=998899889 
and EG.EmpID=1005
and PayYear=2011 
and EG.PayPeriodNumber<=26 
and EG.Wage_code='GRSP'
and Taxing_AuthType='FED') as def ;

这是我的表结构

 delimiter $$

 CREATE TABLE `tbl_employees_swc_grosswagedetails` (

 `id` int(11) NOT NULL AUTO_INCREMENT,
`empid` int(11) NOT NULL,
`Fedtaxid` varchar(9) NOT NULL,
`Wage_code` varchar(45) NOT NULL,
 `Amount` double NOT NULL,
 `Hrly_Rate` double DEFAULT NULL,
`Num_hours` double DEFAULT NULL,
`Taxing_AuthType` varchar(10) DEFAULT NULL,
`Taxing_Auth_Name` varchar(10) DEFAULT NULL,
`PayperiodNumber` int(11) NOT NULL,
`PayYear` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `empid` (`empid`),
 CONSTRAINT `empid` FOREIGN KEY (`empid`) REFERENCES `tblemployee` (`EmpID`) 
     ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=359 DEFAULT CHARSET=latin1$$

任何好的查询，其他这些都是非常可观的

感谢广告，

拉格哈文德拉.V

Answer 1

我想说第一个更好，因为使用 JOIN 几乎总是比使用子查询更好。还建议显式编写 JOIN(尽管这在性能方面并不重要)，如下所示:

SELECT 
    CAST(SUM(G1.amount) AS decimal(8,2)) AS YTDRegularPay,
    CAST(SUM(b1.amount) AS decimal(8,2)) AS YTDBonusPay
FROM 
    tbl_employees_swc_grosswagedetails g1,
JOIN 
    tbl_employees_swc_grosswagedetails b1 ON g1.empid = b1.empid
                                          AND g1.PayYear = b1.PayYear
                                          AND g1.PayperiodNumber = b1.PayperiodNumber
                                          AND g1.Taxing_AuthType = b1.Taxing_AuthType
                                          AND g1.Fedtaxid = b1.Fedtaxid
 WHERE 
     g1.fedtaxid = 998899889
     AND g1.payyear = 2011
     AND g1.PayperiodNumber <= 26
     AND g1.Wage_code = 'GRTT'
     AND b1.wage_code = 'GRSP'
     AND g1.empid = 1005
     AND g1.taxing_AuthType = 'FED';

添加一些索引可能也有助于加快这两个查询的速度。由于您在 WHERE 子句中使用了许多列，因此您需要根据数据结构选择要索引的列。尝试添加一堆索引，使用 EXPLAIN 运行查询并查看使用了哪个索引 - 这个索引将是最有效的索引，您可以删除其他索引。