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 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. > > 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 ?
Pretty much. Obviously there are a ton of details, but what you have is a good mental model for practical purposes. > 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) If you have a schema: CREATE TABLE t1(a INTEGER PRIMARY KEY, b UUID); And then create an index on column b, you will probably double the size of the database, yes. > 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); It's like a table that contains columns B and C, and the rowid. The table is sorted in the same order as the results of: SELECT b, c, rowid FROM t1 ORDER BY 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 ? Such a query can use the compound index T1Idx above. It can do the following too: WHERE b = 3 AND c = 4; WHERE b = 3 AND c > 4; but cannot be used to optimize: WHERE c = 4; Dan. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
