=?ISO-8859-1?Q?Tobias_Rundstr=F6m?= <[EMAIL PROTECTED]> wrote: > > I downloaded your database and the query above was indeed slow. > > But then I ran "ANALYZE" so that SQLite can gather statistics > > on the various indices, then reran the query. This time, SQLite > > was able to use the ANALYZE results to make better index choices > > and the query is quite speedy. > > > > The results of ANALYZE are stored in a special table named > > "sqlite_stat1". So you only have to run it once and the result > > will be used for all subsequent queries. > > Thank, you. > > This helps of course. How often should I run analyze? Trying to > figure out how to make this programaticly for new installations. I > guess there has to be a bit of data in the table before I can run > analyze with the outcome that I want? >
The statistics on all of your databases are likely to be very much alike. So run ANALYZE once on a sample database such as the one you posted earlier. Save off the data that the ANALYZE command writes into the "sqlite_stat1" table. When creating a new database, run ANALYZE before doing any CREATE TABLEs. The ANALYZE command will create an empty "sqlite_stat1" table. Then use INSERTs to populate the sqlite_stat1 table with information saved from when you ran ANALYZE on the large sample database. -- D. Richard Hipp <[EMAIL PROTECTED]>
