Database Index
- In Turkish
- Veritabanı İndeksi
In short
A database index is a data structure that helps a database find rows quickly without scanning a whole table, much like the index at the back of a book.
What is a database index?
A database index is an extra structure the database maintains alongside a table so it can quickly locate rows by the value of one or more columns. Without an index, a query like finding a user by email forces the database to check every row, which is called a full table scan and gets slower as the table grows.
Most relational databases store indexes as B-trees, sorted tree structures that let the database reach the right value in a few steps, even across millions of rows. Other types exist for special cases, such as hash indexes for exact matches, GIN indexes for full-text search and JSON data, and vector indexes for similarity search on embeddings.
The index at the back of a textbook is a good analogy: instead of reading every page to find 'recursion', you look it up alphabetically and jump to the right page. Databases automatically index primary keys and usually unique columns, and developers add other indexes on columns frequently used in WHERE, JOIN, and ORDER BY clauses.
Indexes are not free. Each one takes storage space, and every INSERT, UPDATE, or DELETE must also update the affected indexes, so too many indexes slow down writes. The goal is to index the columns your important queries actually filter or sort by, and commands like EXPLAIN show whether a query uses an index.
At a glance
Key takeaways
- Indexes speed up reads by avoiding full table scans.
- Most indexes are B-trees, which keep values sorted for fast lookups.
- Primary keys are indexed automatically.
- Every index uses extra storage and makes writes slightly slower.
- Use
EXPLAINto check whether a query uses an index.
Example
-- Without an index, this query scans every row in the table
SELECT * FROM users WHERE email = 'ada@example.com';
-- Create an index on the email column
CREATE INDEX idx_users_email ON users (email);
-- Ask the database how it runs the query (it should now use the index)
EXPLAIN SELECT * FROM users WHERE email = 'ada@example.com';
-- A composite index helps queries that filter by both columns
CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);Readers ask
Why not index every column?
Each index uses disk space and must be updated on every insert, update, and delete, so too many indexes slow down writes. Index the columns that your frequent queries filter, join, or sort on.
What is the difference between a clustered and a non-clustered index?
A clustered index determines the order in which the table's rows are physically stored, so a table can have only one. A non-clustered index is a separate structure that points to the rows, and a table can have many.
What is a composite index?
A composite index covers more than one column, such as (customer_id, created_at). It helps queries that filter on the first column, or on the first and second columns together, because the columns are sorted in that order.
See also
- DatabaseDatabases, p. 6A database is an organized collection of data stored on a computer, managed by software that lets applications save, search, and update it efficiently.
- SQLDatabases, p. 40SQL is the standard language for working with relational databases, used to create tables and to insert, query, update, and delete the data stored in them.
- AlgorithmProgramming Fundamentals, p. 2An algorithm is a finite, step-by-step set of instructions for solving a problem or completing a task, such as sorting a list or finding the shortest route.
- ORMDatabases, p. 33An ORM is a library that maps database tables to objects in your programming language, letting you read and write data with code instead of raw SQL.
- TransactionDatabases, p. 47A transaction is a group of database operations that succeed or fail as a single unit, so the data is never left in a half-finished, inconsistent state.
- Primary KeyDatabases, p. 36A primary key is a column, or set of columns, whose value uniquely identifies each row in a database table and can never be empty or duplicated.
Sources
Spotted a mistake or something missing on this page?Suggest an edit