Thanks kjh for your valuable inputs,
> If you use US ASCII, there is a collation (COLLATE NOCASE)
> that could handle this for you.
I am using Unicode characters.
> 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.
> CREATE TABLE t1
> (
>IDINTEGER,
>PathName VARCHAR(255) COLLATE NOCASE, -- contains `dirname MovieFile`
>FileName VARCHAR(255) COLLATE NOCASE -- contains `basename MovieFile`
> ) ;
>
> In this case, COLLATE NOCASE makes both PathName and FileName filters case
> insensitive for the US ASCII character set.
No, The PathName and FileName has to be case sensitive.
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;
So I created an index on FilePath
Create index indexFilePath on MyTable(FilePath);
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?
I have gone through the sqlite arechive of Indexes and its usage, but it
made my knowledge on indexes even more complex.
In simple way, shouldn't the above query use the index indexFilePath as it
is FilePath is being compred with a constant?
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?
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
> I am not sure what your application is ultimately going to do.
My App indexes certain movie files and custom annotations related to that
movie file and stores that in a database. And at some point in time, App
queries for Movie files under specific search directory along with
constraints on these custom annotation. It is similar to that of a Spotlight
Search in Mac OS X.
> You'll have to decide for yourself -- a lot depends on the number of records
> in
> the table -- tens of records won't need an index, hundreds of records might
> work
> better with INDEXes, thousands probably will most likely run better with
> INDEXes).
More number of SELECT s are performed by the application and hence the right
columns has to be indexed for better performance.
Did I explained well?
Any inputs will be greatly appreciated
--
Bharath
---
Robosoft Technologies - Come home to Technology
Disclaimer: This email may contain confidential material. If you were not an
intended recipient, please notify the sender and delete all copies. Emails to
and from our network may be logged and monitored. This email and its
attachments are scanned for virus by our scanners and are believed to be safe.
However, no warranty is given that this email is free of malicious content or
virus.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
