[sqlite] Optimization corner case with IS?
I noticed this, IS is not treated like = for optimization: SQLITE> CREATE TABLE t(f INTEGER); SQLITE> CREATE INDEX t_f ON t(f) WHERE f IS NOT NULL; SQLITE> EXPLAIN QUERY PLAN SELECT * FROM t WHERE f = 1; QUERY PLAN `--SEARCH TABLE t USING COVERING INDEX t_f (f=?) SQLITE> EXPLAIN QUERY PLAN SELECT * FROM t WHERE f IS 1; QUERY PLAN `--SCAN TABLE t SQLITE> EXPLAIN QUERY PLAN SELECT * FROM t WHERE f IS 1 AND f IS NOT NULL; QUERY PLAN `--SEARCH TABLE t USING COVERING INDEX t_f (f=?) Wout. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug report for rtree.c
If SQLITE_DEBUG is not defined, rtree.c found at https://www.sqlite.org/src/dir?ci=edb095a9a679c8c7=ext/rtree fails to compile. This is because bCorrupt is declared (at line 132) only if SQLITE_DEBUG is defined, but is referenced unconditionally at line 980. Line 980 currently is assert( pRtree->nNodeRef==0 || pRtree->bCorrupt ); and it needs to be replaced by #ifdef SQLITE_DEBUG assert( pRtree->nNodeRef==0 || pRtree->bCorrupt ); #else assert( pRtree->nNodeRef==0); #endif Alternatively it may be better to declare and if appropriate set bCorrupt unconditionally, since this avoids a possible assertion error on releasing the Rtree if it is in fact corrupted and nNodeRef is nonzero. Richard Parkins http://www.zen224037.zen.co.uk https://github.com/rparkins999/sqliteman ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to use ORDER BY on FTS5 table ?
Have a fts5 table with 2 indexed columns. Where the idea is to match by one col and sort using the other one. Something like : "select id from fts where col1 match '50' order by price " This is slow. 0.07 seconds. Removing the order by clause - 0.001 seconds. How do I fix this ? I have a feeling I am using this fts table in an incorrect way. One way is to run 2 queries. First on the fts table, to return ids. Second on the regular table with the order by clause. " select * from normaltable where id in (Ids) order by price " . This approach is fast. But the id list could be large sometimes. Any other way ? Thanks PS: This is my second attempt at mailing lists. Not sure if this one will go through. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
