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
explain result below
also the following indexes have been created
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
SQL:
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
Code:
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
Code:
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);