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.
