Re: [sqlite] Some index questions

On 28/05/2009 12:24 AM, Dan wrote: > > If a single column index is like the index found in textbooks, > a compound index with two fields is like the phone book. Sorted first by > surname, then by first name. The "rowid", if you like, is the phone > number. > > So, it's easy to find the set of

Re: [sqlite] Some index questions

On Wed, May 27, 2009 at 12:34:00PM -0500, Jay A. Kreibich scratched on the wall: > On Wed, May 27, 2009 at 10:23:04AM -0400, D. Richard Hipp scratched on the > wall: > > Indices can be used for sorting as well as for lookup. And sometime > > content can be pulled from indices to avoid an

Re: [sqlite] Some index questions

On Wed, May 27, 2009 at 10:23:04AM -0400, D. Richard Hipp scratched on the wall: > > On May 27, 2009, at 11:13 AM, Marcus Grimm wrote: > > I guess such compound indexes have only a benefit for specific > > queries, like the above that use all the time exactly these two > > columns, otherwise two

Re: [sqlite] Some index questions

Thanks again Dan, the phonebook is an excellent example to use the compound index. So far I was thinking for such cases that it is enough to have one index and let sqlite test against the other value from the original table, like: CREATE TABLE T1(A, B, C); CREATE INDEX T1x ON T1(B); SELECT *

Re: [sqlite] Some index questions

Richard, thanks you very much for the example and advice. Ahh, yes. I missed the point that sqlite might use an index also as a kind of buffer to fetch data. kind regards Marcus D. Richard Hipp wrote: > On May 27, 2009, at 11:13 AM, Marcus Grimm wrote: >> I guess such compound indexes have

Re: [sqlite] Some index questions

On May 27, 2009, at 10:13 PM, Marcus Grimm wrote: > Hi Dan, > > thanks for your answers: this was exactly what I was looking for. > > Dan wrote: >> On May 27, 2009, at 9:08 PM, Marcus Grimm wrote: >>> >>> The difference, I guess, to a "real" sql table, is that it is sorted >>> with respect to

Re: [sqlite] Some index questions

On May 27, 2009, at 11:13 AM, Marcus Grimm wrote: > I guess such compound indexes have only a benefit for specific > queries, like the above that use all the time exactly these two > columns, otherwise two seperate indexes will do the same job but maybe > a little slower due to additional

Re: [sqlite] Some index questions

Hi Dan, thanks for your answers: this was exactly what I was looking for. Dan wrote: > On May 27, 2009, at 9:08 PM, Marcus Grimm wrote: >> >> The difference, I guess, to a "real" sql table, is that it is sorted >> with respect to the indexed column and not by row_id, something that >> makes them

Re: [sqlite] Some index questions

On May 27, 2009, at 9:08 PM, Marcus Grimm wrote: > Hi List, > > this is not very sqlite specific but hopefully somebody will give > me some info on this, as I haven't yet found a nice description of > this: > > I'm curios how an index works internally, my suspect is that an index > can be seen

Re: [sqlite] Some index questions

Thanks Puneet, I know Igors nice text book example... :-) My questions are a little more technical like "If I would do a DB engine"... to judge better when or when not using or creating an index, etc. I think it is useful to understand what it is behind all this, similar that I would recommend

Re: [sqlite] Some index questions

On Wed, May 27, 2009 at 7:38 PM, Marcus Grimm wrote: > Hi List, > > this is not very sqlite specific but hopefully somebody will give > me some info on this, as I haven't yet found a nice description of this: > > I'm curios how an index works internally, my suspect is

[sqlite] Some index questions

Hi List, this is not very sqlite specific but hopefully somebody will give me some info on this, as I haven't yet found a nice description of this: I'm curios how an index works internally, my suspect is that an index can be seen as a kind of table that has two columns which hold a copy of a)