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%'.
Regards
Tim Romano
On Fri, Apr 9, 2010 at 5:03 PM, Mike Goins <mike.go...@adtecservices.net>wrote:
> First thanks to all that have helped on previous occasions.
>
> I'm a little confused about some results using an index and GLOB. I'm
> trying to optimize some queries to ensure they use an index after
> reviewing the LIKE Optimization section at the sqlite website.
>
> Using the latest binary, sqlite3-3.6.23.1.bin.gz
>
> sqlite> CREATE TABLE tb_file (tb_file_key INTEGER NOT NULL PRIMARY KEY
> AUTOINCREMENT , basename TEXT, extension TEXT, path TEXT, deleted
> INTEGER default 0 );
> sqlite> CREATE INDEX fullpath_idx on tb_file (basename, extension, path);
> sqlite> INSERT INTO tb_file (basename, extension, path) VALUES ('a', 'b',
> 'c');
> sqlite> select * from tb_file;
> tb_f basename exte path dele
> ---- ------------- ---- ---- ----
> 1 a b c 0
>
> 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 'c';
> orde from deta
> ---- ------------- ----
> 0 0 TABLE tb_file WITH INDEX fullpath_idx
> 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 'c';
> orde from deta
> ---- ------------- ----
> 0 0 TABLE tb_file USING PRIMARY KEY ORDER BY
>
>
> The first select uses the index since the the glob character is picked
> up, while the second does not. Is this expected? Does it matter?
>
> Thanks
> _______________________________________________
> 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
