A database index helps a database find records without examining every row. This lesson introduces what an index stores, why it can make queries faster, and how indexes can help enforce database rules.
An index is a data structure built from selected table columns to make searches more efficient. It commonly stores the indexed key values in an organized form together with a pointer or other link to the original row. Once the database finds a key in the index, it can use that link to retrieve the complete record.
All flashcards
What is a database index?
A data structure built from selected table columns that helps the database find records efficiently, often by storing key values and links to the original rows.
What is a full table scan?
A search method that examines table rows to find records matching a condition. Checking all N rows takes O(N) time.
What are two costs of maintaining indexes?
Indexes consume additional storage and require maintenance when rows are inserted, updated, or deleted.
How can indexes support database constraints?
A UNIQUE index enforces uniqueness; suitable indexes can make primary-key, foreign-key, and exclusion checks more efficient.
What characterizes a clustered index?
It arranges physical table rows in the index order, which can help with sequential and range retrieval.
Quiz questions
What is the primary purpose of a database index?Answer: To locate matching rows without examining the entire table.
What does an index commonly store alongside key values?Answer: A pointer or link to the corresponding table row.
For many balanced-tree indexes, what is the approximate lookup complexity?Answer: O(logN)
What chiefly determines whether a query can use an index effectively?Answer: Whether the query conditions match the index design.
How does a UNIQUE index help enforce a database rule?Answer: It prevents duplicate values among the indexed columns.
Based on the Wikipedia article Database index, licensed CC BY-SA 4.0. Adapted into a summary, flashcards and quizzes.