speed up query

[thompom]

Hi - the following takes 7 secs to run (78 rows are returned) - any ideas how to speed it up?

SELECT action.userid 
    FROM `action` 
      LEFT JOIN 
        (SELECT 
          COUNT(
            CASE
              WHEN UCASE(response.reasonid) LIKE '%APPOINTMENT%' THEN 1 
            END
          ) AS appt, response.actionid
        FROM
          response 
        GROUP BY response.actionid) AS resp 
        ON resp.actionid = action.actionid 
    WHERE DATE(action.actiondate) BETWEEN '20121001' AND '20121031' 
      AND action.typeid = '1' 
    GROUP BY action.userid

 

[r937]

put an index on action.actiondate, and then change this --

WHERE DATE(action.actiondate) BETWEEN '20121001' AND '20121031'

to this --

WHERE action.actiondate >= '2012-10-01' 
  AND action.actiondate  < '2012-11-01'

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[thompom]

Hi Rudy,
thanks for your reply unfortunately not much change
As a test, I changed the CASE to WHERE and the runtime is 1 sec

      LEFT JOIN 
        (SELECT 
          COUNT(1) AS appt, response.actionid
        FROM
          response 
          WHERE UCASE(response.reasonid) LIKE '%APPOINTMENT%' 
        GROUP BY response.actionid) AS resp 
        ON resp.actionid = action.actionid

this is not the solution as need a few CASE statements in the nested query

          COUNT(
            CASE
              WHEN UCASE(response.reasonid) LIKE '%APPOINTMENT%' 
              THEN 1 
            END
          ) AS appt,
          COUNT(
            CASE
              WHEN UCASE(response.reasonid) LIKE '%TESTDRIVE%' 
              THEN 1 
            END
          ) AS testdrive,
          COUNT(
            CASE
              WHEN UCASE(response.reasonid) LIKE '%FIRST CONTACT%' 
              AND response.typeid = '8' 
              THEN 1 
            END
          ) AS fcvisit,
          COUNT(
            CASE
              WHEN UCASE(response.reasonid) LIKE '%FIRST CONTACT%' 
              AND response.typeid = '1' 
              THEN 1 
            END
          ) AS fctel,

 

[IanWaterman]

Would filtering your data on those cases of interest help

WHERE (UCASE(response.reasonid) LIKE '%APPOINTMENT%'
or
UCASE(response.reasonid) LIKE '%TESTDRIVE%'
or
UCASE(response.reasonid) LIKE '%FIRST CONTACT%'
)

Ian