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 ? 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
