Slow MySQL query - 1200 rows in 5secs

Question

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

• Darhazer: The index on response.date cannot be used because you are using a function on the column.
• zima10101: sorted - got it down 1 sec - had to create separate columns for date and hour and remove all functions on date fields

Answer 1

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

• answer to SQL Server "<>" operator is very slow compared to "=" on table with a few million rows

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.