Jordy Jordy - 1 month ago 5
MySQL Question

Mysql query date between in large table

my query is running about 10 seconds and that's unacceptable.

I am looking for a way to improve this speed but i'm out of options.
I have to find the records between now and 30 days back in a table over 12 million rows.

The following query:

SELECT DATE(DATE) AS FDATE,
SUM(VIEW_COUNT) AS COUNT,
COUNT(IP_ADDRESS) AS CLIENTS
FROM VIEWS
WHERE USERID = 'test'
AND DATE BETWEEN ADDDATE(CURDATE(), INTERVAL -30 DAY) AND CURDATE()
GROUP BY FDATE DESC


I also tried, but the same effect:

SELECT DATE(DATE) AS FDATE,
SUM(VIEW_COUNT) AS COUNT,
COUNT(IP_ADDRESS) AS CLIENTS
FROM VIEWS
WHERE USERID = 'test'
AND DATE >= (DATE(NOW() - INTERVAL 30 DAY) + INTERVAL 0 SECOND)
GROUP BY FDATE DESC

Answer

You could try using MySQL Indexes for a better performance in decreasing the query execution time.

http://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html

Comments