Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2009
    Posts
    1

    Unanswered: Help regarding query optimization

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you would like some help, may i suggest that you qualify ~every~ column with its table reference

    and add some query formatting, please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •