Does sqlite support reading certain number of rows for each query?

Question

My Android application has an activity to present data from SQLite database. The db table might contain huge number of rows. For performance reasons, I want to load 20 rows from db at a time, and when user scrolls down listview to the end, read next 20 rows.

So I want to use SQL statement like this:

select * from mytable where id > N and count = 20;

I just wonder if SQLite supports this kind of "count=20" feature to read at maximum 20 rows for the query. If it is supported, what is exact syntax?

Answer 1

Yes, it does. It is called LIMIT:

SELECT *
FROM mytable
WHERE id > N
LIMIT 20

You can also use optional OFFSET clause to start at certain row:

SELECT *
FROM mytable
WHERE id > N
LIMIT 20
OFFSET 100

Answer 2

You can use LIMIT and OFFSET to specify a set of rows to return.

However:

1. This is risky if other threads may be updating the database, particularly if your query will use ORDER BY.

2. Use tools like Traceview to really determine how long things take, and use that to determine the number of rows to fetch. 20 seems seriously annoying.

3. If your "table might contain huge number of rows", you should be focused on a search interface, not expecting people to browse linearly through some list that is long enough to warrant this sort of batching.