On 10/12/2011 04:36 PM, Fabian wrote:
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.
Can we see the output of EXPLAIN for this query?
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
Are you using 3.7.7 or newer? And specifying the order=DESC
option when creating the FTS4 table? If not, it might be a big
help in this case.
http://www.sqlite.org/fts3.html#fts4order
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
