Right, Igor. We can eliminate the middle-column issue :
... where basename GLOB 'a' // no index ... where basename GLOB 'a*' // index used Regards Tim Romano On Sun, Apr 11, 2010 at 8:43 AM, Igor Tandetnik <itandet...@mvps.org> wrote: > Tim Romano wrote: > > I believe the behavior is expected and believe (subject to correction) > that > > the single composite index is placed on a concatenation of the three > column > > values (basename + extension + path). I don't know how the internals > of > > SQLite work never having explored the code, but in situations like yours, > > where you want to do substring queries on each of the three columns that > > comprise your "full path", I would normally create three separate indexes > > rather than a single composite index. I am guessing that when you look > for > > rows where column 'extension' starts with 'b' (column 'extension' is the > > middle column of the three-column composite index) you are running into a > > scenario analogous to .... where someColumn LIKE '%x%'. > > Try this: > > explain query plan SELECT tb_file_key, basename, extension, > path FROM tb_file WHERE basename = 'a' AND extension GLOB 'b*' AND > path = 'c'; > > This query does use the index, and is equivalent to the OP's second query. > How does your theory explain this case? > -- > Igor Tandetnik > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
