MySQL: How to optimize this query?

Question

I almost spent a day to optimize this query:

SELECT 
    prod. *, 
    cat.slug category_slug, 
    sup.bname bname, 
    sup.slug bname_slug
FROM bb_admin.bb_directory_products AS prod
LEFT JOIN bb_admin.bb_categories_products AS cat 
    ON prod.primary_category_id = cat.category_id
LEFT JOIN bb_admin.bb_directory_suppliers AS sup 
    ON prod.supplier_id = sup.supplier_id
LEFT JOIN bb_admin.bb_directory_suppliers AS credit_sup 
    ON prod.credit_supplier_id = credit_sup.supplier_id
LEFT JOIN bb_admin.bb_directory_suppliers AS photo_sup 
    ON prod.photo_supplier_id = photo_sup.supplier_id
WHERE (
    prod.status = '1'
    OR prod.status = '3'
    OR prod.status = '5'
)
    AND (
        sup.active_package_id != '1'
        OR sup.active_package_id != '5'
        OR sup.active_package_id != '6'
        OR prod.supplier_id = '0'
    )
    AND (
        sup.supplier_id = '1989'
        OR credit_sup.supplier_id = '1989'
        OR photo_sup.supplier_id = '1989'
    )
GROUP BY prod.product_id
ORDER BY prod.priority_index ASC

Can you help me to optimized this query?

What is the performance issue? How big are the tables? What is the explain plan? — Gordon Linoff
– Gordon Linoff, Commented Sep 12, 2013 at 2:46
If you want us to help optimize a query, you need to show us the table and index definitions, as well as row counts for each of the tables. Maybe your tables are defined poorly. Maybe the indexes aren't created correctly. Maybe you don't have an index on that column you thought you did. Without seeing the table and index definitions, we can't tell. We also need row counts because that can affect query optimization greatly. If you know how to do an EXPLAIN or get an execution plan, put the results in the question as well. — Andy Lester
– Andy Lester, Commented Sep 12, 2013 at 3:04
Use IN() and NOT IN() in query. But avoid 1:n relationship in join. It will collect more number of rows. use Explain Extended before your query — Suresh
– Suresh, Commented Sep 12, 2013 at 3:04

hjpotter92 · Accepted Answer · 2013-09-12 02:53:40Z

1

Update your column data types to be INT or one of its variants, since the ones you are checking against are all integer IDs (assumption).
Create indexes on following columns(if possible in all tables):
- prod.status
- supplier_id
- active_package_id
Use IN clause instead of concatenating OR segments.

I'll be putting the updated WHERE clause here:

WHERE prod.status IN(1, 3, 5)
    AND ( sup.active_package_id NOT IN(1, 5, 6)
        OR prod.supplier_id = 0
    )
    AND 1989 IN (prod.supplier_id, prod.credit_supplier_id, prod.photo_supplier_id)

answered Sep 12, 2013 at 2:53

hjpotter92

81.1k36 gold badges148 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL: How to optimize this query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related