Bharath Booshan L wrote:
There is also a discussion of the REGEXP Function on that page and why your
app threw an error when you tried to invoke a REGEXP filter in your query.
I have tested that in sqlite3 command-line tool(v3.4.0), but no yield.
REGEXP syntax is supported by SQLite, but you have to supply an suitable
regexp function for SQLite to call when needed. That function is not
included in the standard distribution.
I have tried the following example to test the usage of index
Create table MyTable(FilePath TEXT PRIMARY KEY NULL);
Insert some appropriate values
SELECT MovieURL FROM MyTable WHERE MovieURL = 'Some File Path';
Initially, I thought the Primary key in the table is automatically indexed,
but that doesn't seem to be the case;
Yes, the primary key field is automatically indexed.
So I created an index on FilePath
Create index indexFilePath on MyTable(FilePath);
You are just duplicating the automatic primary key index.
sqlite> explain query plan
...> SELECT MovieURL FROM MyTable WHERE FilePath =
'/Volumes/Users/Shared/';
0|0|TABLE MyTable WITH INDEX sqlite_autoindex_MyTable_1
Now what is this sqlite_autoindex_MyTable_1? Is it the index of implicit
rowid of MyTable?
It is the automatically generated index on the primary key.
In simple way, shouldn't the above query use the index indexFilePath as it
is FilePath is being compred with a constant?
SQLite finds the automatic primary key index first and your duplicate
index does not provide a better means of accessing the data so it is
never used.
Similarly,
sqlite> explain query plan
...> SELECT MovieURL FROM MyTable WHERE MovieURL =
'/Volumes/Users/Shared/%';
0|0|TABLE MyTable WITH INDEX sqlite_autoindex_MyTable_1
Again, the wild character is at the end, and therefore it should have used
index indexFilePath. Isn't it?
This is an equality comparison,not a LIKE call so the % is a normal
character, not a wildcard. In either case, the automatic primary keyu
index is being used for the same reason as the previous test case.
And one more
sqlite> explain query plan
...> SELECT MovieURL FROM MyTable WHERE FilePath GLOB
'/Volumes/Users/Shared/%';
0|0|TABLE MyTable WITH INDEX sqlite_autoindex_MyTable_1
GLOB uses * and ? as wildcard characters, not %.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
