MySQL Performance Tuning: Complete Guide to Index Optimization

A well-optimized MySQL database with strategic indexing can handle 10x more concurrent users while reducing server costs by up to 60%. This guide covers everything you need to know about MySQL index optimization.

MySQL Index Types and When to Use Them

Primary Index (Clustered)

Automatically created with PRIMARY KEY. Data is physically sorted by this index, making it the most efficient for lookups.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255),
    name VARCHAR(100)
);

Secondary Index

Non-clustered indexes pointing to primary key. Essential for fast lookups on non-primary columns.

CREATE INDEX idx_email ON users(email);

Composite Index

Multi-column indexes where column order matters. Place the most selective column first.

CREATE INDEX idx_orders_compound
ON orders(user_id, status, created_at);

Covering Index

Includes all columns needed by query, eliminating table lookups entirely.

CREATE INDEX idx_products_covering
ON products(category_id, price, name, stock);

Advanced Index Optimization Techniques

1. Index Selectivity Analysis

Evaluate columns with high uniqueness (>10% optimal). This can reduce query time by 70%.

-- Check column selectivity
SELECT
    COUNT(DISTINCT column_name) / COUNT(*) AS selectivity
FROM table_name;

2. Prefix Index Optimization

Use prefix indexes for VARCHAR columns to save space while maintaining performance.

-- Find optimal prefix length
SELECT
    COUNT(DISTINCT LEFT(column_name, 10)) / COUNT(*) AS sel10,
    COUNT(DISTINCT LEFT(column_name, 20)) / COUNT(*) AS sel20
FROM table_name;

-- Create prefix index
CREATE INDEX idx_description ON products(description(20));

3. Index Maintenance and Monitoring

Check index usage statistics regularly to identify unused indexes.

SELECT
    object_schema, object_name, index_name,
    count_read, count_write
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
ORDER BY count_read DESC;

Common MySQL Indexing Mistakes to Avoid

• Wrong Column Order: Placing less selective columns first in composite indexes reduces effectiveness
• Ignoring Query Patterns: Creating indexes without analyzing actual usage patterns
• Unused Indexes: Maintaining indexes that are never used wastes resources

Conclusion

Strategic index optimization is one of the most impactful ways to improve MySQL performance. Start by analyzing your query patterns, create appropriate indexes, and regularly monitor their effectiveness. Remember: the best index is one that matches your actual query workload.