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
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:
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
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