D&C GLug - Home Page

[ Date Index ] [ Thread Index ] [ <= Previous by date / thread ] [ Next by date / thread => ]

Re: [LUG] [OT] MySQL help needed

 

Matthew Cremore wrote:
> 
> 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.

I'm curious as to why you did this.

> Anyone got any suggestions on what may be wrong? 

What does "explain" say?

http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

> 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).

SQL_NO_CACHE I've not seen used before.

Since it forces all subqueries to be redone, it will result in slow
performance, but since you won't use it when live it isn't
representative of the performance you'll get unless every query result
changes every time.

> 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