Thursday, 5 September 2013

Optimizing subselects out of query

Optimizing subselects out of query

I have a MySQL query that cherry picks different pieces of data from
different tables to build a report. Example:
SELECT
u.id,
u.first_name,
u.last_name,
(select count(*) from monkeys where owner_id = u.id) as pet_monkeys,
((select count(*) from speeding_tickets where owner_id = u.id) + (select
count(*) from parking_tickets where owner_id = u.id)) as
moving_violations,
FROM
user as u
WHERE
u.id = 12345
In the real code there are about 20 sub-selects pulling statistics from
different tables. This query is also nightmarishly slow.
Is there a better way to organize the above query with a JOIN or a UNION
or something?

No comments:

Post a Comment