Database Indexing: Why Your Queries Are Slow and How to Fix Them

1
2
3
4
5
6
7
8
9
10
SELECT u.*, p.title, p.created_at, COUNT(l.id) as likes
FROM users u
JOIN posts p ON u.id = p.user_id
LEFT JOIN likes l ON p.id = l.post_id
WHERE u.created_at > '2024-01-01'
AND p.status = 'published'
AND u.location LIKE '%New York%'
GROUP BY u.id, p.id
ORDER BY p.created_at DESC
LIMIT 20;

1
2
3
4
5
6
7
8
9
10
EXPLAIN ANALYZE SELECT u.*, p.title, p.created_at, COUNT(l.id) as likes
FROM users u
JOIN posts p ON u.id = p.user_id
LEFT JOIN likes l ON p.id = l.post_id
WHERE u.created_at > '2024-01-01'
AND p.status = 'published'
AND u.location LIKE '%New York%'
GROUP BY u.id, p.id
ORDER BY p.created_at DESC
LIMIT 20;

1
2
3
-- Index for the WHERE clause filters
CREATE INDEX idx_users_created_location ON users(created_at, location);
-- Why this order? created_at is more selective than LIKE '%pattern%'

1
2
3
-- Index for JOIN and WHERE conditions
CREATE INDEX idx_posts_user_status_created ON posts(user_id, status, created_at DESC);
-- This covers the JOIN, WHERE, and ORDER BY in one index

1
2
-- For the LEFT JOIN
CREATE INDEX idx_likes_post_id ON likes(post_id);

1
2
3
4
-- Query plan now shows:
-- Index Scan using idx_users_created_location
-- Nested Loop Join
-- Index Scan using idx_posts_user_status_created

1
2
3
4
5
6
-- Good: Selective column first
CREATE INDEX idx_orders_date_status ON orders(created_at, status);
-- Bad: Less selective column first
CREATE INDEX idx_orders_status_date ON orders(status, created_at);
-- Query: WHERE created_at > '2025-01-01' AND status = 'pending'
-- The first index is much more efficient

1
2
3
4
5
6
7
8
-- Covering index - no table lookup needed
CREATE INDEX idx_posts_covering 
ON posts(user_id, status) 
INCLUDE (title, created_at, content);
-- This query uses only the index:
SELECT title, created_at, content 
FROM posts 
WHERE user_id = 123 AND status = 'published';

1
2
3
4
5
6
7
8
9
-- Bad: Too many single-column indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_name ON users(name);
CREATE INDEX idx_users_created ON users(created_at);
CREATE INDEX idx_users_status ON users(status);
-- Better: Strategic compound indexes
CREATE INDEX idx_users_email ON users(email); -- For login
CREATE INDEX idx_users_name_status ON users(name, status); -- For search
CREATE INDEX idx_users_created_status ON users(created_at, status); -- For filtering

1
2
3
4
5
6
7
8
-- This WON'T use a regular index:
WHERE email LIKE '%@gmail.com'
-- This WILL use an index:
WHERE email LIKE 'john%'
-- For suffix searches, consider:
-- 1. Full-text search indexes
-- 2. Reverse string indexes
-- 3. Trigram indexes (PostgreSQL)

1
2
3
4
5
-- Find unused indexes
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY schemaname, tablename, indexname;

1
2
3
4
5
6
7
8
9
10
-- Check index cardinality
SELECT 
TABLE_NAME,
INDEX_NAME,
CARDINALITY,
SUB_PART,
NULLABLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY TABLE_NAME, SEQ_IN_INDEX;

1
2
3
4
5
6
-- PostgreSQL: Update query planner statistics
ANALYZE users;
ANALYZE posts;
-- SQL Server: Update statistics
UPDATE STATISTICS users;
UPDATE STATISTICS posts;

1
2
3
4
5
6
7
8
9
10
11
-- PostgreSQL: Check index bloat
SELECT 
schemaname,
tablename,
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
async function testQuery(name, query) {
const start = Date.now();
try {
const result = await pool.query(query);
const duration = Date.now() - start;
console.log(`${name}: ${duration}ms (${result.rows.length} rows)`);
return { duration, rows: result.rows.length };
} catch (error) {
console.error(`${name} failed:`, error.message);
}
}
// Test before and after indexing
async function runTests() {
await testQuery('Dashboard Query', dashboardQuery);
await testQuery('Search Query', searchQuery);
await testQuery('Reports Query', reportsQuery);
}
runTests();