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 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 can imagine that you folks have a bunch of issue with indexes... Thanks, for confirming my "mental model". > >> 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. Okay, that was the key question for me. >> >> 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; > An interesting way to express how this index is build. Thank you. > >> ... >> 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; Ahh... I picked up the wrong example... Great, this is exactly inline how I was thinking how it might work. 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 overhead to scan two index tables. Thanks again for the useful advice. kind regards Marcus > > Dan. > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
