On 6 Jan 2011, at 12:10pm, Black, Michael (IS) wrote: > Hmmm...do I hear a new pragma that would either remember such indexes,
Just for context, we're talking about SQLite keeping the indexes it makes up temporarily to speed up a search. I expect that the devs will need to talk to one-another about this before deciding if it's practical. Two ways occur to me to do it: A) Hold the index either in the journal file or in the database file, with some sort of timestamp. Any indexes which haven't been used for say, an hour, can be thrown away. All indexes will be thrown away when the journal file is deleted (i.e. all connections closed). B) Hold the index in memory, as part of the storage used for the database connection. This means that the file won't get bigger unexpectedly. Indexes might or might not be thrown away after a certain time has expired. However, there would have to be a mechanism for throwing the index away (or marking it for update) if another connection (from either the same or a different thread/process/application/computer) updates the table. In terms of PRAGMAs, systems that do this often have some way of reporting which indexes are currently being held. By consulting this at the same time as an application is being run, it's possible to create a log of which ones were created and destroyed at what times. With this log, a developer can begin a project making no indexes at all, then during testing just create whatever indexes the SQL engine decided would be useful. This is a very big advantage for users who don't really understand how SQL works. And it's the sort of thing professional programmers hate, because it cheapens the effort they put into learning database theory and design. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
