SQLite – Min/Max Performance vs Order By Limit

sqlite

I know that combining min/max in one query is not performant. (See, for example, https://stackoverflow.com/questions/11515165/sqlite3-select-min-max-together-is-much-slower-than-select-them-separately). I also know that for SQL, the recommended approach is to use min/max. (See https://stackoverflow.com/questions/426731/min-max-vs-order-by-and-limit) However, how do these functions compare individually against ordering and limiting in sqlite?

And to be clear, these are the queries in question:

SELECT MIN(column)
FROM table;

SELECT column
FROM table
ORDER BY column
LIMIT 1;

Best Answer

When there is an index, both queries just take the first entry from the index.

When there is no index, MIN() does a single pass through the table, and ORDER BY sorts only as much as is needed for the LIMIT, which ends up the same effort. (Older versions of SQLite did sort everything.)

Related Question