SQLite Forum

Min/max and index usage?
Login
I should perhaps add, in case anyone looks at this and thinks "Well why don't the query planner just add the MIN+MAX case then?"

It's a small bit of extra code that must also be maintained, plus a small bit of extra bloat in a very lean query planner - both of these would be quite acceptable if the gain is significant, or at least useful.

As it stands, if you add all the aggregate queries together which use some combination of MIN() and MAX(), you will find that[1]:

- MIN() alone is queried about 20% of the time,
- MAX() alone about 79% of the time, and
- MIN() + MAX() less than 1% of the time.

Add to that the simple optimization work-around of doing 2 queries, and the "need" diminishes rapidly.



[1] Yes, these statistics were completely made up on the spot, but I feel it's fairly accurate in the broad sense, though I would love to be corrected with actual stats, or simply an enlightened argument.