0
votes
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

You are confused. Stop being confused.If the query yields the correct result: it might be correct. Performance is not an issue. Correctness is.wildplasser
Why do you have and time(created_at) > TIME('00:00:00')? Why are you excluding records that were created exactly at midnight on any date?Barmar
I believe your composite index should allow the query to be done without scanning. Maybe a better one would be (student_id, class_id, id)Barmar