Hi All,

I need help in optimization of my following query, which takes too much time to pull out all the data... I have build indexes on all columns which involve in 'joins'... any suggestion or idea to improve it...?


select x.question_id as question_id, (select text from vw_questions where vw_questions.id = x.question_id) as question_text, sum(case when vans.nature = x.answer_nature then x.no_of_clicks else 0 end) as no_of_clicks ,
vans.text as answer_nature,
coalesce(segment,0) as segment, coalesce(hardware_type,0) as hardware_type, coalesce(language_id,0) as language_id,
coalesce(datetime,0) as datetime, coalesce(title_section,0) as title_section, coalesce(tweeks,0) as tweeks, coalesce(tdays,0) as tdays,
coalesce(tmonths,0) as tmonths from (
select sum(t1.no_of_clicks) as no_of_clicks, t1.answer_nature as answer_nature, (select Value from warehouselookups where t1.segment = warehouselookups.id) as segment, (select Value from warehouselookups where t1.hardware_type = warehouselookups.id) as hardware_type, t1.language_id as language_id,
(select max(Date(dt3.datetime)) from dimension_time dt3 where dt3.wtag = 1 and dt3.tweeks = t3.tweeks) as datetime,
(select vw_questions.id as text from vw_questions,vw_answers where vw_questions.id = vw_answers.questionId and vw_answers.nature = answer_nature and vw_questions.question_nature = t4.question_nature and vw_questions.text is not null and vw_questions.lang_id = 1 limit 1) as question_id,
t4.question_nature as title_section, t3.tweeks, t3.tdays, t3.tmonths from wh_answers_analytics t1, warehouselookups t2, dimension_time t3, vw_questions t4 where t3.id = t1.time_dimension_id and t2.id = t1.website_id and t1.question_id = t4.id
and t3.datetime >= '2009-11-16 00:00:00'
and t3.datetime < '2009-11-22 23:59:59' and t2.id in (93,94,95,96,97,99,100,101,102,103,104,105,528,530 ,532,541,552) and
t1.placement_id in (106, 107, 542, 554) and t1.language_id in (1, 2) and hardware_type in (524, 523, 550) and segment in (525, 526, 551) group by t4.question_nature, t1.answer_nature, t1.hardware_type, t1.segment, t3.tweeks order by t3.datetime asc) x , vw_answers vans
where question_id=vans.questionId group by question_text, vans.nature, x.hardware_type, x.segment, x.tweeks order by datetime


Thanks,
Usman Farooqi

if you would like some help, may i suggest that you qualify ~every~ column with its table reference

and add some query formatting, please