On 17 Mar 2011, at 9:00pm, Jeff Archer wrote: > On Wed, Mar 16, 2011 at 5:59 PM, Simon Slavin <[email protected]> wrote: >> You should almost never be creating indexes on the fly. Bear in mind that >> if SQLite finds a search that would be better with an index, it creates it >> itself and it is far better at working out the best index than you are. The >> only disadvantage is that it will recreate the index each time you do that >> SELECT. >> > > Yes. The code was only supposed to create 1 specific index but bug caused > more. > > Is there any way to know when SQLite has created a temporary index?
I believe that in versions of SQLite that do this, the EXPLAIN QUERY PLAN command will clearly indicate when a command would decide to create a temporary index. For more details see section 11 of http://www.sqlite.org/optoverview.html#autoindex and all of http://www.sqlite.org/eqp.html > It would be nice to have an option to allow SQLite to save these > temporary indexes when SQLite is being used in a desktop environment. > Probably by #define at compile time. I made a suggestion that a PRAGMA could be used to turn that function on and off. Now I think about it it would be better to use the PRAGMA to tell SQLite how much filespace it was allowed to use for its own indexes. You could then reset it to 0 to wipe them out. Of course, at the moment, temporary indexes never have to be updated as the data changes. Allowing them to be stored between statements would mean that SQLite would either have to keep them updated or that any change in data of the table(s) would delete them. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
