Hanging mysql query

Question

I'm having an issue with the following query

select
ord.order_id, 
ordProduct.product_id,
coupProd.product_id,
coup.date_end as DateEnd, coup.coupon_id
from `order` ord
inner join order_product ordProduct on ord.order_id = ordProduct.order_id
inner join coupon_product coupProd on ordProduct.product_id = coupProd.product_id
inner join coupon coup on coupProd.coupon_id = coup.coupon_id
where (coup.date_end > curdate());

If I remvove the where clause, the query executes fine, otherwise it just hangs. Any ideas?

Does it speed it up moving the where clause to the inner join? inner join coupon coup on coupProd.coupon_id = coup.coupon_id AND (coup.date_end > curdate()); — Kyra, Mar 04 '13 at 18:07
What is difference in where clause and using AND condition. Just out of curiosity — DevelopmentIsMyPassion, Mar 04 '13 at 18:12
This explains the difference: http://stackoverflow.com/questions/8311096/whats-the-difference-between-where-clause-and-on-clause-when-table-left-join Not sure if it would fix the issue in this case though. — Kyra, Mar 04 '13 at 18:20
@AshReva: `WHERE` is for specifying a condition for filtering while `AND` is for filtering based on more than one conditional ("grouping" conditionals). — Roney Michael, Mar 04 '13 at 18:21
I think it may just be due to the sheer number of records, 30k in the order_product and coupon product table, 65k in the coupon table. I'm only running locally, so maybe i'll let it spin for 10 minutes or so. — John Czajka, Mar 04 '13 at 18:43

score 0 · Answer 1 · answered Mar 04 '13 at 18:18

It's not a solution per se, but as a workaround, you could maybe get it done as a nested query. i.e. ,

SELECT * FROM (
    SELECT
    ord.order_id, 
    ordProduct.product_id,
    coupProd.product_id,
    coup.date_end AS DateEnd, coup.coupon_id
    FROM `order` ord
    INNER JOIN order_product ordProduct ON ord.order_id = ordProduct.order_id
    INNER JOIN coupon_product coupProd ON ordProduct.product_id = coupProd.product_id
    INNER JOIN coupon coup ON coupProd.coupon_id = coup.coupon_id)
WHERE (DateEnd > CURDATE());

Hanging mysql query

1 Answers1