ANALYZE can help increase performance if the query planner is making bad guesses for you, especially if you've never run it, or have changed patterns in the cardinality of index columns. It's useful to do once and probably only to do again if you make a lot of changes to your data patterns.
VACUUM might help if it increases the amount of effective block cache available to you (i.e. your DB is 50gb but you only have 2gb of data) but generally I wouldn't expect it to unless you're on a fit/not fit in memory boundary. It might be a maintenance task if you need to reclaim the disk space, or if your data patterns are something like "do inserts over the course of a month, then on the first delete delete most of it" or other such wide oscillations in size. REINDEX is in kind of the same boat, but even less so: if your index is very sparse across disk, it might help to improve locality in the same way. But generally I wouldn't expect this to be a maintenance task or anything On 13 Dec 2013, at 16:37, veeresh kumar <veeru...@yahoo.com> wrote: > We are looking for database maintenance commands for the Sqlite database and > came across 2 commands which we felt that it would improve the performance if > used. The commands are VACUUM and REINDEX. I came to know that VACUUM just > helps us to reclaim the space and does not give any performance increase. > Just wanted to check of REINDEX or any other Sqlite command which you suggest > users to run it every month? > Our database size varies from 30GB to 50 GB,with various operations like > insert/update/delete would be performed on day to day basis.Just wanted to > know if there is a need to maintain the sqlite database so that the > performance does not degrade or just leave as it is. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >
signature.asc
Description: Message signed with OpenPGP using GPGMail
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
