Can anyone help me speed up this query?  At present, it returns 1200 rows in 5 secs.  I've notice that it is looking at 240000 response records.  I think this is where the issue may be.I've created the following indexes: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);SELECT statement: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 users_eden.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) <> 'CANCELLED'       AND UCASE(response.reasonid) <> 'WEBSITE'       AND DATE(response.date) = '20130228'     ORDER BY DATE(response.date) ASC,      TIME(response.date) ASC) AS response2     ON HOUR(response2.date) = HOUR(diarytime.diarytime)Results of explain:id select_ty table          type   poss_keys  key           key_len  ref    rows    Extra1  PRIMARY   diarytime      index             idx_diarytime  4              24      Using index     1  PRIMARY   <derived2>     ALL                                             1119              2  DERIVED   response       ALL    idx_actionid                             240542  Using filesort  2  DERIVED   action         eq_ref PRIMARY    PRIMARY        4       response.actionid   1         2  DERIVED   users          eq_ref PRIMARY    PRIMARY        4       response.userid  1 Using index 2  DERIVED   responsetype   eq_ref PRIMARY    PRIMARY        4       response.typeid  1    2  DERIVED   event          eq_ref PRIMARY    PRIMARY        4       action.eventid   1       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       action.dealid    1 Using index2  DERIVED   prospect       ref    idx_actionididx_actionid  5       action.actionid  1 2  DERIVED   deptlink       eq_ref PRIMARY    PRIMARY        4       event.deptlinkid 1 2  DERIVED   dept           eq_ref PRIMARY    PRIMARY        4       deptlink.deptid  1    2  DERIVED   site           eq_ref PRIMARY    PRIMARY        4       deptlink.siteid  1   2  DERIVED   eventstocklink eq_ref PRIMARY    PRIMARY        4       action.eventstocklinkid  1 2  DERIVED   stock          eq_ref PRIMARY    PRIMARY        8       eventstocklink.stockid   1   2  DERIVED   stockstate     eq_ref PRIMARY    PRIMARY        4       eventstocklink.statusid  1
Slow MySQL query - 1200 rows in 5secs
optimization;performance;mysql;sql