By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,961 Members | 1,333 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,961 IT Pros & Developers. It's quick & easy.

MySQL optimize a slow query

P: 1
Consider the following tables:

TICKET
------
ticket_number
redeemed
fk_purchase_id


PURCHASE
--------
purchase_id
receipt_number
quantity
fk_customer_id
fk_event_id


EVENT
-----
event_id
event_name
ticket_price


CUSTOMER
--------
customer_id
first_name
last_name

My query should produce the result as follows:

RESULT TABLE
------------
event_name ticket_price purchased_qty redeemed_qty


So far I have:
SELECT event.event_name, event.ticket_price, sum(purchase.quantity) as purchased_qty
FROM event
LEFT JOIN purchase ON purchase.fk_event_id = event.event_id
GROUP BY purchase.fk_event_id


I am stuck on how to count the number of redeemed tickets in an efficient manner. I tried using a join with subquery which is extremely slow. There is also the requirement to display al events even those that do not have any purchases. Also it is possible to have a purchase with no tickets, in which case redeemed quantity is always 0.

SELECT event.event_name, event.ticket_price, sum(p.quantity) as purchased_qty, sum(p.redeemed_q) as redeemed_qty
FROM event
LEFT JOIN (SELECT purchase.*, SUM(ticket.redeemed) as redeemed_q FROM purchase JOIN ticket ON ticket.fk_purchase_id = pr.purchase_id GROUP BY ticket.fk_purchase_id) as p ON p.fk_event_id = event.event_id
GROUP BY purchase.fk_event_id

Is there something fundamentally wrong with the database design? or is there a better way to query it. In any case changing the database design at this stage is out of the question.
Aug 11 '11 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.