Effects of the Use of Non-Natural Keys on Query Performance in Relational Databases
Author: Mitko Radoev
Abstract
Choosing a primary key is of a particular importance in the design of a relational database. What are the possible keys, i.e. which columns or combinations of columns are candidate keys? Which of these candidate keys is best suited for a primary key? For quite some time, a trend is gaining more popularity for choosing a primary key - not to use the existing columns of the relationship but to create new, additional column to ensure the uniqueness and to act as a primary key. Recently it became a common practice to use artificial or surrogate identifiers, even in cases where the relationships already have perfectly suited natural identifiers. Using artificial keys is motivated mainly by practical considerations - the keys are shorter, do not have cascade update when changing the key, queries run faster etc. Does using of non-natural keys really increase the performance of the database? This study is testing what is happening with the database in adopting one or the other approach.