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
