Re: [sqlite] Full Table Scan after Analyze

2011-03-26 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/26/2011 03:12 AM, Black, Michael (IS) wrote: > When you say "All an index does" don't forget that an index is also usually > smaller than the data, thereby increase cache performance and reducing disk > seeks. That is muddied in the case

Re: [sqlite] Full Table Scan after Analyze

2011-03-26 Thread Black, Michael (IS)
e Database Subject: EXT :Re: [sqlite] Full Table Scan after Analyze On 25 Mar 2011, at 11:11pm, Jay A. Kreibich wrote: > On Fri, Mar 25, 2011 at 10:30:59PM +, Simon Slavin scratched on the wall: > >> Actually I'm surprised and not terribly impressed that SQLite ever >> does

Re: [sqlite] Full Table Scan after Analyze

2011-03-25 Thread Simon Slavin
On 25 Mar 2011, at 11:11pm, Jay A. Kreibich wrote: > On Fri, Mar 25, 2011 at 10:30:59PM +, Simon Slavin scratched on the wall: > >> Actually I'm surprised and not terribly impressed that SQLite ever >> does a scan when there's an ideal index available. > > Why? Do you want it to run

Re: [sqlite] Full Table Scan after Analyze

2011-03-25 Thread Jay A. Kreibich
On Fri, Mar 25, 2011 at 10:30:59PM +, Simon Slavin scratched on the wall: > Actually I'm surprised and not terribly impressed that SQLite ever > does a scan when there's an ideal index available. Why? Do you want it to run slower? Indexes are not magic bullets. Using an index to

Re: [sqlite] Full Table Scan after Analyze

2011-03-25 Thread Eric Minbiole
> Suggestion: After you run ANALYZE, go back and modify values in > sqlite_stat1 (and sqlite_stat2 if you are using SQLITE_ENABLE_STAT2) with > "typical" values that you would expect to see in most databases. Yes, you > can read and write the sqlite_stat1 and sqlite_stat2 tables. Maybe you can

Re: [sqlite] Full Table Scan after Analyze

2011-03-25 Thread Richard Hipp
On Fri, Mar 25, 2011 at 6:30 PM, Simon Slavin wrote: > > On 25 Mar 2011, at 10:18pm, Richard Hipp wrote: > > > Suggestion: After you run ANALYZE, go back and modify values in > > sqlite_stat1 (and sqlite_stat2 if you are using SQLITE_ENABLE_STAT2) with > > "typical" values

Re: [sqlite] Full Table Scan after Analyze

2011-03-25 Thread Simon Slavin
On 25 Mar 2011, at 10:18pm, Richard Hipp wrote: > Suggestion: After you run ANALYZE, go back and modify values in > sqlite_stat1 (and sqlite_stat2 if you are using SQLITE_ENABLE_STAT2) with > "typical" values that you would expect to see in most databases. Yes, you > can read and write the

Re: [sqlite] Full Table Scan after Analyze

2011-03-25 Thread Richard Hipp
On Fri, Mar 25, 2011 at 5:24 PM, Eric Minbiole wrote: > We have an application that has been using SQLite successfully for several > years. We use SQLite for various purposes within our product, with a few > dozen tables overall. > > Recently, I started to upgrade from

[sqlite] Full Table Scan after Analyze

2011-03-25 Thread Eric Minbiole
We have an application that has been using SQLite successfully for several years. We use SQLite for various purposes within our product, with a few dozen tables overall. Recently, I started to upgrade from version 3.6.3 to 3.7.5. During that time, I noticed that several previously fast indexed