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 to every young programmer to have a little course in assembly programming to understand what a compiler does and what not, why it is fast why it is slow, etc.... but maybe I'm old fashion. I know, maybe my post is a bit too unspecific. :-) Marcus P Kishor wrote: > On Wed, May 27, 2009 at 7:38 PM, Marcus Grimm <mgr...@medcom-online.de> 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 as a kind of table that has two columns which hold a copy of >> a) the row_ids of the indexed table. >> b) the value of the indexed column. > > See http://www.mail-archive.com/sqlite-users@sqlite.org/msg37474.html > for a very, very nice explanation from Igor of how indexes work. The > explanation uses the same metaphor of an index in a text book. > > >> 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 different to a standard sql table, am I right ? >> >> I often have to deal with columns which are UIDs that have a length >> of say 128 bytes and that will be the majority (in terms of byte-size) >> of that table. If I would now create an index on such a UID I will basically >> double the database size, correct ? >> (I know I can experiment this by my selve, but maybe a sql-guru here has >> allready >> the answer) >> >> How does an compound index work: >> >> Is it the same if I create two indexes compared to a single but compound >> index ? >> I guess no, because reading the optimizer hints from the sqlite doc I >> understand >> that sqlite will not use that index if I ask only for one of the column >> names, like: >> >> CREATE TABLE T1(A, B, C); >> CREATE INDEX T1Idx ON T1(B,C); >> ... >> SELECT * FROM T1 WHERE B=3; >> >> as far as I know this will most likely not use the index, but then I'm >> curious what >> is the benefit or application of a compound index compared to two single >> indexes ? >> >> Again sorry for beeing not very specific in the questions. >> >> Thanks >> >> Marcus Grimm >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > -- Marcus Grimm, MedCom GmbH Darmstadt, Rundeturmstr. 12, 64283 Darmstadt Tel: +49(0)6151-95147-10 Fax: +49(0)6151-95147-20 -------------------------------------------------- MedCom slogans of the month: "Vacation ? -- Every day at MedCom is a paid vacation!" "Friday I have monday in my mind." "MedCom -- Every week a vacation, every day an event, every hour a cliffhanger, every minute a climax." "Damned, it's weekend again!" _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
