Master MySQL Performance: 20 Essential SQL Optimization Techniques

1. Introduction

SQL optimization is a key skill for Java engineers. Efficient database interaction can significantly improve application performance, response time, and user experience.

2. Practical Cases

2.1 Use Indexes

Indexes allow the database to locate data quickly, greatly improving query performance. Create indexes on columns used in WHERE , JOIN , ORDER BY and GROUP BY clauses, and consider covering indexes that contain all needed columns.

Wrong example

<code>SELECT * FROM users WHERE name = 'pack'</code>

Correct example

<code>CREATE INDEX idx_name ON users (name);</code><code>SELECT name, email FROM users WHERE name = 'pack';</code>

Creating an index on the name column of the users table speeds up the query.

2.2 Avoid SELECT *

Using SELECT * retrieves all columns, which can reduce efficiency and cause unnecessary data transfer.

Wrong example

<code>SELECT * FROM users;</code>

Correct example

<code>SELECT name, age FROM users;</code>

Specifying only required columns reduces the amount of data transmitted.

2.3 Proper Use of JOIN

Incorrect join syntax can lead to performance problems. Use the appropriate join type for the query.

Wrong example

<code>SELECT u.name, o.create_time FROM users u, orders o WHERE u.id = o.uid;</code>

Correct example

<code>SELECT u.name, o.create_time FROM users u INNER JOIN orders o ON u.id = o.uid;</code>

This query uses INNER JOIN to combine data from the users and orders tables.

2.4 Filter Data Early with WHERE

Filtering data early reduces the amount of data processed.

Wrong example

<code>SELECT name, age FROM users;</code>

Correct example

<code>SELECT name, age FROM users WHERE status = 0;</code>

Only rows with a normal status are retrieved, reducing processing load.

2.5 Limit Result Set

Use LIMIT to restrict the number of returned rows when you do not need the entire dataset.

Wrong example

<code>SELECT name, age FROM users WHERE status = 0;</code>

Correct example

<code>SELECT name, age FROM users WHERE status = 0 LIMIT 10;</code>

This query retrieves the first 10 valid rows, reducing data transfer.

2.6 Use EXISTS Instead of IN

EXISTS can be more efficient than IN , especially for large result sets.

<code># 1. Using IN
SELECT * FROM customers WHERE id IN (SELECT cid FROM orders);
# 2. Using EXISTS
SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.cid = c.id);</code>

2.7 Avoid Functions in WHERE

Using functions in the WHERE clause can prevent index usage and slow queries.

Wrong example

<code>SELECT name, age FROM users WHERE YEAR(create_time) = 2024;</code>

Correct example

<code>SELECT name, age FROM users WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';</code>

This avoids the function and allows the index on create_time to be used.

2.8 Use JOIN Instead of Subquery

JOINs are usually more efficient than subqueries, especially on large data sets.

Wrong example

<code>SELECT name, (SELECT create_time FROM orders WHERE uid = users.id) AS create_time FROM users;</code>

Correct example

<code>SELECT u.name, o.create_time FROM users u JOIN orders o ON u.id = o.uid;</code>

The JOIN provides better performance.

2.9 Optimize GROUP BY and ORDER BY

These clauses can be resource‑intensive. Use indexes on the involved columns and reduce the number of columns.

Wrong example

<code>SELECT uid, COUNT(*), MAX(create_time) FROM orders GROUP BY uid, create_time ORDER BY create_time;</code>

Correct example

<code>SELECT uid, COUNT(*) FROM orders GROUP BY uid ORDER BY uid;</code>

Indexing the grouped column improves performance.

2.10 Choose Proper Data Types

Selecting appropriate data types greatly affects performance and storage efficiency. Avoid using TEXT or BLOB unless necessary.

Wrong example

<code>CREATE TABLE users (id BIGINT auto_increment PRIMARY KEY, name TEXT, create_time TIMESTAMP);</code>

Correct example

<code>CREATE TABLE users (id BIGINT auto_increment PRIMARY KEY, name VARCHAR(100), create_time TIMESTAMP);</code>

Using the correct type improves performance and storage.

2.11 Analyze Execution Plan

Use EXPLAIN to analyze query execution plans and identify performance bottlenecks.

