How to optimize this SQL select query?
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
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
)
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?
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` );
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)
精彩评论