How database index works

Question

Accepted Answer

Database index is a data structure that improves the speed of data retrieval operations.

Indexes are created on one or more columns of a table.
They work by creating a separate data structure that contains the indexed column(s) and a pointer to the actual data.
This allows the database to quickly locate the data based on the indexed column(s).
Indexes can be clustered or non-clustered, depending on how the data is physically stored on disk.
Clustered indexes determine the physical order of data in a table, while non-clustered indexes create a separate data structure.
Indexes can also be unique, which means that each value in the indexed column(s) must be unique.
However, creating too many indexes can slow down data insertion and updates.

Popular interview questions of Senior Staff Software Engineer