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 > ( > ID INTEGER, > 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] -----------------------------------------------------------------------------
