optimizing my current query

[gardan06]

the code the previous programmer had was it generates numerous mysql_query commands, making the site terribly laggy. to optimize it, i combined the small queries into one big query, as shown in the 2nd query:

 

select a.sku,b.sku from sku_location a, sku b where a.sku = b.sku and location_code = '99' and b.status = 'active' and b.item_type_code = 'RS' and b.raw_material = '0' order by b.sku

the sku values here are then while-looped for the query below:

select * from (SELECT a.sku, qty_on_hand_late, ROISS, SafetyStockQty, LeadTime, sku_desc, uom, SuppMinimumOrderQty, OrderBy, standard_cost, qty_on_expected_trial, qty_on_expected, DSA FROM sku_location a, sku b where a.sku=b.sku and a.sku = '".$mysku."' and location_code = '".$mylocation."') as a, (SELECT sum(a.qty_issue) as TOT_ISS FROM requisition_detail a inner join requisition_hdr b on (a.pr_no = b.pr_no) inner join users c on (b.issuedby = c.username) where a.sku = '$mysku' and (b.issuedbyd between '$startdate' and '$enddate') and c.location = '$mylocation' limit 0,1) as b, (SELECT sum(a.qty_issue_store) as TOT_ISS_STO FROM requisition_detail a inner join requisition_hdr b on (a.pr_no = b.pr_no) left join users c on (b.issuedbystore = c.username) where a.sku = '$mysku' and (b.issuedbystored between '$startdate' and '$enddate') and c.location = '$mylocation' limit 0,1) as c, (SELECT sum(qty_iss) as TOT_ISS_MAN FROM issuance_manual where sku = '$mysku' and (issuedbyd between '$startdate' and '$enddate')) as d;

 

here's the thing, i think i still can optimize this 2 queries by combining these 2 queries together, but the problem is that for almost 3 days now, i still dont know how to do so.

 

here are the things i tried:

- tried creating a view table for the 2nd query. it didnt work because of error 1349: View's SELECT contains a subquery in the FROM clause.

- tried combining the 1st query to the 2nd query. while query "a" in the 2nd query got the values, queries "b", "c" and "d" got totally different values, probably because it also calculated the sums of the other SKUs

- tried combining 1st query to query "a" of the 2nd query, and i was actually successful. i tried joining it (the combined query) to query "b" it generates a "group by" error.

 

right now, im stumped that's why i really need help from you guys. your help is greatly appreciated.

 

thanks.

[fenway]

I can't read that... what are you actually trying to do?