Section 3.6. Multiple Indexing of the Same Columns

3.6. Multiple Indexing of the Same Columns

The systematic indexing of foreign keys can often lead to situations in which columns belong to several indexes. Let's consider once again a classic example. This consists of an ordering system in which some order_details table contains, for each order (identified by an order_id, a foreign key referencing the orders table) articles (identified by article_id, a foreign key referencing the articles table) that have been purchased, and in what quantity. What we have here is an associative table (order_details) resolving a many-to-many relationship between the tables orders and articles. Figure 3-9 illustrates the relationships among the three tables.

Figure 3-9. The Orders/Articles example

Typically, the primary key of order_details will be a composite key, made of the two foreign keys. Order entry is the very case when the referenced table and the referencing table are likely to be concurrently modified, and therefore we must index the order_id foreign key. However, the column that is defined here as a foreign key is already indexed as part of the composite primary key, and (this is the important point) as the very first column in the primary key. Since this column is the first column of the composite primary key, it can for all intents and purposes provide all the benefits as if it were an indexed foreign key. A composite index is perfectly usable even if not all columns in the key are specified, as long as those at the beginning of the key are.

When descending an index tree such as the one described earlier in this chapter, it is quite sufficient to be able to compare the leading characters of the key to the index nodes to determine which branch of the index the search should continue down. There is therefore no reason to index order_id alone, since the DBMS will be able to use the index on (order_id, article_id) to check for child rows when somebody is working on the orders table. Locks will therefore not be required for both tables. Note, once again, that this reasoning applies only because order_id happens to be the very first column in the composite primary key. Had the primary key been defined as (article_id, order_id), then we would have had to create an index on order_id alone, while not building an index on the other foreign key, article_id.

Indexing every foreign key may result in redundant indexing.