INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

help with slow query

help with slow query

(OP)
Hi,

can anyone help me speed up this query? at present it returns 192 rows but takes 4 secs.
I notice it is looking at 240000 response records - think this is where issue may be,
but don't know how to resolve

CODE --> sql

SELECT 
  response2.actionid,
  response2.typeid,
  response2.notes,
  response2.eventid,
  response2.actiondate,
  response2.userid,
  response2.eventtype,
  response2.firstname,
  response2.surname,
  response2.postcode,
  response2.eventtypeid,
  response2.dealtrue,
  response2.dealid,
  response2.eventpic,
  response2.registrationnumber,
  response2.deptlinkid,
  response2.customtype,
  response2.enquiryid,
  response2.eventstocklinkid,
  response2.cusid,
  response2.stockid,
  response2.custitle,
  response2.actiontypeid,
  response2.deptbut,
  response2.cushomtel,
  response2.cusworktel,
  response2.cusmobtel,
  response2.cusadd1,
  response2.cusadd2,
  response2.cusadd3,
  response2.cuscounty,
  response2.cushomemail,
  response2.cusworkemail,
  response2.responsetype,
  response2.date,
  response2.done,
  response2.responsebut,
  response2.reasonid,
  response2.responseid,
  response2.depttype,
  response2.responsetypeid,
  response2.username,
  response2.actionusername,
  diarytime.diarytime,
  response2.prospectmake,
  response2.prospectmod,
  response2.prospectnu,
  response2.statedesc,
  response2.site 
FROM
  diarytime 
  LEFT JOIN 
    (SELECT 
      action.actionid,
      action.typeid,
      response.notes,
      action.eventid,
      action.actiondate,
      response.userid,
      eventtype.event AS eventtype,
      cus.firstname,
      cus.surname,
      cus.postcode,
      event.typeid AS eventtypeid,
      IF(ISNULL(deal.dealid), 0, 1) AS dealtrue,
      IF(ISNULL(deal.dealid), 0, deal.dealid) AS dealid,
      eventtype.eventpic,
      IF(
        ISNULL(stock.registrationnumber),
        0,
        stock.registrationnumber
      ) AS registrationnumber,
      event.deptlinkid,
      action.customtype,
      prospect.enquiryid AS enquiryid,
      action.eventstocklinkid,
      event.cusid,
      eventstocklink.stockid,
      cus.custitle,
      action.actiontypeid,
      dept.deptbut,
      cus.cushomtel,
      cus.cusworktel,
      cus.cusmobtel,
      cus.cusadd1,
      cus.cusadd2,
      cus.cusadd3,
      cus.cuscounty,
      cus.cushomemail,
      cus.cusworkemail,
      responsetype.responsetype,
      response.date,
      response.done,
      responsetype.responsebut,
      response.reasonid,
      response.responseid,
      dept.depttype,
      response.typeid AS responsetypeid,
      response.username,
      response.username AS actionusername,
      prospect.stockmake AS prospectmake,
      prospect.stockmod AS prospectmod,
      prospect.otdbtype AS prospectnu,
      stockstate.statedesc,
      site.site 
    FROM
      response 
      INNER JOIN usersdb.users AS users 
        ON users.userid = response.userid 
      INNER JOIN ACTION 
        ON response.actionid = action.actionid 
      LEFT JOIN responsetype 
        ON responsetype.responsetypeid = response.typeid 
      LEFT JOIN EVENT 
        ON event.eventid = action.eventid 
      LEFT JOIN eventtype 
        ON eventtype.eventid = event.typeid 
      LEFT JOIN cus 
        ON cus.cusid = event.cusid 
      LEFT JOIN deal 
        ON deal.dealid = action.dealid 
      LEFT JOIN enquiries AS prospect 
        ON prospect.actionid = action.actionid 
      LEFT JOIN deptlink 
        ON deptlink.deptlinkid = event.deptlinkid 
      LEFT JOIN dept 
        ON dept.deptid = deptlink.deptid 
      LEFT JOIN site 
        ON site.siteid = deptlink.siteid 
      LEFT JOIN eventstocklink 
        ON eventstocklink.eventstocklinkid = action.eventstocklinkid 
      LEFT JOIN stock 
        ON stock.stockid = eventstocklink.stockid 
      LEFT JOIN stockstate 
        ON stockstate.stateid = eventstocklink.statusid 
    WHERE UCASE(response.reasonid) <> 'FIRST' 
      AND UCASE(response.reasonid) <> 'LOST' 
      AND DATE(response.date) = '20130228'
      AND event.deptlinkid = '1'
    ORDER BY DATE(response.date) ASC,
      TIME(response.date) ASC) AS response2 
    ON HOUR(response2.date) = HOUR(diarytime.diarytime) 

explain result below

CODE -->

id  select_type  table           type    possible_keys        key            key_len  ref                              rows    Extra           
1   PRIMARY      diarytime       index                        idx_diarytime  4                                         24      Using index     
1   PRIMARY      <derived2>      ALL                                                                                   204                     
2   DERIVED      response        ALL     idx_actionid                                                                  240542  Using filesort  
2   DERIVED      action          eq_ref  PRIMARY,idx_eventid  PRIMARY        4        response.actionid        1                       
2   DERIVED      responsetype    eq_ref  PRIMARY              PRIMARY        4        response.typeid          1                       
2   DERIVED      users           eq_ref  PRIMARY              PRIMARY        4        response.userid          1       Using index     
2   DERIVED      event           eq_ref  PRIMARY              PRIMARY        4        action.eventid           1       Using where     
2   DERIVED      eventtype       eq_ref  PRIMARY              PRIMARY        4        event.typeid             1                       
2   DERIVED      cus             eq_ref  PRIMARY              PRIMARY        8        event.cusid              1                       
2   DERIVED      deal            eq_ref  PRIMARY              PRIMARY        4        dealcrm.action.dealid            1       Using index     
2   DERIVED      prospect        ref     idx_actionid         idx_actionid   5        dealcrm.response.actionid        1                       
2   DERIVED      deptlink        eq_ref  PRIMARY              PRIMARY        4        dealcrm.event.deptlinkid         1                       
2   DERIVED      dept            eq_ref  PRIMARY              PRIMARY        4        dealcrm.deptlink.deptid          1                       
2   DERIVED      site            eq_ref  PRIMARY              PRIMARY        4        dealcrm.deptlink.siteid          1                       
2   DERIVED      eventstocklink  eq_ref  PRIMARY              PRIMARY        4        dealcrm.action.eventstocklinkid  1                       
2   DERIVED      stock           eq_ref  PRIMARY              PRIMARY        8        dealcrm.eventstocklink.stockid   1                       
2   DERIVED      stockstate      eq_ref  PRIMARY              PRIMARY        4        dealcrm.eventstocklink.statusid  1 

also the following indexes have been created

CODE -->

CREATE INDEX idx_eventid ON `action` (eventid);
CREATE INDEX idx_actionid ON `response` (actionid);
CREATE INDEX idx_date ON `response` (`date`);
CREATE INDEX idx_stockid ON `eventstocklink` (stockid);
CREATE INDEX idx_eventid ON `eventstocklink` (eventid);
CREATE INDEX idx_cusid ON `event` (cusid); 

RE: help with slow query

(OP)
sorted - had to remove HOUR func from join
then create new column for hour and create index
the query now runs in 1 sec

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close