Primary Keys

We check the existence of indexes & primary keys (PK) on all tables in the schema, as one step within our health check.

Without a PK, the integrity of the data remains suspect – duplicates can & will exist in the table. Also, no Foreign Keys (FK) can exist without the necessary PK. Without this, orphan data can appear. These are fundamental data quality reasons for implementing them.

However, performance is also a huge reason to implement them.

I still find developers creating tables with no indexes. ‘They are too small’ to need indexes. Yet, it is the performance that is the first issue that we come across.

I found one table where the developer would SELECT @ID = MAX(id) FROM table and then insert a new row as @ID + 1. Without PKs or indexes, the SELECT was scanning through 250,000 records all the way to the last record (with the highest value) before inserting the record. This was being called every 10 seconds and took about the same time to complete. The server was straining under the load.

When the PK was added to this ID field, the query fell to milliseconds in duration and the server went from permanently busy down to always idle.

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: