4

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

1 Answer 1

2

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.)

2
  • is there perhaps any documentation you can link to back this up?
    – U Avalos
    Oct 12, 2016 at 15:05
  • 1
    These are implementation details.
    – CL.
    Oct 12, 2016 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.