raghavendra v raghavendra v - 2 months ago 5
MySQL Question

which among the following is the best query asper execution time and load on server

here goes my two MySQL Queries and can some guide me which is the best query to use as per MYSQl DATABase

the below goes my two sql queries

query 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' ;


and
Query 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 ;


Here goes my Table structure

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$$


any good query else these are very much appreciable

Thanks IN adv,

Raghavendra.V

Answer

I would say the first one is better, since using JOIN is almost always better than using a subquery. It is also recommended to write the JOIN explicitly (though it does not matter in terms of performance), like this:

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';

Adding some indexes will probably help as well to make both queries quicker. Since you use many columns in your WHERE clause, you need to choose which ones to index according to the data structure. Try adding a bunch of indexes, run the query with EXPLAIN and see which index is used - this one would be the most effective one and than you can drop the others.