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
