> 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.
I wondering the same and wanted to make sure I was not missing something silly.
> 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.
I was trying to avoid using multiple queries, but there isn't any
reason I can't. Just means more string testing and more prepared
statements.
Went back to an older version we have in production and it uses the
index regardless if the asterisk is found or even if just asterisk:
SQLite version 3.5.1
Enter ".help" for instructions
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';
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';
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';
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 '*';
0|0|TABLE tb_file WITH INDEX fullpath_idx
The last one with the leading wildcard doesn't look like it should use
the index.
> 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
