Bits of .NET

⚡ Full Table Scan = Slow

Without index, database scans every row. Indexes are like book indexes. Find data instantly. 1000x faster.

📝 Create Indexes

-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (multiple columns)
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- Unique index (prevents duplicates)
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Full-text index (for search)
CREATE FULLTEXT INDEX idx_posts_search ON posts(title, content);

-- Include columns (cover query)
CREATE INDEX idx_orders_user ON orders(user_id) INCLUDE (total, status);

-- Partial index (only active rows)
CREATE INDEX idx_active_users ON users(last_login) WHERE active = true;

🎯 When to Index

-- ✅ Index columns used in WHERE
SELECT * FROM users WHERE email = 'alice@example.com';
-- Index on email column

-- ✅ Index columns used in JOIN
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- Index on orders.user_id, users.id (primary key)

-- ✅ Index columns used in ORDER BY
SELECT * FROM products ORDER BY price DESC;
-- Index on price column

-- ❌ Don't index low-cardinality columns (gender, status with few values)
-- ❌ Don't index columns rarely used in WHERE
-- ❌ Don't index tables that are mostly write-heavy (indexes slow writes)

💡 Monitor Index Usage

• PostgreSQL: pg_stat_user_indexes
• MySQL: SHOW INDEX FROM table;
• SQL Server: sys.dm_db_index_usage_stats
• Remove unused indexes (they slow writes)
• Rebuild fragmented indexes regularly