On Mon, Jun 01, 2009 at 12:32:26PM +0200, Ralf scratched on the wall: > Hello, > [>> ] considering a m:n relation a.id <- a.id,b.id -> b.id, is it due to > performance, advisable to put an index on a.id,b.id ?
Yes, but very likely not for the reasons you're thinking. In a many-to-many bridge table you'll typically define (a_id,b_id) as a multi-column PRIMARY KEY, since you usually want memberships to be unique. This automatically creates a UNIQUE index across (a_id,b_id). While the main purpose of this index is to enforce the UNIQUE constraint on the PRIMARY KEY, it can also be used to accelerate lookups based off an a.id value (e.g. given an a.id value, find a set of b.id values). This index will *not* help with lookups based off b.id values (e.g. given a b.id value, find a set of a.id values). Multi-column indexes must utilize the columns in-order. If you don't have a known a.id value, the (a_id,b_id) index isn't useful. Since most many-to-many relations are largely two way-- that is, the operation a.id -> set(b.id) is about as common as b.id -> set(a.id)-- you'll normally want an additional index on (b_id,a_id) as well. The a_id field in the second index is not required, but including it gives SQLite the option of returning the a_id values directly from the index, cutting your index lookup time in half. -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
