Re: [sqlite] Does VACUUM imply REINDEX?
On 19 Feb 2020, at 4:36pm, Olivier Mascia wrote: > having an apparently sane database (pragma integrity_check and pragma > foreign_key_check successful at least), it would make sense to do : REINDEX, > ANALYZE and then VACUUM as part of final preparation step for archive (the DB > would be seldom queried later). That would seem to the a good order in which to do those five things. If I was setting up a new system for customer use, I would give them a maintenance procedure which did those things, let them run it whenever they wanted, but tell them to run it at least once a year. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does VACUUM imply REINDEX?
> Le 19 févr. 2020 à 17:26, Simon Slavin a écrit : > > On 19 Feb 2020, at 4:18pm, Richard Hipp wrote: > >> There is no benefit to running >> ANALYZE after running VACUUM if the ANALYZE data (the content of the >> sqlite_stat1 table) is still up-to-date. > > However, if you have a yearly maintenance procedure, it might make sense to > include ANALYZE in it. This would take care of circumstances where your > business has changed character, for instance changing from selling a lot to a > few customers, to selling a little to many customers. So, reading the precise and complete description of Richard, and this idea from Simon, having an apparently sane database (pragma integrity_check and pragma foreign_key_check successful at least), it would make sense to do : REINDEX, ANALYZE and then VACUUM as part of final preparation step for archive (the DB would be seldom queried later). In this plan, the REINDEX step would seem probably useless if there have been no collation changes, but I have seen cases where some indexes are incredibly more compact after REINDEX (which is logical). ⏤ Olivier ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does VACUUM imply REINDEX?
On 19 Feb 2020, at 4:18pm, Richard Hipp wrote: > There is no benefit to running > ANALYZE after running VACUUM if the ANALYZE data (the content of the > sqlite_stat1 table) is still up-to-date. However, if you have a yearly maintenance procedure, it might make sense to include ANALYZE in it. This would take care of circumstances where your business has changed character, for instance changing from selling a lot to a few customers, to selling a little to many customers. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does VACUUM imply REINDEX?
On 2/19/20, Olivier Mascia wrote: > Hello, > > As part of a rare database "maintenance"... > Does VACUUM, in essence, implies whatever actions REINDEX would do? > Would REINDEX before VACUUM make any more sense? > > And what about ANALYZE? Would it be wise or useless, to run it after VACUUM? VACUUM does not imply REINDEX. Those are separate actions. VACUUM repacks all the content in to minimal space, and thus (probably) makes the database file a little smaller. It also reorganizes the pages so that tables are grouped together and are in search order, which might make table scans faster, depending on the underlying filesystem. REINDEX rebuilds an index from scratch based on the original table data. This amounts to a big sort. REINDEX was invented so that if you change the definition of a collating function, you can run REINDEX on all indexes that use that collating function and it will reorder the indexes into the new correct order. REINDEX will also fix any index corruption. But if you have index corruption, that is a symptom of deeper problems which should be addressed. You should not paper-over such problems by running REINDEX. VACUUM is independent of ANALYZE. The information computed by ANALYZE is the same before and after VACUUM. There is no benefit to running ANALYZE after running VACUUM if the ANALYZE data (the content of the sqlite_stat1 table) is still up-to-date. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
