Mike Goins wrote:
> 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
I don't know why "basename GLOB 'a' " doesn't get optimized. There is no reason
not to, and the documentation suggests that it should. Could be a bug.
Note that "basename GLOB 'a' " is equivalent to "basename='a' ". Can you
rewrite your statements replacing GLOBs with equality tests where possible?
Then the index is indeed used.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
