> > > > How would I optimize the above queries, to take advantage of the > > LIMIT/OFFSET values, making them suitable for fast pagination? > > Are you sure the issue is with the fts table? Or is it the order by desc? > > If I omit the FTS table in the query, like so:
SELECT table1.data1 FROM table1 WHERE table1.data1 = 10 ORDER BY table1.rowid DESC LIMIT 250 OFFSET 0 The query always executes fast, independant of how large OFFSET is. The only difference is that I don't include 'table2.data2' in the output, so that leads me to think that the issue is related to FTS, or is there another possibility? And in the FTS docs I read: "Because of the way SQLite's virtual table interface works, retrieving the value of the "title" column requires loading the entire row from disk (including the "content" field, which may be quite large). This means that if the users query matches several thousand documents, many megabytes of "title" and "content" data may be loaded from disk into memory even though they will never be used for any purpose." Which seems to confirm that the reason the query is so slow, is because I include the value of 'table2.data2' in the output? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
