A database index is essentially a data structure that improves the speed of queries returning data. This does come at the cost of using more storage space.
Indexes are used to to locate data quicker and without the need of searching every single row in the table, they are a copy of the column data and contain a direct link to the complete row of data.

In some databases it is possible to create indexes based on expressions and possibly functions. This means for example we could use initcap(first_name) on an index and it would store only the values with the first letter Capitalised.

There are many types of indexes and methods for indexing.

Indexing Methods include

• Non-Clustered – Data is present in an arbitrary order, with the logical ordering specified by the index. Usually exist on non primary keys and can have more than one index on a database table
• Clustered – This alters the data block in a certain order to match that of the index, meaning the row data is also stored in order. You can only have one clustered index on a database table and this is usually on the primary key of the table.
• Cluster – This is when multiple databases and tables are joined. The records sharing the value of a cluster key will be stored together. This may improve the joins between these tables as the data being stored together means it is easier to locate the record.

The types of Indexes are:-

• Bitmap Index – This index stores most of its data as bit arrays and uses bitwise logical operations for its queries. The Bitmap index is designed for columns where values repeat frequently such as a Gender. This can have a significant performance advantage over the commonly used tree indexes.
• Dense Index – A file with pairs of keys and pointers for every record in the data file. In a clustered index with duplicate entries this will point to the first record with that key.
• Sparse Index – A file with pairs of keys and pointers for every record in the block in the data file. In a clustered index with duplicate entries this will point to the lowest search key in the block.
• Reverse Index – This index reverses the key before entering it into the index. Reversing the key value is particularly useful for indexing data such as sequence numbers, where new key values monotonically increase.