reate index idx_tb_file_deleted on tb_file(deleted);
create index fullpath_idx on tb_file (basename, extension, path);
Thanks
> On Fri, Apr 16, 2010 at 8:17 AM, Mike Goins
> wrote:
>
>> Sorry, this may look a bit familiar.
>>
>> Table structure:
>> CREATE TABLE tb_file (tb
On Fri, Apr 16, 2010 at 8:17 AM, Mike Goins
wrote:
> Query:
> SELECT tb_file_key, basename, extension, path FROM tb_file WHERE
> basename GLOB 'a*' AND extension GLOB 'b*' AND path GLOB 'c*' AND
> deleted = 0 ORDER BY tb_file_key DESC;
Apolo
>> 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
Sorry, this may look a bit familiar.
Table structure:
CREATE TABLE tb_file (tb_file_key INTEGER NOT NULL PRIMARY KEY
AUTOINCREMENT , basename TEXT, extension TEXT, path TEXT, deleted
INTEGER default 0 );
Index:
CREATE INDEX fullpath_idx on tb_file (basename, extension, path, deleted);
Example in
> > 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
> 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
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.
I use BEGIN TRANSACTION and COMMIT as prepared statements in a C
application. I originally was not resetting these types of
non-binding prepared statements that only return SQLITE_DONE.
http://www.sqlite.org/c3ref/step.html states:
SQLITE_DONE means that the statement has finished executing
succ
8 matches
Mail list logo
