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
