QUERY OPTIMIZATION FOR UNION ALL IN MYSQL 4.1.22
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.