This query takes around 2 seconds when the order by is included, without the order it takes less than a tenth of a second. The field that is doing the sorting, qc_products.addedDate, is an index and is a datetime.

What can I do to speed this up other than removing the order by?

SELECT p.productId, vo.Item_Price1,vo.Item_Price2 ,vo.samefldALU, p.visible
    qc_productInCategories as pc,
    qc_categories as c, 
    qc_products as p,
    qc_variantOptions AS vo, 
    qc_variants AS v
WHERE c.name='Coquette' 
    AND c.categoryId = pc.categoryId 
    AND pc.productId = p.productId
    AND p.productId = v.productId 
    AND v.variantId = vo.variantId 
    AND vo.visible=1 
    AND p.visible=1 
GROUP BY vo.samefldALU 
ORDER BY p.addedDate DESC
  • 2
    Performance aside, you're doing a GROUP BY on one field, but rest of the fields are not aggregated. You won't get a result you expect. Nov 18 '14 at 1:06
  • Can you expand on what you mean @MannyCalavera? Thanks.
    – marcusds
    Nov 18 '14 at 2:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.