All Concept Bites
14 views
Databaseintermediate
SQL Indexing
An extra structure that lets the database find rows without scanning the table
Reviewed by Abhijith N M · 24 October 2026 · Editorial standards
The Simple VersionNo jargon
An index is usually a B-tree that stores a sorted copy of one or more columns plus a pointer to the row. Instead of reading every row, the database descends the tree to the matching range. A composite index is ordered left to right, so (user_id, created_at) helps queries that filter by user_id, or by user_id and a date range, but not a query that filters only by created_at. Indexes speed reads and slow writes, because every insert or update must maintain them.
SQL Indexing — Visual Breakdown
Option A
Without Index (Full Table Scan)
A
- ✓Read every row one by one
- ✓O(n) time complexity
- ✓1M rows = 1M comparisons
- ✓Fast for tiny tables
- ✓Painful at scale
- ✓No extra storage needed
Option B
With Index (B-Tree Lookup)
B
- ✓Jump directly to matching rows
- ✓O(log n) time complexity
- ✓1M rows = ~20 comparisons
- ✓Ideal for large tables
- ✓Slightly slower writes
- ✓Needs extra storage (5-20%)
VS
Why It Matters in Production
The first step in a slow query is EXPLAIN, not adding indexes blindly. Look for sequential scans on large tables, missing join indexes, and filters that cannot use an index because of a function or a leading wildcard. Covering indexes can avoid table lookups, but wide indexes cost disk and cache. Too many indexes is a real problem: they increase write latency, bloat storage and confuse the planner. Review unused indexes and drop them.
Implementation & Syntax
SQL
-- Without index (slow on large tables)
SELECT * FROM users WHERE email = 'dev@example.com';
-- Execution plan: Seq Scan (reads every row)
-- Create an index
CREATE INDEX ix_users_email ON users(email);
-- Now the same query uses the index
SELECT * FROM users WHERE email = 'dev@example.com';
-- Execution plan: Index Scan (jumps directly)
-- Check if query uses index
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'dev@example.com';Quick Concept Check
Test your understanding in 15 seconds
What SQL command lets you inspect whether a query is using an index?
Related Concepts
Written and edited by the nexGenCoders team. Spotted a mistake? Tell us and we will fix it.