MySQL Performance Optimization

Performance optimization in MySQL means identifying and fixing bottlenecks so that queries run faster, the server uses fewer resources, and the database scales effectively as data grows. Optimization applies to query writing, table design, indexing, and server configuration.

1. Use Indexes Effectively

Indexes are the single most impactful tool for query performance. Without an index on a filtered or joined column, MySQL scans every row.

-- Add index on a frequently searched column
CREATE INDEX idx_last_name ON employees (last_name);

-- Composite index for queries filtering by two columns together
CREATE INDEX idx_dept_salary ON employees (department, salary);

Check if a query uses an index:

EXPLAIN SELECT * FROM employees WHERE last_name = 'Kumar';

2. Select Only Needed Columns

Avoid SELECT * in production queries. Fetch only the columns required.

-- Slow: fetches all columns unnecessarily
SELECT * FROM employees WHERE department = 'IT';

-- Fast: fetches only what is needed
SELECT emp_id, emp_name FROM employees WHERE department = 'IT';

3. Avoid Functions on Indexed Columns in WHERE

Applying a function to an indexed column prevents MySQL from using the index.

-- MySQL cannot use the index on order_date here
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- This allows index usage
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

4. Use LIMIT for Large Result Sets

SELECT emp_id, emp_name FROM employees ORDER BY salary DESC LIMIT 10;

Fetching the top 10 rows is far faster than retrieving all rows when only a few are needed.

5. Optimise JOIN Queries

• Always join on indexed columns.
• Filter with WHERE before joining when possible.
• Avoid joining too many tables in a single query.

-- Ensure both join columns are indexed
CREATE INDEX idx_dept_id ON employees (dept_id);
CREATE INDEX idx_dept_pk ON departments (dept_id);

6. Use Appropriate Data Types

Using the smallest appropriate data type saves storage and speeds up comparisons.

7. Avoid SELECT in a Loop

Running a query inside a loop (in application code or cursors) sends many small queries. Replace loops with set-based SQL operations.

-- Slow: individual updates in a loop
UPDATE employees SET salary = 50000 WHERE emp_id = 1;
UPDATE employees SET salary = 55000 WHERE emp_id = 2;

-- Fast: single update with CASE
UPDATE employees
SET salary = CASE
    WHEN emp_id = 1 THEN 50000
    WHEN emp_id = 2 THEN 55000
END
WHERE emp_id IN (1, 2);

8. Use Query Cache Wisely

MySQL can cache the results of identical SELECT queries. Avoid non-deterministic functions like NOW() or RAND() in cached queries as they change on every call.

9. Normalise Tables Appropriately

Normalised tables reduce data redundancy and improve update performance. However, for read-heavy reporting systems, a denormalised structure with fewer JOINs can be faster. Balance based on workload.

10. Monitor with SHOW STATUS and SHOW PROCESSLIST

-- View running queries
SHOW PROCESSLIST;

-- View server statistics
SHOW STATUS LIKE 'Slow_queries';
SHOW STATUS LIKE 'Threads_connected';

Key Points

• Indexes are the most effective single improvement for query speed.
• Avoid SELECT * and fetch only required columns.
• Never apply functions to indexed columns in WHERE conditions.
• Use LIMIT to reduce result set size when only top rows are needed.
• Use EXPLAIN to analyse query execution plans and identify missing indexes.