On Thu, 14 Apr 2016 13:44:45 +0200 R Smith <rsmith at rsweb.co.za> wrote:
> > > On 2016/04/14 10:46 AM, John Found wrote: > > Playing with optimization of my queries, I stuck on very strange (for me) > > behaviour of > > sqlite. > > > > The query is pretty complex, but for the experiment I simplified it to: > > > > select a, b from t order by a desc, b desc; > > > > The result of "explain query plan" is as expected: > > > > SCAN TABLE T > > USE TEMP B-TREE FOR ORDER BY > > > > Then I created index: > > > > create index i on T(a desc, b desc); > > > > After creating index, the explain query plan looks like: > > > > SCAN TABLE T USING COVERING INDEX i > > > > The query speed raises significantly and I leaved it this way. The original > > more complex query also started to use the index and increased its speed > > significantly. > > > > But after some time working in wild (executing the more complex original > > query), the performance decreased again and despite of existing the index, > > the result of the "explain query plan" is again: > > > > SCAN TABLE T > > USE TEMP B-TREE FOR ORDER BY > > > > After dropping the index and recreating, everything repeats from the > > beginning - short period of high performance queries and then not using > > index again. > > > > The database is not changed, at least for the tables used in the query. > > Only select statements are executed, by several connections. > > > > What can be the reason for such strange behaviour? > > > > Are you running periodic ANALYZE perhaps? > Yes, but from time to time, manually. How often should I run it? I mean there should be some reason for SQLite to ignore the existing index on not changed database. -- http://fresh.flatassembler.net http://asm32.info John Found <johnfound at asm32.info>
