Reading this thread I decided to run an explain query plan for a query that I have with multiple selects combined with union. The (simplified) result is:
SEARCH TABLE UB USING COVERING INDEX UBS (UBS=?) (~5108 rows) USE TEMP B-TREE FOR GROUP BY USE TEMP B-TREE FOR ORDER BY SEARCH TABLE UB USING COVERING INDEX UBA (UBA=?) (~5108 rows) USE TEMP B-TREE FOR GROUP BY USE TEMP B-TREE FOR ORDER BY COMPOUND SUBQUERIES 7 AND 8 (UNION) SEARCH TABLE UB USING COVERING INDEX UBO (UBO=?) (~5108 rows) USE TEMP B-TREE FOR GROUP BY USE TEMP B-TREE FOR ORDER BY COMPOUND SUBQUERIES 6 AND 9 (UNION) SEARCH TABLE UC USING INDEX UCS (UCS=?) (~10847 rows) USE TEMP B-TREE FOR GROUP BY USE TEMP B-TREE FOR ORDER BY COMPOUND SUBQUERIES 5 AND 10 (UNION) SCAN TABLE UC (~10847 rows) USE TEMP B-TREE FOR GROUP BY USE TEMP B-TREE FOR ORDER BY COMPOUND SUBQUERIES 4 AND 11 (UNION) SCAN TABLE UC (~10847 rows) USE TEMP B-TREE FOR GROUP BY USE TEMP B-TREE FOR ORDER BY COMPOUND SUBQUERIES 3 AND 12 (UNION) SCAN TABLE UC (~10847 rows) USE TEMP B-TREE FOR GROUP BY USE TEMP B-TREE FOR ORDER BY COMPOUND SUBQUERIES 2 AND 13 (UNION) SCAN TABLE UB USING COVERING INDEX UBW (~10215 rows) COMPOUND SUBQUERIES 1 AND 14 (UNION) I realise that there are 3 complete scans of table UC, which is not what I would prefer to see. But what draws my attention are the three variations: SEARCH USING COVERING INDEX SEARCH USING INDEX SCAN USING COVERING INDEX What is the difference and what do they imply? Thanks, Staffan On Sat, May 11, 2013 at 5:49 PM, Keith Medcalf <kmedc...@dessus.com> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
