Slow MySQL query - 1200 rows in 5secs

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 eventtypei

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.

StackExchange Code Review Q#23430, answer score: 3