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;

migrated from stackoverflow.com Oct 12 '16 at 6:52

This question came from our site for professional and enthusiast programmers.

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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