ANALYZE analyzes the data distribution of the indexes. This is affected by the data distribution of the tables, since the indexes index the tables. The statistics computed by the analysis are stored in the database and used to choose the most efficient way to execute a query.
If your data never changes, then you never need to run analyze but the one time. If the statistical result never changes, then you only need to run analyze one time. However, if it is possible that the data distribution could change, then you need to run analyze sto that the statistical result "matches" the actual database. For example if you have a table with 4 records in it and run analyze, this will be recorded. If you later insert 14 billion records in that table, then when you perform a query the optimizer will ACT AS IF the table only had four records. This may or may not result in a non-optimal query plan and non-optimal performance. Only you, the designer of the database, will know the answer to that question. The solution however is simple: run analyze so that the statistical data matches the current content of the database. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Paolo Bolzoni > Sent: Saturday, 11 May, 2013 09:17 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Scan table in explain query. Question. > > At the moment EXPLAIN gives me a very small number of rows > for all the queries (thanks to indexes) a part of this one, so if I > understand correctly ANALYZE is not needed anymore. > > Or I can get a speed up executing once in a while? > > Regards, > Paolo > > > On Sat, May 11, 2013 at 1:06 PM, Clemens Ladisch <clem...@ladisch.de> > wrote: > > Paolo Bolzoni wrote: > >> sqlite> explain query plan SELECT id FROM tour LIMIT 1; > >> 0|0|0|SCAN TABLE tour USING COVERING INDEX tour_unsorted_path_idx > >> (~1000000 rows) > >> > >> I am not still sure I understand completely the output of explain > >> query plan. > > > > It means that SQLite estimates that the table (or its index) contains > > about 1000000 rows. > > > > The LIMIT clause is *not* used for this estimate. > > > >> To get this simple result, sqlite3 actually scans the whole > >> table? > > > > No. Without the LIMIT, the query would scan the entire index. With the > > LIMIT, the scan is stopped after the first row. > > > >> I just have a question about query written in the begin, in my > >> application I use it to know if a table is empty. I execute it > >> and just use sqlite3_step return value. Errors a part, if it is > >> SQLITE_DONE the table is empty, if it is SQLITE_ROW it is not. > > > > This is more a documentation than an optimization improvement, but if > > you are not interested in the value of any particular column, you should > > not request it in the first place; i.e., use something like: > > SELECT 0 FROM tour LIMIT 1; > > > > > > Regards, > > Clemens > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
