Database Index Optimization Strategies for MySQL and PostgreSQL
Proper indexing is one of the most impactful optimizations for database performance on your Breeze. Poorly designed indexes waste disk space and slow down writes without improving reads.
Identify Missing Indexes
-- MySQL: Find slow queries
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
-- Review /var/log/mysql/mysql-slow.log
-- PostgreSQL: Find sequential scans on large tables
SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 1000
ORDER BY seq_scan DESC;Composite Index Best Practices
-- Place high-selectivity columns first
-- Good: matches queries filtering by status then date
CREATE INDEX idx_orders_status_date ON orders(status, created_at);
-- Use EXPLAIN to verify index usage
EXPLAIN ANALYZE SELECT * FROM orders
WHERE status = 'active' AND created_at > '2026-01-01';Covering Indexes
-- MySQL: Include all queried columns to avoid table lookups
CREATE INDEX idx_users_email_name ON users(email, first_name, last_name);
-- PostgreSQL: Use INCLUDE for non-key columns
CREATE INDEX idx_users_email ON users(email) INCLUDE (first_name, last_name);Remove Unused Indexes
-- MySQL: Check index usage statistics
SELECT * FROM sys.schema_unused_indexes;
-- PostgreSQL: Find indexes with zero scans
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;Review index usage quarterly. Each unnecessary index slows INSERT, UPDATE, and DELETE operations on your Breeze database. Balance read performance against write overhead.