On Thu, Apr 15, 2010 at 9:51 AM, Mike Goins <mike.go...@adtecservices.net>wrote:
> > > sqlite> explain query plan SELECT tb_file_key, basename, extension, > > > path FROM tb_file WHERE basename GLOB 'a' AND extension GLOB 'b' AND > > > path GLOB '*'; > > > 0|0|TABLE tb_file WITH INDEX fullpath_idx > > > > > > > > > > SQLite will not try to optimize a GLOB (to use an index) if it doesn't > > contain wild cards -- although it probably should. > > This optimization has been added to our list for a possible future > > enhancement. > > > > As a workaround, as has been noted, you could simply use the foo = 'b' > > instead of foo GLOB 'b'. > > > Is the "explain query plan" lying when it says it will use the index? > Can I just slap a trailing "*" on the end of each parameter for it > really use the index? > > Yep, I know I can modify the query, but that requires more prepared > statements, and each I'm adding extends the connect/prepare time for > my embedded system. How I wish the SSE was still around. > > I'm not sure what you mean by "lying". When I try EXPLAIN QUERY PLAN on a GLOB 'b' example, it reports that it will not use an index. Output of the three cases below: C:\work\sqlite\misc\glob_index>type test.sql DROP TABLE IF EXISTS t1; CREATE TABLE t1 (word TEXT); CREATE INDEX t1_word ON t1(word); EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE word GLOB 'b'; EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE word GLOB 'b*'; EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE word = 'b'; C:\work\sqlite\misc\glob_index>sqlite3 < test.sql 0|0|TABLE t1 0|0|TABLE t1 WITH INDEX t1_word 0|0|TABLE t1 WITH INDEX t1_word You could add a "*" to the end of each parameter if you like, but note that GLOB 'b' and GLOB 'b*' are different conditions, and will only be the same if your data set doesn't contain more than one entry beginning with 'b'. I suppose you could also try something like: SELECT * FROM t1 WHERE word GLOB 'b*' AND word GLOB 'b' Where the first GLOB uses the parameter with '*' appended, and the second GLOB uses the actual parameter. HTH. -Shane _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
