supermariobros wrote: > If I am using FTQ that looks like this > SELECT * FROM mail WHERE body MATCH 'sqlite' > can I add to it "WHERE rowid > 5 AND rwoid <10" > or it will significantly slow it down.
How much did it slow down when you tested it? Anyway, without index: sqlite> EXPLAIN QUERY PLAN SELECT * FROM t; 0|0|0|SCAN TABLE t VIRTUAL TABLE INDEX 0: with word search: sqlite> EXPLAIN QUERY PLAN SELECT * FROM t WHERE t MATCH 'x'; 0|0|0|SCAN TABLE t VIRTUAL TABLE INDEX 3: your query: sqlite> EXPLAIN QUERY PLAN SELECT * FROM t WHERE t MATCH 'x' AND rowid BETWEEN 5 AND 10; 0|0|0|SCAN TABLE t VIRTUAL TABLE INDEX 393219: So it uses some index (and the lowest two bits are still set, so it still uses the FTS index, but this is an implementation detail.) This might be different with a different SQLite version. > If so what would be the best approach for pagination The rowid cannot be used for pagination because you get the numbers of the original rows. You would have to use OFFSET/LIMIT, which is inefficient for large offsets. Regards, Clemens _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
