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
Best 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 emptyPriceRuleId
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.
I hope I understood the problem correctly.