dear all,
please help!
CREATE TABLE `po` (
`id` int(12) NOT NULL AUTO_INCREMENT,
`PID` varchar(8) DEFAULT NULL,
`PONo` varchar(20) DEFAULT NULL,
`DeliveryDate` date DEFAULT NULL,
`DeliveryID` varchar(4) DEFAULT NULL,
`dcharges` tinyint(1) DEFAULT '0',
`Quantity` int(10) DEFAULT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `PID` (`PID`),
KEY `DeliveryID` (`DeliveryID`),
KEY `DeliveryDate` (`DeliveryDate`),
KEY `active` (`active`)
) ENGINE=MyISAM AUTO_INCREMENT=213119 DEFAULT CHARSET=latin1
CREATE TABLE `invoicedetail` (
`idid` int(12) NOT NULL AUTO_INCREMENT,
`invid` int(12) DEFAULT NULL,
`poid` int(12) DEFAULT NULL,
`qty` int(10) DEFAULT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`idid`),
KEY `invid` (`invid`),
KEY `poid` (`poid`),
KEY `active` (`active`)
) ENGINE=MyISAM AUTO_INCREMENT=232060 DEFAULT CHARSET=latin1
query1: //union
select * from (
select id, sum(poqty) as poqty,sum(outqty) as outqty from (
select id, Quantity as poqty, 0 as outqty from po where active
union all
select poid as id, 0 as poqty, qty as qty from invoicedetail where active
) as a where id group by id
) as a where poqty>outqty
explain result:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 205487 Using where
2 DERIVED <derived3> ALL NULL NULL NULL NULL 421181 Using where; Using temporary; Using filesort
3 DERIVED po ALL NULL NULL NULL NULL 210772 Using where
4 UNION invoicedetail ALL NULL NULL NULL NULL 231960 Using where
NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL
query2: //join
select * from(
select a.id,a.quantity,sum(ifnull(qty,0)) as qty from po as a
left join invoicedetail as b on a.id=b.poid and b.active
where a.active group by a.id
) as a where quantity>qty
explain result:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 205486 Using where
2 DERIVED a ALL NULL NULL NULL NULL 210772 Using where; Using temporary; Using filesort
2 DERIVED b ref poid poid 5 a.id 1
query3: //subquery
select * from (
select id,quantity-ifnull((select sum(qty) from invoicedetail where active and poid=a.id group by poid),0) as balanceqty from po as a
where active
) as a where balanceqty
explain result:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 205486 Using where
2 DERIVED a ALL NULL NULL NULL NULL 210772 Using where
3 DEPENDENT SUBQUERY invoicedetail ref poid poid 5 a.id 1 Using where
3 kinds of the above query taken 9-11seconds, is there another way to speed up?!
Edited 2 time(s). Last edit at 06/16/2011 05:09AM by mo melvin.