MySQL Forums
Forum List  »  Optimizer & Parser

QUERY OPTIMIZATION FOR UNION ALL IN MYSQL 4.1.22
Posted by: aswini nayak
Date: September 03, 2008 03:30AM

Hi,
Am runing a query which takes 4-5 seconds for execution. Can anyone help me out where am wrong.. Thanks a ton in advance

Query:

select p.post_id post_id,0 as client_id,p.post_title post_title,p.group_id as group_id,g.group_name,
p.created_on created_on,p.active_version active_version,p.post_content post_content,s.username as postedby,
'post' as 'tbldata' from posts p,groups g,subscription s where s.id=p.author_id and MATCH(p.post_content,p.post_title)
AGAINST ('MCD' IN BOOLEAN MODE) and g.group_id=p.group_id and p.is_deleted='0'

union all

select bbc.id as post_id,bbc.client_id as client_id,bbc.title as post_title,'group_id','group_name',bbc.date
as created_on,'active_version',bbc.body as post_content,cbc.name,'bbc' as 'tbldata' from buzzbanter_bc bbc,
contributors_bc cbc where bbc.contrib_id=cbc.id and bbc.approved='1' AND bbc.is_live='1' AND bbc.show_on_web='1' and MATCH(bbc.body,bbc.title) AGAINST ('MCD' IN BOOLEAN MODE)


union all

select bbt.id as post_id,0 as client_id,bbt.title as post_title,'group_id','group_name',bbt.date as created_on,
'active_version',bbt.body as post_content,c.name,'bbt' as 'tbldata' from buzzbanter_td bbt,contributors c
where bbt.contrib_id=c.id and bbt.approved='1' AND bbt.is_live='1' AND
bbt.show_on_web='1' and MATCH(bbt.body,bbt.title) AGAINST ('MCD' IN BOOLEAN MODE) order by created_on desc LIMIT 0,5

Explain
=========

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY p ALL author_id (NULL) (NULL) (NULL) 107 Using where
1 PRIMARY g eq_ref PRIMARY PRIMARY 4 minyanville_stage.p.group_id 1
1 PRIMARY s eq_ref PRIMARY PRIMARY 4 minyanville_stage.p.author_id 1
2 UNION cbc system PRIMARY,user_id,NewIndex,myindex (NULL) (NULL) (NULL) 1
2 UNION bbc ALL (NULL) (NULL) (NULL) (NULL) 11 Using where
3 UNION bbt ref is_live,approved approved 1 const 31918 Using where
3 UNION c ref PRIMARY,id id 4 minyanville_stage.bbt.contrib_id 1 Using where
(NULL) UNION RESULT <union1,2,3> ALL (NULL) (NULL) (NULL) (NULL) (NULL) Using filesort



Edited 1 time(s). Last edit at 09/03/2008 03:45AM by aswini nayak.

Options: ReplyQuote


Subject
Views
Written By
Posted
QUERY OPTIMIZATION FOR UNION ALL IN MYSQL 4.1.22
8898
September 03, 2008 03:30AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.