What is an Index
An index is a data structure that speeds up database searches. Like a book’s index, it quickly locates the position of target data.
Why it speeds things up: Without an index, a full scan of all rows is needed, but with an index, only the necessary rows are accessed.
B-Tree Index
The most common index structure. Used in MySQL’s InnoDB and PostgreSQL.
B-Tree Characteristics
- Balanced tree structure
- All leaf nodes at the same depth
- Supports range searches
- Maintains data in sorted order
Operations B-Tree Excels At
- Exact match search:
WHERE id = 100 - Range search:
WHERE price BETWEEN 1000 AND 5000 - Prefix search:
WHERE name LIKE 'John%' - Sorting:
ORDER BY created_at
Hash Index
An index that uses hash functions to calculate data positions.
Hash Index Characteristics
- Very fast exact match search (O(1))
- Cannot be used for range searches
- Cannot be used for sorting
-- Creating Hash index in MySQL (MEMORY engine)
CREATE TABLE sessions (
id VARCHAR(64),
data TEXT,
INDEX USING HASH (id)
) ENGINE=MEMORY;
Creating Indexes
-- Single column index
CREATE INDEX idx_email ON users(email);
-- Composite index
CREATE INDEX idx_name_created ON posts(user_id, created_at);
-- Unique index
CREATE UNIQUE INDEX idx_unique_email ON users(email);
Index Considerations
Disadvantages
- Consumes storage space
- Slows down INSERT/UPDATE/DELETE
- Excessive indexes create overhead
Cases Where Indexes Don’t Work
- Functions applied to columns:
WHERE YEAR(created_at) = 2024 - Suffix matching:
WHERE name LIKE '%smith' - Negation conditions:
WHERE status != 'deleted' - NULL searches (depends on case)
Checking execution plans: Use the
EXPLAINcommand to verify if queries are using indexes.
Summary
Proper indexes dramatically improve query performance, but creating them blindly can backfire. It’s important to check execution plans and determine the necessary indexes.
← Back to list