15 Proven SQL Optimization Techniques to Boost Query Performance

Avoid Using SELECT *

Selecting all columns with SELECT * often retrieves unnecessary data, consumes extra memory, CPU, and network I/O, and prevents the use of covering indexes, leading to costly table‑row lookups. Instead, explicitly list only the required columns, e.g.

SELECT name, age FROM user WHERE id = 1;

Prefer UNION ALL Over UNION

UNION

removes duplicates by sorting and comparing rows, which is CPU‑intensive. When duplicate removal is not needed, use UNION ALL to simply concatenate result sets, e.g.

(SELECT * FROM user WHERE id = 1) UNION ALL (SELECT * FROM user WHERE id = 2);

Drive Large Tables with Small Tables (IN vs EXISTS)

When a query involves a small lookup table, using IN (small table on the right) lets MySQL evaluate the subquery first and filter quickly. Conversely, EXISTS is better when the large table is on the left. Example:

SELECT * FROM `order` WHERE user_id IN (SELECT id FROM user WHERE status = 1);

SELECT * FROM `order` WHERE EXISTS (SELECT 1 FROM user WHERE order.user_id = user.id AND status = 1);

Batch Operations

Inserting rows one‑by‑one inside a loop incurs a round‑trip for each row. Use a batch‑insert method to send all rows in a single request, e.g. orderMapper.insertBatch(list); or a multi‑value INSERT statement.

Use LIMIT to Restrict Result Sets

When only the first matching row is needed, add LIMIT 1 to avoid scanning the entire result set, e.g.

SELECT id, create_date FROM `order` WHERE user_id = 123 ORDER BY create_date ASC LIMIT 1;

Control the Size of IN Lists

Very long IN lists can cause timeouts. Limit the number of values (e.g., 500) and paginate or split the request in application code, e.g.

SELECT id, name FROM category WHERE id IN (1,2,3,…,100) LIMIT 500;

Incremental Queries for Data Sync

To sync large tables incrementally, query only rows newer than the last processed ID or timestamp and limit the batch size, e.g.

SELECT * FROM user WHERE id > #{lastId} AND create_time >= #{lastCreateTime} LIMIT 100;

Efficient Pagination

Using LIMIT offset, count on huge tables forces MySQL to scan and discard the offset rows. Instead, paginate by the last seen primary‑key value:

SELECT id, name, age FROM user WHERE id > 1000000 LIMIT 20;

or use a BETWEEN range.

Replace Subqueries with Joins

Subqueries often create temporary tables. Converting them to joins can improve performance:

SELECT o.* FROM `order` o INNER JOIN user u ON o.user_id = u.id WHERE u.status = 1;

Limit the Number of Joined Tables

Having more than three tables in a join can cause the optimizer to choose inefficient plans and increase complexity. Keep join chains short or denormalize where appropriate.

Join Type Considerations

Use INNER JOIN when possible; MySQL will drive the larger table with the smaller one. If a LEFT JOIN is required, ensure the left table is the smaller one to avoid scanning many rows.

Control Index Count

Too many indexes increase write overhead and storage. Follow the guideline of ≤5 indexes per table, each with ≤5 columns. Prefer composite indexes over many single‑column indexes, and consider moving heavy search workloads to specialized stores (e.g., Elasticsearch).

Choose Appropriate Column Types

Use CHAR for fixed‑length strings, VARCHAR for variable length, DECIMAL for monetary values, and numeric types wherever possible. Smaller types (e.g., TINYINT, BIT) reduce storage and improve speed.

Improve GROUP BY Efficiency

Filter rows before grouping: place restrictive WHERE clauses before GROUP BY to reduce the amount of data that needs to be grouped, e.g.

SELECT user_id, user_name FROM `order` WHERE user_id <= 200 GROUP BY user_id;

Index Optimization and EXPLAIN

Use EXPLAIN to verify whether a query uses an index. If an index is not used, investigate common causes such as functions on indexed columns, type mismatches, or low selectivity. Force a specific index with FORCE INDEX when necessary.

Images illustrating execution plans and index usage: