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 > -- Puneet Kishor http://www.punkish.org/ Carbon Model http://carbonmodel.org/ Charter Member, Open Source Geospatial Foundation http://www.osgeo.org/ Science Commons Fellow, Geospatial Data http://sciencecommons.org Nelson Institute, UW-Madison http://www.nelson.wisc.edu/ ----------------------------------------------------------------------- collaborate, communicate, compete ======================================================================= _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
