CODEWITHSUNDEEP
mysqlsqlquery-optimization
Drew Cutchins
Drew Cutchins

Reputation: 16

How should I index a query with both a BETWEEN and GROUP BY?

I am creating a table to store user visits to a site and would like to properly index the table before continuing with its use.

I have already removed usages of {HOUR | DAY | MONTH}(visit_time), with generated (stored) columns so that I am not using functions in my GROUP BY clause.

SELECT COUNT(id)
     , hour 
  FROM Visits
 WHERE site_id = 9 
   AND start_time BETWEEN '2019-07-23 21:29:49' AND '2019-07-29 21:03:33'
GROUP BY hour, day, month, year 
ORDER BY hour;

I do not have much experience with optimizing mysql queries and will be using this query often in my application. As of now I have a composite index on site_id,hour,day,month,year but I feel as if start_time should be included in my index. Another concern of mine is slowing down the insert performance of this table as it will be inserted into frequently.

Upvotes: 0

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269943

For this query:

SELECT COUNT(*), hour 
FROM Visits
 WHERE site_id = 9 AND
       start_time BETWEEN '2019-07-23 21:29:49' AND '2019-07-29 21:03:33'
GROUP BY hour, day, month, year 
ORDER BY hour;

The optimal index is likely to be on (site_id, start_time, hour, day, month, year). You should probably include day, month, year in the SELECT.

This is a covering index for the query. That means that all columns in the query are in the index, so only the index needs to be scanned.

However, only the first two columns are really used. The first because of the equality condition and the second for the BETWEEN. The index will not be used for the aggregation, so that will require an additional sort.

Upvotes: 1

Related Questions