help with slow query

[thompom]

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

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

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

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);

 

[thompom]

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