On Wed, May 27, 2009 at 10:23:04AM -0400, D. Richard Hipp scratched on the wall:
>
> 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.
Don't forget that indexes are also used to enforce UNIQUE
constraints. You have to have compound indexes to support
compound keys. Although, if the index exists solely for the purpose
of enforcing a UNIQUE constraint, the column order has less meaning.
One additional point. This is getting a bit into the nitty gritty
details, but SQLite is capable of servicing data requests directly
from indexes, if all the data is available. The example, consider a
Person table and a Group table. If you want to build a many-to-many
relationship between these tables, you typically create a bridge table
that might be Person_Group_Membership that consists of the columns
(ROWID, person_id, group_id). You need a UNIQUE index over
(person_id, group_id) to make sure memberships are properly unique.
This index can also be used for person_id directed lookups (e.g.
WHERE person_id = 34). Many people would also create an index on
(group_id) for group_id directed lookups. This would allow a
group_id to be looked up in the index, getting a ROWID. The ROWID
could then be looked up in the main table, getting the required
person_id.
If, however, the second index is made across both fields as
(group_id, person_id) and SQLite is aware that it is touching the
Person_Group_Membership table just to translate a group_id into a
person_id (or set there of), it can service such requests directly
out of the index data so that a second lookup in the main table is
not required. This effectively doubles the speed of the lookup at the
cost of a slightly larger index.
This is also another example that the indexes {person_id, group_id}
and {group_id, person_id} are very different and are used for
different things-- and that there are legit situations to have both.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
