select MAX(id) from studenthistory
where class_id = 1
and date(created_at) = '2021-11-05'
and time(created_at) > TIME('00:00:00')
group by student_id
composite indexes = ("class_id", "student_id", "created_at")
id is the primary key.
Is date(created_at) = '2021-11-05' and time(created_at) > TIME('00:00:00')
filter condition unnecessary for Max function since MAX(id) is already indexed on class_id and student_id?
The only reason I added that datetime filter is because this table will get huge over time. (historical data) And I wanted to reduce the number of rows the query has to search.
But for the case of Max function - I believe MAX would simply fetch the last value without checking the whole row, if it is indexed.
So can i safely remove the datetime filter and turn it into
select MAX(id) from studenthistory
where class_id = 1
group by student_id
And have the same performance? (or better since it does not need to filter further?)
Checking the query plan seems like the performance is similar, but the size of the table is rather small as of now..
First:
| -> Group aggregate: max(id) (cost=1466.30 rows=7254) (actual time=2.555..5.766 rows=3 loops=1)
-> Filter: ((cast(studenthistory.created_at as date) = '2021-11-05') and (cast(riderlocation.created_at as time(6)) > <cache>(cast('00:00:00' as time)))) (cost=740.90 rows=7254) (actual time=0.762..5.384 rows=5349 loops=1)
-> Index lookup on studenthistory using idx_studenthistory_class_id_931474 (zone_id=1) (cost=740.90 rows=7254) (actual time=0.029..3.589 rows=14638 loops=1)
|
1 row in set (0.00 sec)
Second:
| -> Group aggregate: max(riderlocation.id) (cost=1475.40 rows=7299) (actual time=0.545..5.271 rows=10 loops=1)
-> Index lookup on riderlocation using idx_riderlocati_zone_id_931474 (zone_id=1) (cost=745.50 rows=7299) (actual time=0.026..4.164 rows=14729 loops=1)
|
1 row in set (0.01 sec)
Many thanks in advance
and time(created_at) > TIME('00:00:00')
? Why are you excluding records that were created exactly at midnight on any date? – Barmar(student_id, class_id, id)
– Barmar