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?
