SilverCart Forum
We moderate this Forum and we're here to help. Have you already run a forum search to check if your problem has already been solved?
You can help us helping you by providing detailed error messages, screenshots and logfile entries.
| Page: 1 | ||
| Topic Db optimization | 5312 Views | |
Db optimization
27 February 2012 at 1:13pm Last edited: 27 February 2012 2:07pmThis query
SELECT count(*) FROM (SELECT `SilvercartProduct`.`ClassName`, `SilvercartProduct`.`Created`, `SilvercartProduct`.`LastEdited`, `SilvercartProduct`.`Title`, `SilvercartProduct`.`ShortDescription`, `SilvercartProduct`.`LongDescription`, `SilvercartProduct`.`MetaDescription`, `SilvercartProduct`.`MetaTitle`, `SilvercartProduct`.`MetaKeywords`, `SilvercartProduct`.`ProductNumberShop`, `SilvercartProduct`.`ProductNumberManufacturer`, `SilvercartProduct`.`Weight`, `SilvercartProduct`.`isFreeOfCharge`, `SilvercartProduct`.`EANCode`, `SilvercartProduct`.`isActive`, `SilvercartProduct`.`PurchaseMinDuration`, `SilvercartProduct`.`PurchaseMaxDuration`, `SilvercartProduct`.`PurchaseTimeUnit`, `SilvercartProduct`.`StockQuantity`, `SilvercartProduct`.`StockQuantityOverbookable`, `SilvercartProduct`.`PackagingQuantity`, `SilvercartProduct`.`SortOrder`, `SilvercartProduct`.`PurchasePriceCurrency`, `SilvercartProduct`.`PurchasePriceAmount`, `SilvercartProduct`.`MSRPriceCurrency`, `SilvercartProduct`.`MSRPriceAmount`, `SilvercartProduct`.`PriceGrossCurrency`, `SilvercartProduct`.`PriceGrossAmount`, `SilvercartProduct`.`PriceNetCurrency`, `SilvercartProduct`.`PriceNetAmount`, `SilvercartProduct`.`SilvercartTaxID`, `SilvercartProduct`.`SilvercartManufacturerID`, `SilvercartProduct`.`SilvercartProductGroupID`, `SilvercartProduct`.`SilvercartMasterProductID`, `SilvercartProduct`.`SilvercartAvailabilityStatusID`, `SilvercartProduct`.`SilvercartProductConditionID`, `SilvercartProduct`.`SilvercartQuantityUnitID`, `SilvercartProduct`.`ImageID`, `SilvercartProduct`.`ID`, CASE WHEN `SilvercartProduct`.`ClassName` IS NOT NULL THEN `SilvercartProduct`.`ClassName` ELSE 'SilvercartProduct' END AS `RecordClassName`, CASE WHEN SPGMSO.SortOrder THEN CONCAT(SPGMSO.SortOrder, SilvercartProduct.SortOrder) ELSE SilvercartProduct.SortOrder END AS `_SortColumn0` FROM `SilvercartProduct` LEFT JOIN SilvercartProductGroupMirrorSortOrder SPGMSO ON SPGMSO.SilvercartProductGroupPageID = 79 AND SPGMSO.SilvercartProductID = SilvercartProduct.ID WHERE ((`isFreeOfCharge` = 1 OR `PriceGrossAmount` != 0.0) AND (`SilvercartProductGroupID` = '79' OR
`SilvercartProduct`.`ID` IN ('512','511','510','509','508','507','506','505','504','503','502','501','500','499','484','489','494','495','496','497','498','271','483','485','486','487','488','490','491','492','493')) AND isActive = 1) GROUP BY `SilvercartProduct`.`ClassName`, `SilvercartProduct`.`Created`, `SilvercartProduct`.`LastEdited`, `SilvercartProduct`.`Title`, `SilvercartProduct`.`ShortDescription`, `SilvercartProduct`.`LongDescription`, `SilvercartProduct`.`MetaDescription`, `SilvercartProduct`.`MetaTitle`, `SilvercartProduct`.`MetaKeywords`, `SilvercartProduct`.`ProductNumberShop`, `SilvercartProduct`.`ProductNumberManufacturer`, `SilvercartProduct`.`Weight`, `SilvercartProduct`.`isFreeOfCharge`, `SilvercartProduct`.`EANCode`, `SilvercartProduct`.`isActive`, `SilvercartProduct`.`PurchaseMinDuration`, `SilvercartProduct`.`PurchaseMaxDuration`, `SilvercartProduct`.`PurchaseTimeUnit`, `SilvercartProduct`.`StockQuantity`, `SilvercartProduct`.`StockQuantityOverbookable`, `SilvercartProduct`.`PackagingQuantity`, `SilvercartProduct`.`SortOrder`, `SilvercartProduct`.`PurchasePriceCurrency`, `SilvercartProduct`.`PurchasePriceAmount`, `SilvercartProduct`.`MSRPriceCurrency`, `SilvercartProduct`.`MSRPriceAmount`, `SilvercartProduct`.`PriceGrossCurrency`, `SilvercartProduct`.`PriceGrossAmount`, `SilvercartProduct`.`PriceNetCurrency`, `SilvercartProduct`.`PriceNetAmount`, `SilvercartProduct`.`SilvercartTaxID`, `SilvercartProduct`.`SilvercartManufacturerID`, `SilvercartProduct`.`SilvercartProductGroupID`, `SilvercartProduct`.`SilvercartMasterProductID`, `SilvercartProduct`.`SilvercartAvailabilityStatusID`, `SilvercartProduct`.`SilvercartProductConditionID`, `SilvercartProduct`.`SilvercartQuantityUnitID`, `SilvercartProduct`.`ImageID`, `SilvercartProduct`.`ID`, CASE WHEN `SilvercartProduct`.`ClassName` IS NOT NULL THEN `SilvercartProduct`.`ClassName` ELSE 'SilvercartProduct' END) all_distinct;
is not optimized, better to build an index in ProductGroupMirror between order and productID.
Not sure about the correct index, still testing, thi query is slow...
Thank you!
Re: Db optimization
1 March 2012 at 2:11pmHi Gregorio,
thank you for pointing this out. We'll look into this and see what we can do to improve it.
Cheers
Ramon