Thanks Dan and Vladimir. A combination of the two approaches is required. I'll detail it here for future reference:
Using the sub query against the FTS table as suggested: SELECT * FROM main WHERE rowid IN (SELECT rowid FROM main WHERE main MATCH 'data') ORDER BY udate Still causes the USE TEMP B-TREE FOR ORDER BY. As Dan pointed out, the FTS "index" isn't a traditional SQL Index. However, by using the suggested content table to populate the FTS table, I was able to get the desired result, with just a few more steps (and disk space) required: CREATE TABLE IF NOT EXISTS content1 (id INTEGER PRIMARY KEY, udate, msg) CREATE VIRTUAL TABLE IF NOT EXISTS main USING fts4(content="content1",id,udate,msg) CREATE TRIGGER IF NOT EXISTS content_trig1 AFTER INSERT ON content1 BEGIN INSERT INTO main(docid,udate,msg) VALUES(new.rowid, new.udate,new.msg); END CREATE INDEX IF NOT EXISTS content1_index on content1 (udate) To query with ORDER BY, I had to also add the "INDEXED BY" to get the Query Planner to use the Index, and not a temp B-tree. SELECT * FROM content1 INDEXED BY content1_index WHERE rowid IN (SELECT rowid FROM main WHERE main MATCH 'data to match') ORDER BY odate" And I now have fast (and sorted) FTS queries again, with no temp B-tree! Thanks. Dave On Thu, Aug 20, 2015 at 11:23 AM, Vladimir Vissoultchev <wqweto at gmail.com> wrote: > Try to rewrite the query like this > > SELECT * FROM main > WHERE rowid IN (SELECT rowid FROM main WHERE main MATCH 'data') > ORDER BY udate > > cheers, > </wqw> > > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org > [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of David > Waters > Sent: Thursday, August 20, 2015 4:47 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Is it possible to have query planner use FTS Index > for > ORDER BY > > Sorry. I should have specified that that 'udate' is one of the indexed > columns of the FTS4 table main. > > The goal is to do a FTS query and be able to ensure the results are ordered > the same each time (via the ORDER BY). It seemed at first to me that the > FTS index contains what is needed for the ORDER BY, but that is likely > incorrect. > > Thanks. > > DW > > On Thursday, August 20, 2015, Dan Kennedy <danielk1977 at gmail.com> wrote: > > > On 08/20/2015 12:38 PM, David Waters wrote: > > > >> I have a large FTS4 table (around 200 million rows and growing). A > >> simple query (SELECT * FROM main WHERE main MATCH 'data') returns in > >> less than a second. However, if an ORDER BY is added (SELECT * FROM > >> main WHERE main MATCH 'data' ORDER BY udate) it will never return > >> (after 20 mins, I canceled the query). > >> > >> EXPLAIN QUERY PLAN on the ORDER BY statement shows 'USE TEMP B-TREE > >> FOR ORDER BY'. Shouldn't it attempt to use the available FTS Index > >> for ORDER BY? > >> > > > > I don't see how it could. The FTS index is not a list of rows sorted > > by udate. > > > > If not, Is there another method (or work around) to get the data back > >> in order? > >> > > > > You could use an external content FTS index. Then put a regular index > > on the udate column of your external content table and query it > > directly for non-fulltext queries. > > > > https://www.sqlite.org/fts3.html#section_6_2_2 > > > > Dan. > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > -- > Dave > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Dave
