How to optimize this SQL select query?

Question

I am able to complete this query but it takes 25 seconds. That's too long! How can I optimize this query?

SELECT COUNT(DISTINCT u1.User_ID ) AS total
FROM UserClicks u1
INNER JOIN (SELECT DISTINCT User_ID 
              FROM UserClicks 
             WHERE (Date BETWEEN DATE_SUB(:startDate, INTERVAL 1 MONTH) AND :startDate)) u2
            ON u1.User_ID = u2.User_ID
WHERE (u1.Date BETWEEN :startDate AND :endDate)

This is being used on a MySQL database

Answer 1

Have you tried to move the DATE_SUB(:startDate, INTERVAL 1 MONTH) outside of the statement into a variable? Do you have an index by UserClicks.Date?

Answer 2

Why not just use one select statement instead of running a nested pair of selects. Right now you're essentially running two queries. Try this:

SELECT COUNT(DISTINCT UserClicks.User_ID) AS total
FROM UserClicks
WHERE (UserClicks.Date BETWEEN :startDate AND :endDate)
AND (UserClicks.Date BETWEEN DATE_SUB(:startDate, INTERVAL 1 MONTH) AND :startDate)

Might help if you add an index on the date column too:

ALTER TABLE  `UserClicks` ADD INDEX (  `Date` );

Answer 3

SELECT  COUNT(*) AS total
FROM    (
        SELECT  DISTINCT User_ID 
        FROM    UserClicks 
        WHERE   Date BETWEEN DATE_SUB(:startDate, INTERVAL 1 MONTH) AND :startDate
        ) u1
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    UserClicks u2
        WHERE   u2.User_ID = u1.User_ID
                AND u2.Date BETWEEN :startDate AND :endDate
        )

Create a composite index on (User_ID, Date):

CREATE INDEX ix_userclicks_user_date ON UserClicks (User_ID, Date)

If you have few users but lots of clicks, and have a table Users, you may use the Users table instead of DISTINCT:

SELECT  COUNT(*)
FROM    Users u
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    UserClicks uc1
        WHERE   uc1.UserId = u.Id
                AND uc1.Date BETWEEN DATE_SUB(:startDate, INTERVAL 1 MONTH) AND :startDate
        )
        AND EXISTS
        (
        SELECT  NULL
        FROM    UserClicks uc2
        WHERE   uc2.UserId = u.Id
                AND u2.Date BETWEEN :startDate AND :endDate
        )

Answer 4

MySQL tends to ignore indexes when processing subqueries, so it has to process every row. How about a self-join instead? This is just off the top of my head so it may not be quite correct, but it should at least point you in the right direction.

SELECT COUNT(DISTINCT u1.User_ID) AS total
FROM   UserClicks AS u1
JOIN   UserClicks AS u2 USING (User_ID)
WHERE  u1.Date BETWEEN :startDate AND :endDate
AND    u2.Date BETWEEN DATE_SUB(:startDate, INTERVAL 1 MONTH) AND :startDate)