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
