2

This is kind of a general DB design question. If one has an associative entity table, i.e. a cross-reference, containing records that basically just consist of two FK references, should it be indexed in some way? Is it necessary to explicitly index that table, since the PKs in the associated tables are already indexed by definition? If one should index it, should it be a combination index, consisting of the two FK fields together?

Henry
  • 600
  • 2
  • 7
  • 22

2 Answers2

3

Indexes on the referenced pk columns in the other tables do not cover it.

By defining the two fk columns as composite primary key of the "associative entity" table (as you should in most cases - provided that associations are unique), you implicitly create a multi-column index.

That covers all queries involving both or the first columns optimally.
It also covers queries on the second column, but in a less effective way.
If you have important queries involving just the second column, create an additional index on that one, too.

Read all the details about the topic at this related question on dba.SE.
Or this question on SO, also covering this topic.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

Suppose your associative table has a schema such as:

CREATE TABLE Association
(
    ReferenceA  INTEGER NOT NULL REFERENCES TableA CONSTRAINT FK1_Association,
    ReferenceB  INTEGER NOT NULL REFERENCES TableB CONSTRAINT FK2_Association,
    PRIMARY KEY(ReferenceA, ReferenceB)            CONSTRAINT PK_Association
);

The chances are that your DBMS will automatically create some indexes.

Some DBMS will create an index for each of the two foreign keys and also a unique index for the primary key. This is slightly wasteful since the PK index could be used for accessing ReferenceA too.

Ideally, there will be just two indexes: the PK (unique) index and the (duplicates allowed) FK index for ReferenceB, assuming that the PK index has ReferenceA as the first column.

If a DBMS does not automatically create indexes to enforce the referential integrity constraints, you'll want to create the RI or FK duplicates-allowed index. If it doesn't automatically create an index to enforce the PK constraint, you'll want to create that unique index too. The upside is that you'll only create the indexes for the ideal case.

Depending on your DBMS, you might find it more effective to create the table without the constraints, then to add the indexes, and then to add the constraints (which will then use the indexes you created). Things like fragmentation schemes can also factor into this; I ignored them above.

The concept remains simple — you want two indexes in total, one to enforce uniqueness on both columns and provide fast access on the leading column, and a non-unique or duplicates-allowed index on the trailing column.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
  • 1
    The question was tagged as being related to PostgreSQL, and PostgreSQL never creates an index for a foreign key; it does create a unique btree index for a primary key or unique constraint. – kgrittn Apr 26 '12 at 17:46
  • I agree there's a PostgreSQL tag; there's also a comment that it is a general DB design question. I tried to cover the general bases, allowing for possibilities such as 'PostgreSQL does not create an index for a FK constraint' (which I was not aware of, and am a little surprised about, but only a little surprised about). Informix, in contrast, is in the 'create an index for everything' school; there'd be three indexes on the table where two are sufficient. – Jonathan Leffler Apr 26 '12 at 18:08
  • Some DBMS also allow for index-only tables. I'm not sure whether that makes sense here. It might if you had two 'index only' indexes, one on (ReferenceA, ReferenceB) in that order, and one for the other order. More likely, it doesn't make sense for this scenario. – Jonathan Leffler Apr 26 '12 at 18:13