On 14 Nov 2014, at 2:32pm, RP McMurphy <rpm0...@yahoo.com> wrote: > After we run analyze and then > let the process run for a while the DB > contents change - and it can change quite > considerably depending > upon what is > happening > > I suspect that the analyze data gets stale, but > I don't know how to track such things in > sqlite. Anyhow we can't keep running > analyze every few minutes because it takes a > long time to run with our DB > and it appears > to block all other actions until it is done.
Okay. ANALYZE isn't used for specific information about every piece of data. It's used to evaluate the 'chunkiness' of each column and index. For instance one column may only ever have two values in it -- "yes" or "no" -- whereas another column may have a different value in it for each row. So unless the character of your data changes, if you run ANALYZE once when you have realistic data, you shouldn't need to run it again even if your tables grow to 100 times their original size. You don't need to run ANALYZE under normal operation. Perhaps once a year in a yearly maintenance run or something. Or if you create a new index so SQLite can see how useful that index will be for certain operations. As others have shown you using EXPLAIN QUERY PLAN, SQLite is itself capable of doing the expansion you describe to get an efficient query. I would definitely not recommend you try beating the SQLite internals which have been developed and improved for years. It's going to be more effective to see if you can spot some point at which your query starts executing slowly. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
