SQLite Forum

Help speeding up a query
Login
Thanks Jose.

I thought this might make a big difference.  If you look at the query plan for the original query it would basically have to do the following:

1)  Generate the list of "a" by doing a table scan of t0  
2)  Scan each row in t0 matching the above  
    a) for each row do a subquery to check if it is the max(idate)

The alternate query does the following:

1)  Generate the list of "a" by doing a table scan of t0
2)  For each "a" in the above list, find and output the last record in the index for that group (a single index seek, basically)

So the revised query is avoiding an extra scan of each group of "a" and a correlated subquery for each row in the group to see if it is the one with the max(idate).

Note that this would not work in most other SQL databases because most of them do not allow the select list to contain values that are not in the group by list and are not aggregates.