Database Indexing in System Design: What It Is and How It Works

In system design, database indexing is a technique that creates a fast lookup structure to quickly find data without scanning the entire database. It works like an index in a book, helping the system locate records efficiently and improve query performance.

How It Works

Imagine you have a huge book without an index. To find a topic, you'd have to read every page. A database index works like the book's index, listing key values and pointing to where the full data is stored. Instead of searching all data, the system looks up the index to jump directly to the needed records.

Technically, an index is a separate data structure, often a tree or hash, that stores key columns and references to the full rows. When a query asks for data using those keys, the database uses the index to find results quickly, reducing the time from scanning millions of rows to just a few steps.

Example

This example shows how to create an index on a database table and use it to speed up queries.

sql

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

-- Create an index on the email column
CREATE INDEX idx_email ON users(email);

-- Query using the indexed column
SELECT * FROM users WHERE email = '[e-mail]';

Output

Query uses index idx_email to quickly find the user with email '[e-mail]' without scanning the entire users table.

When to Use

Use database indexing when you have large tables and frequent queries filtering or sorting by specific columns. Indexes speed up read operations but add overhead to writes, so they are best for columns used often in searches or joins.

Real-world examples include:

• Searching users by email or username in social apps
• Filtering products by category or price in e-commerce
• Looking up transactions by date in financial systems

Key Points

• Indexes improve query speed by avoiding full table scans.
• They use extra storage and slow down inserts/updates slightly.
• Choosing the right columns to index is crucial for performance.
• Common index types include B-trees and hash indexes.

Key Takeaways

Database indexing creates a fast lookup to find data without scanning all records.

Indexes improve read query speed but add some overhead to writes.

Use indexes on columns frequently used in search, filter, or join operations.

Proper index design balances query speed and storage or update costs.