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? If not, Is there another method (or work around) to get the data back in order? Thank you. Dave