Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

So, I am sure I have done something really stupid while designing this, and I'm open to schema changes if they'll really help me out.

On to the problem:

I have a custom shopping cart system (backed by a MySQL database) that includes a products table and a price_rules table that's used for computing discounts and applying promo code discounts. Some price rules don't have promo codes attached to them; some are simply "10% off of product X from March 1st through April 1st" or similar. Because a single price rule can apply to many individual products, I also have a join table called price_rule_product.

When showing a set of products (for example, on the main shop page or listing all products in a category) I'm currently running a separate query for each product to look for price rules that apply to that product. Here's what one of those queries looks like:

SELECT DISTINCT price_rule.* FROM price_rule
    INNER JOIN price_rule_product ON
        price_rule.id = price_rule_product.price_rule_id
    INNER JOIN product ON
        product.id = price_rule_product.product_id
    WHERE product.id = 10
        AND price_rule.apply_to = 'line_items'
        AND ( price_rule.start_date IS NULL
            OR price_rule.start_date = 0
            OR price_rule.start_date <= 1366063902 )
        AND ( price_rule.end_date IS NULL
            OR price_rule.end_date = 0
            OR price_rule.end_date >= 1366063902 )
        AND ( price_rule.promo_code IS NULL
            OR price_rule.promo_code = '' )
    ORDER BY sort ASC

Oh SQL Gods, I pray you have some suggestions/solutions for this. It is causing some significant performance issues, and I'm just not experienced enough with SQL to figure out where to go from here.

EDIT: Here's the output of EXPLAIN SELECT both with and without DISTINCT:

WITH DISTINCT

id  select_type table               type    possible_keys   key key_len ref                         rows    Extra
1   SIMPLE          product             const   PRIMARY         PRIMARY 3   const                           1   Using index; Using temporary; Using filesort
1   SIMPLE          price_rule      ALL         PRIMARY         NULL    NULL    NULL                            14  Using where
1   SIMPLE          price_rule_product  eq_ref  PRIMARY         PRIMARY 8   craft_art.price_rule.id,const   1   Using where; Using index; Distinct

WITHOUT DISTINCT

1   SIMPLE          product             const   PRIMARY         PRIMARY 3   const                           1   Using index; Using filesort
1   SIMPLE          price_rule      ALL         PRIMARY         NULL    NULL    NULL                            14  Using where
1   SIMPLE          price_rule_product  eq_ref  PRIMARY         PRIMARY 8   craft_art.price_rule.id,const   1   Using where; Using index
share|improve this question
1  
DISTINCT is commonly overused, and seems unnecessary here... it may be hurting performance my requiring the server to take extra steps to ensure distinctness of a result-set that shouldn't contain duplicates. Consider posting the output from EXPLAIN SELECT of the query... with and without the DISTINCT. –  Michael - sqlbot Apr 16 '13 at 4:00
    
ETA the output from EXPLAIN SELECT with and without DISTINCT –  Daniel Apr 16 '13 at 22:59
    
You can also remove the join to product and use the condiiton WHERE price_rule_product.product_id = 10 –  ypercube Jan 12 at 11:25
add comment

1 Answer

If I understand you correctly, you want to display the products regardless if they are associated with a price rule, right ? In my opinion, you'd best achieve this with an OUTER JOIN, which will give you all products and simply return an empty PriceRuleId column for the products without price rules.

The following query will give you a result Matrix with all products and their price rules, if they have them.

select 
     p.id as ProductId
    ,pr.id as PriceRuleId
    ,pr.apply_to as PriceRuleApplyTo
    ,pr.start_date as PriceRuleStartDate
    ,pr.end_date as PriceRuleEndDate
    ,pr.promo_code as PriceRulePromoCode
from
    products p
    LEFT OUTER JOIN price_rule_product prp ON prp.product_id = p.id
    LEFT OUTER JOIN
        ( SELECT id, apply_to, start_date, end_date, promo_code
            FROM  price_rule
            WHERE 
                apply_to = 'line_items'
                AND ( start_date IS NULL OR start_date = 0 OR start_date <= 1366063902 )
                AND ( end_date IS NULL OR end_date = 0 OR end_date >= 1366063902 )
                AND ( promo_code IS NULL OR promo_code = '' )
            ) pr ON prp.price_rule_id = pr.id

I hope I understood the problem correctly.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.