INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

speed up query

speed up query

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

CODE --> mysql

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 

RE: speed up query

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

CODE

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

CODE

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

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

RE: speed up query

(OP)
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

CODE --> mysql

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

CODE --> mysql

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, 

RE: speed up query

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close