Re: [sqlite] Avoiding table scans with complex queries

2013-05-31 Thread Paul Vercellotti
stop working on the FTS lookups once the LIMIT conditions are met. Seems UNIONing repetitions of whole query for each OR section is offering the best option so far.  I'll keep trying things.  Cheers, Paul From: Clemens Ladisch To: sqlite-users@sqlite.org Sent

Re: [sqlite] Avoiding table scans with complex queries

2013-05-31 Thread Simon Slavin
On 31 May 2013, at 8:21am, Paul Vercellotti wrote: > If I change it to the following, by removing the first OR: > SELECT files.key FROM files, search_hierarchy, filename_fts, > file_comment_metadata > WHERE search_hierarchy.child = files.parent AND filename_fts.rowid = > files.key AND file_com

Re: [sqlite] Avoiding table scans with complex queries

2013-05-31 Thread Clemens Ladisch
Paul Vercellotti wrote: > I've got a very slow query that seems to be oh-so-close to running > quickly, and it seems to come down to handling of OR operators. > When the query has one OR criteria, it uses the correct indexes, but > when there are two, it does a table scan, which in this case brings

[sqlite] Avoiding table scans with complex queries

2013-05-31 Thread Paul Vercellotti
Hi there, I've got a very slow query that seems to be oh-so-close to running quickly, and it seems to come down to handling of OR operators.  When the query has one OR criteria, it uses the correct indexes, but when there are two, it does a table scan, which in this case brings the query time