Re: [sqlite] FTS pagination
Just to be clear. It basically means that after MATCH records are returned it iterates through ALL the rowids of the returned set and removes them from the set and orders them accordingly. -- View this message in context: http://sqlite.1065341.n5.nabble.com/FTS-pagination-tp78754p78849.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS pagination
supermariobros wrote: > Well, they all give exactly the same output. > > sqlite> EXPLAIN QUERY PLAN SELECT rowid FROM activity_text_content WHERE > activity_text_content MATCH 'x' ORDER BY rowid ASC LIMIT 100; > 0|0|0|SCAN TABLE activity_text_content VIRTUAL TABLE INDEX 4:ASC (~0 rows) > > sqlite> EXPLAIN QUERY PLAN SELECT rowid FROM activity_text_content WHERE > activity_text_content MATCH 'x' AND rowid>1000 ORDER BY rowid ASC LIMIT 10; > 0|0|0|SCAN TABLE activity_text_content VIRTUAL TABLE INDEX 4:ASC (~0 rows) > > sqlite> EXPLAIN QUERY PLAN SELECT rowid FROM activity_text_content WHERE > activity_text_content MATCH 'x' LIMIT 100; > 0|0|0|SCAN TABLE activity_text_content VIRTUAL TABLE INDEX 4: (~0 rows) > > It almost looks like EXPLAIN ignores the second part where rowid is compared > or sorted. It doesn't change the way in which the database accesses the table, which implies that the rowid comparison is not using any index, i.e., the FTS module first computes the results, and then the rows with small rowid values are thrown away. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS pagination
Well, they all give exactly the same output. sqlite> EXPLAIN QUERY PLAN SELECT rowid FROM activity_text_content WHERE activity_text_content MATCH 'x' ORDER BY rowid ASC LIMIT 100; 0|0|0|SCAN TABLE activity_text_content VIRTUAL TABLE INDEX 4:ASC (~0 rows) sqlite> EXPLAIN QUERY PLAN SELECT rowid FROM activity_text_content WHERE activity_text_content MATCH 'x' AND rowid>1000 ORDER BY rowid ASC LIMIT 10; 0|0|0|SCAN TABLE activity_text_content VIRTUAL TABLE INDEX 4:ASC (~0 rows) sqlite> EXPLAIN QUERY PLAN SELECT rowid FROM activity_text_content WHERE activity_text_content MATCH 'x' LIMIT 100; 0|0|0|SCAN TABLE activity_text_content VIRTUAL TABLE INDEX 4: (~0 rows) It almost looks like EXPLAIN ignores the second part where rowid is compared or sorted. how should I understand that? -- View this message in context: http://sqlite.1065341.n5.nabble.com/FTS-pagination-tp78754p78831.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS pagination
supermariobros wrote: > EXPLAIN QUERY PLAN SELECT rowid FROM activity_text_content WHERE > activity_text_content MATCH 'x' AND rowid>1000 ORDER BY rowid ASC LIMIT 10; > 0|0|0|SCAN TABLE activity_text_content VIRTUAL TABLE INDEX 4: (~0 rows) > > If I understand it correctly it uses indexes properly on FTS but I do not > know how the row id scanning and sorting is done. Compare the EXPLAIN QUERY PLAN output of this query and of the same query without the rowid comparison. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS pagination
Hi Thanks For quick response Of course you are right that I can not use row id in the way I used it above. I guess I wrote it quicker than I thought about it. However If I use original rowid and LIMIT it should be fine, knowing that the submited rowid is the rowid of the last element of the previous set. Like this: SELECT rowid FROM text_content WHERE text_content MATCH 'x' AND rowid >1000 ORDER BY rowid LIMIT 10; of course it only works when last rowid of the previous set is available so I can not just skip to the N-th page, wchich is fine in my case. I just have to know first and last element to go back and forward; This is what explain query shows: EXPLAIN QUERY PLAN SELECT rowid FROM activity_text_content WHERE activity_text_content MATCH 'x' AND rowid>1000 ORDER BY rowid ASC LIMIT 10; 0|0|0|SCAN TABLE activity_text_content VIRTUAL TABLE INDEX 4: (~0 rows) If I understand it correctly it uses indexes properly on FTS but I do not know how the row id scanning and sorting is done. If you have some time could you shed some light on this. Also when I compare time of the querry of the one with rowid > to the one with OFFSET query on 5000 records I do not see any difference in execution time. I read just like you wrote that OFFSET starts to be time consuming when number is very high but what is the high number case. I doubt I will ever deal with more than 1 records in this entire virtual table so should I even be concerned? Thanks -- View this message in context: http://sqlite.1065341.n5.nabble.com/FTS-pagination-tp78754p78772.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS pagination
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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS pagination
Quick question. 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. If so what would be the best approach for pagination, For example if I get 500 rows with the matching term and obviously I do not need them all right away. Should I just use the first option and maybe select only rowids and then go through them? Any suggestions? Thanks -- View this message in context: http://sqlite.1065341.n5.nabble.com/FTS-pagination-tp78754.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS pagination
Or maybe, if I am using android, it should be done at the cursor level? -- View this message in context: http://sqlite.1065341.n5.nabble.com/FTS-pagination-tp78754p78755.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
