On Mon, Mar 21, 2011 at 9:48 AM, Simon Slavin <slav...@bigfraud.org> wrote:
> > 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. > Not exactly. The results of ANALYZE will be used if there are two or more indices that SQLite needs to choose from. So for example: CREATE TABLE t1(a,b,c); CREATE INDEX t1a ON t1(a); CREATE INDEX t1b ON t1(b); Then if you do: SELECT * FROM t1 WHERE a=5 AND b=11; In that case, SQLite can choose to use index t1a or t1b. But it cannot reasonably use both. (It could, but the result would almost always be slower than using just one or the other index, and so the case of using both is not even considered.) Now if the table T1 contains only 5 distinct values of A but contains 5000 distinct values for B, it makes since to use T1B as the index because that index is more likely to narrow down the search to a small number of rows more quickly. But without running ANALYZE, SQLite has no way of knowing that there are more distinct values of B than their are of A and so it will choose arbitrarily. Further suppose that you compile with -DSQLITE_ENABLE_STAT2 such that the ANALYZE command will also record a histogram of the data in each index. Then consider the query: SELECT * FROM t1 WHERE a=5; Without ANALYZE, this second query will always use index T1A. But if the histogram data shows that (say) 95% of the entries in T1 have a=5, then it would be cheaper to do a full table scan, and that is exactly what SQLite will do in that case. So it is important to have good indices. But ANALYZE can still help. Note, though, that the whole point of SQL (not just SQLite but all SQL implementations) is that you really should need to worry about any of this until your application is finished. Then you can run performance measurements using real data and add indices and/or ANALYZE as necessary to address any performance problems. I don't think the OP groks the seriousness of this proverb: "Premature optimization is the root of all evil". -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users