>> Is the "explain query plan" lying when it says it will use the index?
>> Can I just slap a trailing "*" on the end of each parameter for it
>> really use the index?
>>
> I'm not sure what you mean by "lying". When I try EXPLAIN QUERY PLAN
> on a GLOB 'b' example, it reports that it will not us
On Thu, Apr 15, 2010 at 9:51 AM, Mike Goins wrote:
> > > 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
> > >
> > >
>
>
> > SQLite
> > 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
> >
> >
> SQLite will not try to optimize a GLOB (to use an index) if it doesn't
> co
On Mon, Apr 12, 2010 at 9:14 AM, Igor Tandetnik wrote:
> Mike Goins wrote:
> > 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
> >
> >
>
Mike Goins wrote:
> 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 us
> 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
Right, Igor.
We can eliminate the middle-column issue :
... where basename GLOB 'a'// no index
... where basename GLOB 'a*' // index used
Regards
Tim Romano
On Sun, Apr 11, 2010 at 8:43 AM, Igor Tandetnik wrote:
> Tim Romano wrote:
> > I believe the behavior is expected and believe (s
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, bu
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, exten
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
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.
11 matches
Mail list logo
