Sqlite LIMIT / OFFSET query

Question

I have simple question with Sqlite. What is the difference between this:

Select * from Animals LIMIT 100 OFFSET 50

and

Select * from Animals LIMIT 100,50

Cleruchy

Answer 1

The two syntax forms are a little confusing because they reverse the numbers:

LIMIT <skip>, <count>

Is equivalent to:

LIMIT <count> OFFSET <skip>

It's compatible with the syntax from MySQL and PostgreSQL. MySQL supports both syntax forms, and its docs claim that the second syntax with OFFSET was meant to provide compatibility with PostgreSQL. PostgreSQL docs show it only supports the second syntax, and SQLite's docs show that it supports both, recommending the second syntax to avoid confusion.

By the way, using LIMIT without first using ORDER BY may not always give you the results you intend. In practice, SQLite will return the rows in some order, probably determined by how they're physically stored in the file. But this doesn't necessarily mean it's in the order you want. The only way to get a predictable order is to use ORDER BY explicitly.

Riff

This similar answer have a good solution with good performance if the order of the rows matters. https://mcmap.net/q/145307/-android-sqlite-query-getting-latest-10-records – Ganesa

Answer 2

The latter is an alternative syntax with one caveat:

If a comma is used instead of the OFFSET keyword, then the offset is the first number and the limit is the second number. This seeming contradition is intentional - it maximizes compatibility with legacy SQL database systems.

Redivivus

Answer 3

I made some tests and there is no difference in performance.

That is only for compatability with other sql languages.

Running time of both versions is same.

I made sqlite db with table1 with 100000 rows. I run next test

long timeLimitOffset = 0;
long timeLimitComma = 0;
for (int i = 0; i < 100000; i++)
{
   //first version
   timeLimitOffset += SqlDuraction("Select * from table1  order by col1 LIMIT " + (i + 1) + " OFFSET " + (1001 - i) + "");
   // second version
   timeLimitComma += SqlDuraction("Select * from table1 order by col1 LIMIT " + (1001 - i) + " , " + (i + 1) + "");
}

Times vary for 0.001 of a second

Tenfold

why would there be any difference in performance? they are same! – Homophonic