4
\$\begingroup\$

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    Extra
1  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 index
2  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
\$\endgroup\$
2
  • \$\begingroup\$ The index on response.date cannot be used because you are using a function on the column. \$\endgroup\$ – Darhazer Mar 1 '13 at 9:21
  • \$\begingroup\$ sorted - got it down 1 sec - had to create separate columns for date and hour and remove all functions on date fields \$\endgroup\$ – zima10101 Mar 2 '13 at 12:40
3
\$\begingroup\$

In your Select statement you are doing this

WHERE UCASE(response.reasonid) <> 'FIRST' 
  AND UCASE(response.reasonid) <> 'CANCELLED' 
  AND UCASE(response.reasonid) <> 'WEBSITE' 
  AND DATE(response.date) = '20130228' 

Which is about the same as doing this

WHERE UCASE(response.reasonid) NOT IN ('FIRST','CANCELLED','WEBSITE')
  AND DATE(response.date)='20130228'

Both of which are slower because of the use of the Not Equals function in the query. But using the IN statement makes the code a little less redundant looking.

If you could change this query to use the Equals function in the where statement instead, this query would run much faster.

For References on why this is please see my answer to a similar question.

Relevant links from answer

I also think that if you use a temp table instead of nested select statements you could speed this up as well, because you could add Primary keys and indexes to the temp table helping the engine to sort the results faster.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.