On 21 Mar 2011, at 12:12pm, Guilherme Batista wrote: > 1) So if I run ANALYZE, it will gather statistics about all columns in all > tables from the database?
You can add parameters to the end of the command: http://www.sqlite.org/lang_analyze.html but without them it will analyze everything, just as you wrote it above. > 2) And you said if I do not use ANALYZE, the optimizer will use a > less-informed guess... how does it make this guess? This is important to me. For an overview of how the query planner works, see here: http://www.sqlite.org/optoverview.html especially sections 5.2 to 11.0. But because there is no documentation for it, you cannot tell how it will handle your particular circumstances. And it is different in different versions of SQLite. As you can see from that page, if you are at all worried about how the optimizer will make its guesses, the solution is to provide a suitable INDEX for your query, or to run ANALYZE. You only need to run ANALYZE once, after your database has been populated with reasonable data. Or you could run it perhaps once a year in case the nature of your data changes. Note that if you provide good INDEXes as you should, then the results of ANALYZE will never be used at all because the query finder will find it has an excellent INDEX before it even starts trying to guess how to access the data by a slower searching method. > 3) If someone could just point me where the query execution plan is built > and executed in the code, so I have a better idea where to start studying > the code, I'll be appreciated. You can use the EXPLAIN QUERY PLAN command with a particular SELECT or UPDATE to find out how the query analyzer decides to handle it, given the exact circumstances it finds at the time: http://www.sqlite.org/eqp.html Note that the query planner is frequently improved between different versions of SQLite, so even if you discover how a query is handled with the current version of SQLite and with your current data, it may change in the future. If you are at all concerned about how your query will be executed, just provide an INDEX that will make that query very fast. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
