Indexes

An index is a data structure that improves the speed of data retrieval operations on a table. It acts as a lookup mechanism that allows the database management system (DBMS) to quickly find specific rows in a table based on the values in one or more columns. Essentially, an index provides a way to efficiently access data without having to scan the entire table.

Here are the key characteristics and concepts related to indexes in databases:

Purpose: The primary purpose of an index is to enhance query performance. By creating an index on one or more columns, the DBMS can quickly locate the rows that match specific search criteria, such as those specified in a WHERE clause.
Structure: An index typically consists of a data structure that organizes the indexed column's values in a way that facilitates fast lookups. Common index structures include B-trees, hash tables, and bitmap indexes.
Indexing Columns: Indexes are created on specific columns of a table. These columns are often chosen based on their frequency of use in search conditions or joins, or if they represent primary key or unique constraints.
Indexed Data: For each indexed column, the index contains a copy of the column's values along with a reference to the corresponding row in the table. This allows the DBMS to quickly locate the rows matching a particular value.
Trade-offs: While indexes significantly improve query performance, they come with certain trade-offs. Indexes occupy additional storage space, and maintaining them may add overhead to data modification operations (e.g., insert, update, delete).
Clustered vs. Non-clustered Indexes: In some databases, indexes are categorized as clustered or non-clustered. A clustered index determines the physical order of the data on disk and, in effect, reorganizes the table. A non-clustered index, on the other hand, contains a separate data structure that points to the actual rows in the table.
Selectivity: Index selectivity refers to the uniqueness of the indexed values. High selectivity means the indexed values are very unique, making the index more efficient, while low selectivity means the indexed values are less unique, potentially leading to less efficient index usage.

Creating appropriate indexes is an essential aspect of database performance tuning. The choice of columns to index, the index structure, and the number of indexes depend on the specific queries that will be executed, the nature of the data, and the workload patterns of the database. A well-designed set of indexes can significantly improve query performance and overall database responsiveness.

An index is an orderly arrangement to logically access rows in a table
The index key is the index’s reference point that leads to data location identified by the key
In a unique index, the index key can have only one pointer value associated with it
A table can have many indexes, but each index is associated with only one table
The index key can have multiple attributes