Re: [sqlite] How to delete all indexes
On 17 Mar 2011, at 9:00pm, Jeff Archer wrote: > On Wed, Mar 16, 2011 at 5:59 PM, Simon Slavin 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to delete all indexes
On Wed, Mar 16, 2011 at 5:59 PM, Simon Slavin 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? 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. Doesn't seem like this would add much test burden since the creation of the temporary indexex must be tested now, likewise saving index is clearly tested now. So I see only new test required for the actual save that would be done in this new execution path. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to delete all indexes
On 16 Mar 2011, at 7:19pm, Jeff Archer wrote: > I have found that I can use this select to get the names of all existing > indexes: > > select name from sqlite_master where type = 'index' and sql is not null; You can also be specific about which table you want by filtering on the table name. > Then I can iterate the result and drop each index. > > > > This leaves me with 2 questions. > > 1. How stable are the column names and table contexts of the > sqlite_master table? They're dependable in this context. > 2. Is there a better way to accomplish this without resorting to > explicit query of the sqlite_master table? No I think you found the best way to do it with SQLite. 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. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to delete all indexes
Hello all and thank you in advance for your help. I have just realized that a buggy code has been secretly creating indexes. Thus I have need to make a generic function that can delete all indexes in a database. I have found that I can use this select to get the names of all existing indexes: select name from sqlite_master where type = 'index' and sql is not null; Then I can iterate the result and drop each index. This leaves me with 2 questions. 1. How stable are the column names and table contexts of the sqlite_master table? 2. Is there a better way to accomplish this without resorting to explicit query of the sqlite_master table? Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819-4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
