I have two tables, one normal table (table1), and one FTS4 table (table2). The ROWID's between the tables are in sync. I often run queries on table1, but every row returned must also include a value from table2 (data2), so I create a query like this:
SELECT table1.data1, table2.data2 FROM table1 JOIN table2 ON table1.rowid=table2.rowid WHERE table1.data1 = 10 ORDER BY table1.rowid DESC LIMIT 250 OFFSET 0 This query returns the results as expected, and performs well. But as soon as I raise the OFFSET to a large value (for pagination) the performance drops drastically. After a long search through the documentation, I found out the reason: the SELECT on table2.data2 causes FTS4 to load the entire column from disk, and doesn't take into account the LIMIT/OFFSET clauses. I have a related query, where I do a MATCH on table2.data2, SELECT table1.data1, table2.data2 FROM table2 JOIN table1 ON table2.rowid=table1.rowid WHERE table2.data2 MATCH 'value' AND table1.data1 = 10 ORDER BY table1.rowid DESC LIMIT 250 OFFSET 0 Here the problem is even worse. When MATCH 'value' returns a lot of results, the query is very slow (even with OFFSET 0) because it ignores the fact it only has to return the first 250 results. How would I optimize the above queries, to take advantage of the LIMIT/OFFSET values, making them suitable for fast pagination? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
