Re: [sqlite] Help me understand the sqlite query optimizer

2011-03-21 Thread Simon Slavin
On 21 Mar 2011, at 5:46pm, Enrico Thierbach wrote: > On 21.03.2011, at 06:34, Simon Slavin wrote: > >> Suppose you have a TABLE employees with ten thousand rows and no indexes, >> and you execute >> >> SELECT id,firstname,surname FROM employees WHERE firstname='Guilherme' AND >> age=46 >> >>

Re: [sqlite] Help me understand the sqlite query optimizer

2011-03-21 Thread Enrico Thierbach
Hi Simon, I just stumbled about this post. > On 21.03.2011, at 06:34, Simon Slavin wrote: > > Suppose you have a TABLE employees with ten thousand rows and no indexes, and > you execute > > SELECT id,firstname,surname FROM employees WHERE firstname='Guilherme' AND > age=46 > > The quer

Re: [sqlite] Help me understand the sqlite query optimizer

2011-03-21 Thread Simon Slavin
On 21 Mar 2011, at 2:17pm, Guilherme Batista wrote: > So, in general, the the ANALYZE is not advantageous only if I have just 1 > index in my table, in that case the sqlite never use the ANALYZE... If you have exactly one INDEX which looks helpful for the query, SQLite will just use that. It w

Re: [sqlite] Help me understand the sqlite query optimizer

2011-03-21 Thread Richard Hipp
On Mon, Mar 21, 2011 at 9:48 AM, Simon Slavin 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

Re: [sqlite] Help me understand the sqlite query optimizer

2011-03-21 Thread Simon Slavin
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, j

Re: [sqlite] Help me understand the sqlite query optimizer

2011-03-21 Thread Guilherme Batista
Thanks for the big and helpful explanation Simon! My changes in my study, theoretically, would improve the performance just of big and complex queries, so it's not my intention improve the sqlite, I want just study the effects.. Just a few more questions if anyone can help me 1) So if I run ANALY

Re: [sqlite] Help me understand the sqlite query optimizer

2011-03-20 Thread Simon Slavin
On 21 Mar 2011, at 3:33am, Guilherme Batista wrote: > I'll implement some changes in the sqlite query optimization for study > purpose... before I start studying the code please help me understand the > basic idea... > > 1) The basic idea of optimization is to optmize and then execute. Where in