On 14 Apr 2016, at 1:01pm, John Found <johnfound at asm32.info> wrote:
> R Smith <rsmith at rsweb.co.za> wrote: > >> Are you running periodic ANALYZE perhaps? > > Yes, but from time to time, manually. How often should I run it? Once. > I mean there should be some reason for SQLite to ignore the existing index on > not changed database. I also wondered whether ANALYZE had anything to do with your problem but I don't understand why it should. ANALYZE looks at your data and, among other things, works out how 'chunky' it is. For instance, you might have a column for 'surname' in your table and every member might have a different surname. But you might have a another column for 'sex' and only have two sexes in it. This information helps the search optimizer figure out whether it should use existing indexes, make up its own, etc.. Unless the nature of your data changes there's no reason to run ANALYZE. So if you have already run it, perhaps after you have all your existing data loaded, or perhaps when you have a year's worth of data in your database, the 'chunkiness' of your data isn't going to change, and the relative lengths of your tables isn't going to change much, the same search strategies will be optimal, and there's no point in running ANALYZE again. However it should never do any harm. If you're running ANALYZE and it makes things slow down, something is wrong with your database. First, run "PRAGMA integrity_check". If that doesn't spot anything the next time you recreate your index run ANALYZE immediately after that. See if that changes your pattern of fast-slow-fast-slow. Simon.
