On 06/01/11 12:44, Simon Slavin wrote: > > 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 > >
The last paragraph there has the key to my particular problem. I needed to write the query paying attention to the way SQLite runs a query as documented. The solution is to write the query so that I select only one record in each of the left joins, thus avoiding the need for an index. I tried three ways, all of which run as fast as each other, and faster than PostgreSQL. One of them allows me to keep my inner views by wrapping them in a (select * from view where ) clause. Now I've bothered to look at the PostgreSQL 'Explain analyze' output this is exactly what the PostgreSQL query planner does - applies the final 'where' clause to each of the joined tables thus avoiding the need for on-the-fly indexes and covering up for my poorly written query in the process. Pete -- Peter Hardman 'For every complex problem there is a solution that is simple, straightforward - and wrong' _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
