Richard, thanks you very much for the example and advice. Ahh, yes. I missed the point that sqlite might use an index also as a kind of buffer to fetch data.
kind regards Marcus D. Richard Hipp wrote: > On May 27, 2009, at 11:13 AM, Marcus Grimm wrote: >> 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. >> > > Indices can be used for sorting as well as for lookup. And sometime > content can be pulled from indices to avoid an additional search to > find the original table entry. For these reasons, compound indices > can be useful. > > For example: > > CREATE TABLE t1(a,b,c); > CREATE INDEX t1abc ON t1(a,b,c); > > Then if you say: > > SELECT c FROM t1 WHERE a=5 ORDER BY b; > > The query above will run faster with the index on a,b,c than it would > with any other index. The first column of the index is used to do the > search. Then the index is scanned in order to output rows in order by > b. Finally, the c value is pulled directly from the index, avoiding a > lookup of the original table. > > > > D. Richard Hipp > d...@hwaci.com > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Marcus Grimm, MedCom GmbH Darmstadt, Rundeturmstr. 12, 64283 Darmstadt Tel: +49(0)6151-95147-10 Fax: +49(0)6151-95147-20 -------------------------------------------------- MedCom slogans of the month: "Vacation ? -- Every day at MedCom is a paid vacation!" "Friday I have monday in my mind." "MedCom -- Every week a vacation, every day an event, every hour a cliffhanger, every minute a climax." "Damned, it's weekend again!" _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
