[sqlite] Is it possible to have query planner use FTS Index for ORDER BY

Actually, doubt it's possible to use `content1_index` for ordering in your case once `content` rows are filtered by the IN operator. Most probably the index is just ignored here. Do you get any measurable performance improvement with vs w/o it? cheers, -Original Message- From:

[sqlite] Is it possible to have query planner use FTS Index for ORDER BY

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, -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of

[sqlite] Is it possible to have query planner use FTS Index for ORDER BY

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

[sqlite] Is it possible to have query planner use FTS Index for ORDER BY

I did. My last post was based off testing it live - it works beautifully. Lighting quick and sorted without a temp B-TREE: EXPLAIN QUERY PLAN is now: SCAN TABLE content1 USING INDEX content1_index (~10 rows) EXECUTE LIST SUBQUERY 1 SCAN TABLE main VIRTUAL TABLE INDEX 6: (~0 rows) Dave On

[sqlite] Is it possible to have query planner use FTS Index for ORDER BY

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

[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

[sqlite] Is it possible to have query planner use FTS Index for ORDER BY

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