All Concept Bites
15 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%)
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

Complementary topics
SQL vs NoSQL
What is Caching?

Written and edited by the nexGenCoders team. Spotted a mistake? Tell us and we will fix it.