Thanks again Dan, the phonebook is an excellent example to use the compound index. So far I was thinking for such cases that it is enough to have one index and let sqlite test against the other value from the original table, like:
CREATE TABLE T1(A, B, C); CREATE INDEX T1x ON T1(B); SELECT * FROM T1 WHERE (B=4) AND (C=5); if the temporary result for B=4 is small, compared to the number of rows in that table, the additional test against C=5 will not harm very much, I guess. Of course, as I have learned now, a compound index would be better, and even better if the fetch could concentrate on index data only (as DRH mentioned)... This sounds reasonable if the datasize of C is not very big. In case C is a char like a UID it might be worse since the index will become big (in terms of bytes) -- then it is time to make experiments, I guess. Thanks again Marcus Dan wrote: > On May 27, 2009, at 10:13 PM, Marcus Grimm wrote: > ... >>> >>> 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
