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? -- http://fresh.flatassembler.net http://asm32.info John Found <johnfound at asm32.info>
