Speed up this query in mysql

Databases
1

Hello all

Ive been using this query to get history data for page views over the last 3 months and it works fine, however it is very slow currently running about 0.3 seconds for each query.

Any way to speed it up ?

SELECT  SUM(CASE WHEN DATEDIFF(CURDATE(), Time) 
                     		BETWEEN  0 AND 30 THEN 1 ELSE 0 END) D30,
       		SUM(CASE WHEN DATEDIFF(CURDATE(), Time) 
                     		BETWEEN 31 AND 60 THEN 1 ELSE 0 END) D60,
       		SUM(CASE WHEN DATEDIFF(CURDATE(), Time) 
                     		BETWEEN 61 AND 90 THEN 1 ELSE 0 END) D90
  	FROM page_stats_t
 	        WHERE Time BETWEEN CURDATE() - INTERVAL 90 DAY AND CURDATE()
  			AND Prop_ID = 'xxxxxxx'
2

please identify the indexes on the table

3

Hi R937

I have Time and Prop_ID as indexes

4

the index on Prop_ID should be a 2-column index on Prop_ID (first) and time (second)

5

Wow, that’s really speeded it up, thanks Rudy

Should I keep the seperate index for Time ?

6

only if you have a query that requires filtering by time across all props

7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.