Well, the text for the index issued from the program is constructed programmatically, so I'm going to provide the actual text as seen in the Visual Studio debugger just before it is sent to Sqlite.
The actual text, as seen in VisualStudio's debugger, is: CREATE INDEX IDX_C$SimulinkBlockParameters$Property_COLLECTIONID ON C$SimulinkBlockParameters$Property (COLLID) The program code that creates this index eventually executes a sqlite3_prepare_v2 followed by a call to the step() function. Here's the text of the resulting index as reported by the Sqlite tool: CREATE INDEX IDX_C$SimulinkBlockParameters$Property_COLLECTIONID ON C$SimulinkBlockParameters$Property (COLLID) When I dropped and recreated it, I copied this text and pasted it back into the Execute SQL pane of the Sqlite tool. I just ran the experiment again and the average execution time of a SELECT statement that uses this index is about 18 or 19ms when run immediately after the database (and the index in question ) is generated and the records initially populated. After manually dropping and recreating the index (as described above) statement execution time dropped to about 1 ms. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Friday, October 25, 2013 8:14 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Trying to figure out how to circumvent sqlite3_win32_mbcs_to_utf8 On Fri, Oct 25, 2013 at 6:54 PM, Mike Clagett <mike.clag...@mathworks.com>wrote: > Yet the following oddity still remains: when we create the index > programmatically before adding the rows, it doesn't seem to have any > effect. Both interactively in the Firefox tool and programmatically > from the second phase of my application it takes about an average 18ms > to do a select -- the same amount of time without the index even being > there at all. Only when I manually drop and recreate the index from > within the Firefox tool does the index kick in and subsequently reduce > select time to about 1ms. > Do you have any thoughts on this? > My guess: The index created by the program and the index you create using Firefox are not the same. The difference might be subtle - perhaps a change in collating sequence or something - but it is enough to make the difference. Send us the exact text of the index (both the program version and the Firefox version) and its associated table and maybe also the query, then we can tell. Exact text: No changes please. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
