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-boun...@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
