What are the different types of database indexes?

1 Clustered index

A clustered index is a type of index that physically orders the data in the database table according to the index key. This means that there is only one clustered index per table, and it defines the logical order of the rows. A clustered index can speed up queries that perform range scans, such as finding all records between two dates or values, since the data is already sorted and stored together. However, a clustered index can also slow down insertions, updates, and deletions, since the data may need to be moved or reorganized to maintain the order.

2 Non-clustered index

A non-clustered index is a type of index that does not affect the physical order of the data in the database table. Instead, it creates a separate data structure, such as a B-tree or a hash table, that stores the index key and a pointer to the corresponding record in the table. A non-clustered index can have multiple columns and multiple indexes per table, and it can improve the performance of queries that filter, join, or aggregate data based on specific criteria. However, a non-clustered index can also consume more storage space and memory, and it can require more disk I/O to access the data.

3 Full-text index

A full-text index is a type of index that enables text-based searches on natural language data, such as documents, articles, or emails. A full-text index uses linguistic analysis and algorithms to break down the text into tokens, such as words, phrases, or stems, and store them in an inverted index. An inverted index maps each token to a list of documents that contain it, along with some metadata, such as frequency or position. A full-text index can support complex and flexible queries that match, rank, or highlight text based on keywords, synonyms, proximity, or relevance. However, a full-text index can also be expensive to create and maintain, and it can have limitations on the supported languages, formats, and features.

4 Spatial index

A spatial index is a type of index that facilitates queries on geometric data, such as points, lines, polygons, or regions. A spatial index uses spatial data types and functions to represent and manipulate the data, and it uses specialized data structures and algorithms to optimize the spatial operations, such as intersection, distance, or containment. A spatial index can enable applications that deal with maps, locations, or geospatial analysis, such as navigation, routing, or clustering. However, a spatial index can also be complex and challenging to implement and tune, and it can have trade-offs between accuracy and performance.

5 Bitmap index

A bitmap index is a type of index that uses a binary representation of the data values and their occurrences in the database table. A bitmap index consists of a set of bitmaps, one for each distinct value of the indexed column, where each bit corresponds to a row in the table and indicates whether the row has that value or not. A bitmap index can compress the data and perform fast bitwise operations on the bitmaps, such as AND, OR, or NOT, to answer queries that involve multiple conditions or aggregations. However, a bitmap index can also be inefficient for columns that have high cardinality, meaning many distinct values, or for tables that have frequent modifications, since the bitmaps may need to be updated or restructured.

6 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?