What is an Index
An index is a data structure that allows the database to find rows quickly - like an index in a book. Without an index, the database scans every row (full table scan). With an index, it jumps directly to matching rows. Indexes speed up reads but slow down writes.
Index Fundamentals
- An index is a separate data structure maintained alongside the table
- Full table scan: reads every row - O(N) time
- Index scan: finds matching rows directly - O(log N) time
- Indexes automatically used for WHERE, JOIN ON, and ORDER BY columns
- Trade-off: faster reads, slower INSERTs/UPDATEs/DELETEs (index must be updated)
- PRIMARY KEY and UNIQUE constraints create indexes automatically
Index Concept & Example
-- Without index: full table scan on 1 million orders
SELECT * FROM orders WHERE user_id = 42;
-- Database reads ALL 1,000,000 rows to find those with user_id=42
-- With index: direct lookup using B-tree
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- Database follows B-tree to user_id=42 directly
-- Returns matching rows in microseconds instead of seconds
-- Indexes already present (auto-created):
-- users.id → PRIMARY KEY auto-creates a unique index
-- users.email → UNIQUE constraint auto-creates a unique index
-- orders.user_id → you must create this manually (FK does NOT auto-index in PostgreSQL)
-- List indexes on a table (PostgreSQL)
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';
-- MySQL equivalent
SHOW INDEX FROM orders;Quick Quiz
Tip
Tip
Practice What is an Index in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
B-Tree default. Hash for = only. Full-text for search. GiST for geospatial + PostGIS.
Common Mistake
Warning
A common mistake with What is an Index is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of What is an Index from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- An index is a data structure that allows the database to find rows quickly - like an index in a book.
- An index is a separate data structure maintained alongside the table
- Full table scan: reads every row - O(N) time
- Index scan: finds matching rows directly - O(log N) time