Why Bother Indexing Foreign Key Columns?

Last week I shared a script to identify foreign keys which weren’t indexed. What we didn’t discuss in detail was why it can be important to index these columns.

Here we’ll demonstrate the two areas we can see great benefits by introducing the indexes.

Query optimisation

We’ll start with the typical benefit of indexing – query optimisation. The index provides a smaller set of data which is sorted to allow faster searching.

The most common example of this when querying a child table based on a filtered parent table. Using the StackOverflow database let’s retrieve all votes cast by users from the ‘UK’:

SELECT v.*
FROM dbo.Users u
	INNER JOIN dbo.Votes v ON u.Id = v.UserId
WHERE [Location] = 'UK';

The query can get a list of users from the UK and will then need to find the votes for these users. Without the Votes table having an index on the UserId column, we’d need to scan the entire votes table:

However with an index added we can change the scan to a seek operation (plus key lookup):

We can see the benefits on the plan in terms of operator timings and row counts. In terms of performance metrics, the new plan reduces CPU and reads by over 95%.

Record deletions

When removing records from a table which is referenced by foreign keys, the engine must check related tables to ensure the keys aren’t in use before they can be deleted.

With tables which are referenced widely in the solution – for example Users – this can be a performance bottleneck.

Let’s again use the StackOverflow database to demonstrate:

DELETE FROM dbo.Users
WHERE Id = @UserId;

With 3 foreign keys referencing the User details, the deletion of a single record will require 3 table scans to deal with:

That’s an insane amount of data to be reading to delete one record. With indexes on those foreign key columns the plan looks much better:

We’ve now got 3 seek operations on the tables and the query is much faster. So much so that the CPU and reads are reduced by over 99.9%.

As you’ve seen from the performance improvements above, these indexes can be game-changing under the right conditions.

It should be noted that adding indexes will add overhead to INSERT and UPDATE operations so this impact should be considered, particularly in well established solutions.

If you’d like to see what opportunities you might be missing with your own foreign keys, check out last week’s post with a script to reveal those.