Indexes — B-tree, hash, full-text — when each is used
Concept
Indexes are data structures that allow the database to find rows without scanning the entire table. Without an index: O(n) scan. With an index: O(log n) B-tree lookup.
B-tree index (default): A balanced tree. Supports exact match (=), range (<, >, BETWEEN), and prefix-match LIKE 'foo%'. Inefficient for LIKE '%foo' (can't use prefix). The most common index type.
Composite index: An index on multiple columns. CREATE INDEX idx_user_status ON orders (user_id, status). The leftmost prefix rule: this index helps queries that filter by user_id alone, or user_id + status together. It does NOT help queries that filter by status alone.
Hash index: O(1) exact match. Does NOT support range queries or sorting. MySQL InnoDB does not support standalone hash indexes (but uses an adaptive hash table internally). PostgreSQL supports CREATE INDEX ... USING HASH.
Full-text index: Used for text search (MATCH ... AGAINST in MySQL, tsvector in PostgreSQL). Tokenizes text, supports natural language search, relevance ranking. Does NOT support prefix/suffix wildcard like B-tree.
Covering index: An index that includes ALL columns needed by a query. The database can answer the query from the index alone — no need to touch the actual table rows ("index-only scan").
Index cost: Indexes are not free. Every INSERT, UPDATE, DELETE must update all relevant indexes. Too many indexes slow down writes.
When to add an index:
- Foreign key columns (always).
- Columns used in
WHERE,JOIN ON,ORDER BY. - Columns used in
GROUP BY. EXPLAINshows "Full Table Scan" on a large table.
Code Example
-- B-tree index — default type
CREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX idx_users_email_unique ON users (email); -- unique constraint
-- Composite index — leftmost prefix rule
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
-- Helps: WHERE user_id = 1
-- Helps: WHERE user_id = 1 AND status = 'pending'
-- Does NOT help: WHERE status = 'pending' (status is not the leftmost column)
-- Full-text index (MySQL)
CREATE FULLTEXT INDEX idx_posts_content ON posts (title, body);
-- Usage:
SELECT * FROM posts WHERE MATCH(title, body) AGAINST ('laravel performance' IN NATURAL LANGUAGE MODE);
-- Covering index — includes all columns needed by the query
CREATE INDEX idx_users_covering ON users (active, created_at, id, email);
-- Query: SELECT id, email FROM users WHERE active = 1 ORDER BY created_at DESC
-- Database reads ONLY the index, never touches the users table rows
-- PostgreSQL — partial index (indexes only a subset of rows)
CREATE INDEX idx_active_users ON users (email) WHERE deleted_at IS NULL;
-- Smaller index, only covers active users
-- EXPLAIN to check if index is being used (MySQL)
EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';
-- Look for: type = 'ref' or 'range' (good) vs 'ALL' (full scan = bad)
-- key column shows which index is used
-- Force an index (use sparingly — optimizer usually knows best)
SELECT * FROM orders FORCE INDEX (idx_orders_user_status)
WHERE user_id = 42;<?php
// Laravel migration — adding indexes
Schema::table('orders', function (Blueprint $table) {
$table->index('user_id'); // single column
$table->index(['user_id', 'status']); // composite
$table->unique('order_number'); // unique
$table->fullText(['title', 'body']); // full-text (MySQL)
});
// Check if a query uses an index in Laravel
$sql = User::where('email', 'alice@example.com')->toSql();
// Then run EXPLAIN on it in a DB client