Indexing can have positive and negative effects on a database. There are four types of index used in a database and each has a different impact on the database. When creating indexes on foreign keys in a database this should be done with caution. Too many indexes will negatively impact a database.
As databases grow in size and complexity, their performance and search speed often gets slower. Indexing is a technique used to improve database search performance. With indexing a database is able to find information at greater speed. Also, data is more efficiently stored using indexing. Linear search is not used under indexing because it is inefficient for large databases. Instead, a sub-linear time look-up is used.
There are four types of Index used. Each has a different and unique impact on the database. The most commonly used is a Bitmap Index, which stores most of its data as bit arrays. The Bitmap Index is used when a variable will not repeat itself very frequently. Another type of index is a Dense Index. The Dense Index is used in databases to file with pairs of keys and points for each record in the database. In this index, every key is associated with a certain pointer. The next type of index is the Spare Index. The Sparse Index has pairs of keys and points for every block in the data files that make up the database. Every key in the file is associated with a particular pointer. The Sparse Index points to the lowest search key in each block. The last type of index is the Reverse Index. In a Reverse Index, a reverse key alters the key value before entering it in the index. Reversing the key value is used for indexing sequence numbers, in which new key values monotonically increase.
When creating an index on foreign keys that are used to enforce referential integrity in a relationship, a join between primary and foreign keys occurs. However, this action should not be taken if the database is small. Foreign keys typically require more space. The need for additional space can negatively affect the database performance and speed.
Too many indexes on a table can negatively affect a database performance. Adding indexes causes slower performance and more time to be taken for monitoring and testing. It is important to ensure that the additional indexes produce the desired effects and will not negatively impact database performance. Any benefit additional indexes give must be considered for capacity planning going forward. When new indexes are added, they must be tested and monitored for flaws.
References:
http://sqlblog.com/blogs/davide_mauri/archive/2011/06/11/how-the-number-of-indexes-built-on-a-table-can-impact-performances.aspx
http://stackoverflow.com/questions/141232/how-many-database-indexes-is-too-many
http://www.sqlperformance.com/2012/11/t-sql-queries/benefits-indexing-foreign-keys
