On May 27, 2009, at 10:13 PM, Marcus Grimm wrote: > 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.
If a single column index is like the index found in textbooks, a compound index with two fields is like the phone book. Sorted first by surname, then by first name. The "rowid", if you like, is the phone number. So, it's easy to find the set of phone numbers for everybody with the surname "Jones". It's easy to find the set of phone numbers for people called "Barry Jones". Quite difficult to find all the people called "Barry" though. If you had two separate indexes, one on surname and one on first-name, you could quickly find all the Jones's using the first index, or all the Barry's using the second index. But to find the set of people called "Barry Jones" would be much more work than it was with the compound index. Dan. > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
