OUTER JOIN vs UNION ... faster?
Got a query that looks like:
========================================================================
SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
FROM send0,card_info,category_details
WHERE send0.card_id=card_info.card_id
AND category_details.mcategory='e-cards'
AND card_info.main_cat=category_details.category
AND send_date >= '2001/04/08'
AND send_date <= '2001/05/14' group by 1,2
UNION ALL
SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
FROM send1,card_info,category_details where send1.card_id=card_info.card_id
AND category_details.mcategory='e-cards'
AND card_info.main_cat=category_details.category
AND send_date >= '2001/04/08'
AND send_date <= '2001/05/14' group by 1,2
UNION ALL
SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
FROM send2,card_info,category_details where send2.card_id=card_info.card_id
AND category_details.mcategory='e-cards'
AND card_info.main_cat=category_details.category
AND send_date >= '2001/04/08'
AND send_date <= '2001/05/14' group by 1,2
UNION ALL
SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
FROM send3,card_info,category_details where send3.card_id=card_info.card_id
AND category_details.mcategory='e-cards'
AND card_info.main_cat=category_details.category
AND send_date >= '2001/04/08'
AND send_date <= '2001/05/14' group by 1,2
UNION ALL
SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
FROM send4,card_info,category_details where send4.card_id=card_info.card_id
AND category_details.mcategory='e-cards'
AND card_info.main_cat=category_details.category
AND send_date >= '2001/04/08'
AND send_date <= '2001/05/14' group by 1,2
UNION ALL
SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
FROM send5,card_info,category_details where send5.card_id=card_info.card_id
AND category_details.mcategory='e-cards'
AND card_info.main_cat=category_details.category
AND send_date >= '2001/04/08'
AND send_date <= '2001/05/14' group by 1,2
UNION ALL
SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
FROM send6,card_info,category_details where send6.card_id=card_info.card_id
AND category_details.mcategory='e-cards'
AND card_info.main_cat=category_details.category
AND send_date >= '2001/04/08'
AND send_date <= '2001/05/14' group by 1,2
UNION ALL
SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
========================================================================
*Really* dreading the thought of changing it to an OUTER JOIN, and am
wondering if there would be a noticeable speed difference between going
from the UNION above to an OUTER JOIN, or should they be about the same?
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
FROM send0,card_info,category_details
WHERE send0.card_id=card_info.card_id
AND category_details.mcategory='e-cards'
AND card_info.main_cat=category_details.category
AND send_date >= '2001/04/08'
AND send_date <= '2001/05/14' group by 1,2
...
UNION ALL
SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
FROM send6,card_info,category_details where send6.card_id=card_info.card_id
AND category_details.mcategory='e-cards'
AND card_info.main_cat=category_details.category
AND send_date >= '2001/04/08'
AND send_date <= '2001/05/14' group by 1,2UNION ALL
SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
========================================================================
*Really* dreading the thought of changing it to an OUTER JOIN, and am
wondering if there would be a noticeable speed difference between going
from the UNION above to an OUTER JOIN, or should they be about the same?
afaict the point of this query is to do joins on separate tables send0
through send6. An outer join won't help you here. The last clause pulls
everything out of the other tables involved in the previous joins, so
I'm *really* not sure what stats you are calculating. But they must be
useful to have done all this work ;)
But if you had constructed those tables (or are they views?) to avoid an
outer join somehow, you could rethink that. An outer join on the two
tables card_info and category_details should be much faster than six or
seven inner joins on those tables plus the union aggregation.
- Thomas