I have a query to bin some log lines on a pre-processed "timeslice" field (this is unique every 15 minutes, for example
"2018-04-11-15-3-3") There are approximately 6,000,000 rows in the database.
The performance seems slow and inconsistent; sometimes it will return a single timeslice and take 0.5 seconds, sometimes it will return thousands and take 0.0002 seconds.
SELECT count(*), time_slice FROM logs WHERE deployment_id = 123 and type="FOO" and received_at BETWEEN "2019-04-01 00:00:00" AND "2019-04-30 23:59:59" GROUP BY time_slice
I've created an index on each of those columns, which helps. It's still very inconsistent.
EXPLAIN SELECT tells me that it's using the
Alternatively, is there a better way to bin lines on a 15 minute interval?