MySQL Optimization

Indexing dan query optimization sangat penting untuk performa aplikasi.

Indexing Strategy

-- Add index pada frequently queried columns
CREATE INDEX idx_email ON users(email);

-- Composite index untuk multi-column queries
CREATE INDEX idx_status_created ON orders(status, created_at);

-- Unique index
CREATE UNIQUE INDEX idx_email_unique ON users(email);

EXPLAIN Command

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

Perhatikan:

• type - Access method (ALL, index, range)
• key - Index yang digunakan
• rows - Perkiraan jumlah rows
• Extra - Info tambahan

Query Optimization Tips

1. Selalu gunakan WHERE clause
2. Hindari SELECT *
3. Gunakan LIMIT untuk large result sets
4. Optimalkan JOIN conditions
5. Gunakan prepared statements

Configuration Tuning

[mysqld]
# InnoDB Buffer Pool (70-80% dari available RAM)
innodb_buffer_pool_size = 4G

# Query Cache
query_cache_size = 128M
query_cache_type = 1

# Max Connections
max_connections = 200

Maintenance

-- Optimize table
OPTIMIZE TABLE users;

-- Analyze table
ANALYZE TABLE users;

-- Check table
CHECK TABLE users;