Indexing improves database search performance by creating a structure that allows quick data retrieval.
An index is a database structure that speeds up data retrieval by creating a fast lookup mechanism for specific columns. Instead of scanning every row to find what you need, the database uses the index to jump directly to the relevant data.
Think of it like a book index. Instead of reading every page to find mentions of "database," you check the index at the back, which tells you exactly which pages contain that word.
Without an index, finding a user by email means checking every single row in the users table. If you have 10 million users, that takes seconds or minutes.
With an index on the email column, the database finds the user instantly, even with 10 million rows.
When you create an index on a column, the database builds a separate data structure (usually a B-tree) that organizes values for fast searching.
CREATE INDEX idx_email ON users(email);
Now searching by email is lightning fast:
SELECT * FROM users WHERE email = "john@example.com";
The database uses the index instead of scanning the entire table.
No related topics found.
Single Column Index: Index on one column (most common).
CREATE INDEX idx_last_name ON users(last_name);
Composite Index: Index on multiple columns together.
CREATE INDEX idx_name ON users(first_name, last_name);
Unique Index: Ensures no duplicate values (often used with primary keys).
CREATE UNIQUE INDEX idx_username ON users(username);
Full-Text Index: Optimized for searching text content.
CREATE FULLTEXT INDEX idx_content ON articles(content);
Indexes make reads faster but writes slower.
Faster Reads: Queries using indexed columns execute almost instantly.
Slower Writes: Every INSERT, UPDATE, or DELETE must also update all relevant indexes.
More Storage: Indexes consume disk space (sometimes significant amounts).
This is why you do not index every column - only the ones you frequently search or filter by.
Index columns that appear in:
Common examples:
Small Tables: If a table has 100 rows, indexes add overhead without meaningful speed gains.
Frequently Updated Columns: If a column changes constantly, maintaining the index becomes expensive.
Low Cardinality Columns: Columns with few unique values (like boolean fields) benefit less from indexing.
An unindexed query on a large table might take 30 seconds. After adding the right index, the same query runs in 0.01 seconds.
E-commerce sites index product IDs, categories, and prices. Social media platforms index user IDs, timestamps, and hashtags. The right indexes mean the difference between fast, responsive apps and slow, frustrating ones.
Most databases provide tools to see which indexes are being used and which are wasted:
-- PostgreSQL
SELECT * FROM pg_stat_user_indexes;
-- MySQL
SHOW INDEX FROM users;
Remove unused indexes - they slow down writes without providing benefits.
Start Simple: Index primary keys and foreign keys first.
Monitor Queries: Use database query analyzers to find slow queries.
Add Indexes Strategically: Index based on real query patterns, not guesses.
Test Impact: Measure query performance before and after adding indexes.
Regular Maintenance: Rebuild fragmented indexes periodically.
Indexes are one of the most powerful tools for database optimization. A few well-placed indexes can transform a slow application into a fast one.
Learn to identify which columns need indexing by analyzing your queries. This skill is essential for any developer working with databases.