[ Date Index ] [ Thread Index ] [ <= Previous by date / thread ] [ Next by date / thread => ]
In addition I would double check your indexes - you want each field involved in a join and the wheres. You may find the best mileage in making it a single query and methodically reorganising it to optimise it (use explain to help , especially look for table scans if it is that slow and slap on indexes.) Remember though indexes slow updates. Steve Lee 2009/3/8 Simon Waters <simon@xxxxxxxxxxxxxx>: > 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