<code>EXPLAIN SELECT name, sex, age FROM big_table t WHERE t.name = 'Pack';</code>

Based on the result, analyze whether indexes are used and their types.

2.12 Use Connection Pool

Connection pools reduce the overhead of establishing database connections.

Wrong example

<code>Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "xxxooo");
conn.close();</code>

Correct example

<code>HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/test");
config.setUsername("root");
config.setPassword("xxxooo");
config.setMaximumPoolSize(10);
HikariDataSource ds = new HikariDataSource(config);
Connection conn = ds.getConnection();
// ...
conn.close();</code>

In Spring Boot, this can be configured via properties without manual code.

2.13 Use Batch Processing

Batch inserts/updates greatly improve performance for multiple data modifications.

Wrong example

<code>Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
for (User user : userList) {
    stmt.executeUpdate("INSERT INTO users (name, age) VALUES ('" + user.getName() + "', '" + user.getAge() + "')");
}
stmt.close();
conn.close();</code>

Correct example

<code>Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement("INSERT INTO users (name, age) VALUES (?, ?)");
for (User user : userList) {
    ps.setString(1, user.getName());
    ps.setInt(2, user.getAge());
    ps.addBatch();
}
ps.executeBatch();
ps.close();
conn.close();</code>

Batch processing inserts data efficiently.

2.14 Optimize JOIN Order

Ensure join columns are indexed and start joining from the smallest table.

Wrong example

<code>SELECT u.name, o.create_time FROM orders o JOIN users u ON u.id = o.uid WHERE u.status = 0;</code>

Correct example

<code>SELECT u.name, o.create_time FROM users u JOIN orders o ON u.id = o.uid WHERE u.status = 0;</code>

This query joins on indexed columns, improving performance.

2.15 Optimize Subqueries

Replace subqueries with joins or CTEs when possible.

Wrong example

<code>SELECT o.* FROM orders o WHERE o.amount > (SELECT AVG(o2.amount) FROM orders o2 WHERE o2.customer_id = o.customer_id);</code>

Correct example

<code>WITH customer_avg AS (
    SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id
)
SELECT o.* FROM orders o JOIN customer_avg ca ON o.customer_id = ca.customer_id WHERE o.amount > ca.avg_amount;</code>

2.16 Optimize Aggregation Queries

Index columns used in GROUP BY , consider summary tables or materialized views for frequent aggregations.

Wrong example

<code>SELECT customer_id, SUM(amount) AS total_amount, COUNT(*) AS order_count FROM orders GROUP BY customer_id;</code>

Correct example

<code>CREATE INDEX idx_customer_id ON orders (customer_id);
SELECT customer_id, SUM(amount) AS total_amount, COUNT(*) AS order_count FROM orders GROUP BY customer_id;</code>

2.17 Use Summary Columns

Store pre‑computed aggregates in a summary column to avoid expensive calculations at query time.

Wrong example

<code>SELECT user_id, SUM(amount) AS total_amount FROM orders GROUP BY uid;</code>

Correct example

<code>ALTER TABLE users ADD total_order_amount DECIMAL(10,2);
UPDATE users u SET total_order_amount = (SELECT SUM(amount) FROM orders o WHERE o.uid = u.id);</code>

The summary column holds each user's total order amount.

2.18 Use Materialized Views

Materialized views cache complex query results, improving read performance.

Wrong example

<code>SELECT uid, COUNT(*), SUM(amount) FROM orders GROUP BY uid;</code>

Correct example

<code>CREATE MATERIALIZED VIEW user_order_summary AS
SELECT uid, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders GROUP BY uid;</code>

The view stores pre‑computed order summaries.

2.19 Monitor and Tune Database Settings

Regularly monitor and adjust settings such as buffer pool size, cache size, and use tools like EXPLAIN and ANALYZE to identify bottlenecks.

2.20 Regularly Review and Refactor SQL

Periodic code reviews help identify performance issues; refactor complex queries into simpler, more efficient forms.

Wrong example

<code>SELECT u.name, (SELECT COUNT(*) FROM orders o WHERE o.uid = u.id) AS order_count FROM users u;</code>

Correct example

<code>SELECT u.name, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id = o.uid GROUP BY u.name;</code>

This refactored query uses a join and GROUP BY for better performance.