[LUG] [OT] MySQL help needed

[Matthew Cremore <dclug@xxxxxxxxxxxxxxxx>]

I've just spent the weekend migrating the last few access tables to
MySQL, which seems to have gone OK. 
To cut out access altogether I've redone some of the queries in MySQL
for one of our asp web pages (the page is a load of functions which
calculates costs from data stored in MySQL). 
The thing is - instead of things going faster they've instead slowed
down enormously. I've checked indexes and they seem to be OK. It seems
to involve queries that use views. 
Basically what I've done is redo the SQL commands as they were for
Access so they'd work for MySQL. Any sub-queries I've redone and turned
into views on MySQL. 
Anyone got any suggestions on what may be wrong? 
I know I've done the right thing by migrating to MySQL but this isn't
going to look good tomorrow. 
Any help very much appreciated!! 
Using MySQL 5.0.22

More info: 
Running the main query below takes over 90 seconds. 
Running each of the views on their own takes less than a second each
(ran using SQL_NO_CACHE to force the results to be recalculated). 
Are the joins wrong? (remember I've just translated this direct from
access). 
Here's the SQL: 

Main query: 

SELECT p.CostHeadID, Sum(If(i.SumOfInvAmount>v.OrderValue Or
p.FullyInvoiced<>0,IFNULL(i.SumOfInvAmount,0),v.OrderValue)) AS
Committed 
FROM (purchase_orders p INNER JOIN AA_SumOfPOValue v ON p.POrderID =
v.POrderID) LEFT JOIN PIValueNEW i ON p.POrderID = i.POrderID 
WHERE ((p.OrderSent)<>0) 
GROUP BY p.CostHeadID, v.ContractID, p.OrderType HAVING
(((v.ContractID)=3149) AND ((p.OrderType)=2)); 

AA_SumOfPOValue view: 

select `d`.`POrderID` AS `POrderID`,sum(ifnull((((`d`.`Quantity` -
`d`.`QuantityCancelled`) * `d`.`UnitPrice`) * (1 - `d`.`Discount`)),0))
AS `OrderValue`,`c`.`ContractID` AS `ContractID`,`h`.`CostHeadID` AS
`CostHeadID` from ((`contracts` `c` join `cost_heads` `h`
on((`c`.`ContractID` = `h`.`ContractID`))) join (`purchase_orders` `p`
join `purchase_order_details` `d` on((`p`.`POrderID` = `d`.`POrderID`)))
on((`h`.`CostHeadID` = `p`.`CostHeadID`))) group by
`d`.`POrderID`,`c`.`ContractID`,`h`.`CostHeadID`; 

PIValueNEW view: 

select `i`.`POrderID` AS `POrderID`,sum(`i`.`InvAmount`) AS
`SumOfInvAmount` from `purchase_invoices` `i` group by `i`.`POrderID`; 

Thanks again for any help. 
Matt.


-- 
The Mailing List for the Devon & Cornwall LUG
http://mailman.dclug.org.uk/listinfo/list
FAQ: http://www.dcglug.org.uk/linux_adm/list-faq.html