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 O开发者_StackOverflowN 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
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.
精彩评论