15 Essential SQL Optimization Tips to Boost Query Performance

Introduction

SQL optimization is a hot topic for both interview preparation and daily work because performance problems often require tuning SQL statements, which are cheaper to modify than application code.

This article shares fifteen practical tips for optimizing SQL queries.

1. Avoid Using SELECT *

Using

select *

retrieves all columns, wasting memory, CPU, and network bandwidth, and prevents the use of covering indexes, leading to costly table lookups.

<code>select * from user where id=1;</code>

Instead, select only the needed columns:

<code>select name, age from user where id=1;</code>

2. Use UNION ALL Instead of UNION

union

removes duplicates and requires sorting, which is slower. When duplicate rows are acceptable, use

union all

to avoid the extra work.

<code>(select * from user where id=1) union (select * from user where id=2);</code>

Optimized version:

<code>(select * from user where id=1) union all (select * from user where id=2);</code>

3. Small Table Drives Large Table (IN / EXISTS)

When a small table provides filter criteria for a large table, use

IN

if the left side is the large table and the right side is the small table; use

EXISTS

in the opposite case.

<code>select * from `order` where user_id in (select id from user where status=1);</code>

<code>select * from `order` where exists (select 1 from user where `order`.user_id = user.id and status=1);</code>

IN

is suitable when the left side is the large table and the right side is the small table.

EXISTS

is suitable when the left side is the small table and the right side is the large table.

4. Batch Operations

Inserting rows one by one inside a loop generates many round‑trips to the database and degrades performance.

<code>for (Order order : list) { orderMapper.insert(order); }</code>

Use a batch insert method instead:

<code>orderMapper.insertBatch(list);</code>

<code>insert into `order` (id, code, user_id) values (123,'001',100),(124,'002',100),(125,'003',101);</code>

Keep each batch size reasonable (e.g., ≤500 rows) to avoid long response times.

5. Use LIMIT

To fetch only the first row (e.g., a user's earliest order), add

LIMIT 1

instead of retrieving all rows and picking the first in code.

<code>select id, create_date from `order` where user_id=123 order by create_date asc limit 1;</code>

Similarly, add

LIMIT

to DELETE or UPDATE statements to protect against accidental large‑scale modifications.

<code>update `order` set status=0, edit_time=now(3) where id>=100 and id<200 limit 100;</code>

6. Too Many Values in IN

Large IN lists can cause timeouts. Limit the number of values or paginate the query.

<code>select id, name from category where id in (1,2,3,...,1000000);</code>

Better:

<code>select id, name from category where id in (1,2,3,...,100) limit 500;</code>

In application code, reject lists larger than a threshold (e.g., 500) or split them into multiple batches.

<code>if (ids.size() > 500) { throw new BusinessException("Maximum 500 IDs allowed"); }</code>

7. Incremental Query

Fetching the entire table for synchronization is inefficient. Query only new rows using the last processed ID or timestamp.

<code>select * from user where id > #{lastId} and create_time >= #{lastCreateTime} limit 100;</code>

8. Efficient Pagination

Using

LIMIT offset, count

with a huge offset forces MySQL to scan and discard many rows.

Instead, remember the last retrieved ID and query the next page with a WHERE clause:

<code>select id, name, age from user where id > 1000000 limit 20;</code>

Alternatively, use a BETWEEN range on an indexed column.

<code>select id, name, age from user where id between 1000000 and 1000020;</code>

9. Use JOIN Instead of Subquery

Subqueries may create temporary tables and be slower. Replace them with JOINs.

<code>select o.* from `order` o inner join user u on o.user_id = u.id where u.status=1;</code>

10. Limit Number of Joined Tables

Alibaba’s guidelines recommend no more than three tables in a single JOIN to keep the optimizer’s index selection manageable.

<code>select a.name, b.name, c.name from a inner join b on a.id=b.a_id inner join c on c.b_id=b.id;</code>

If more tables are needed, consider denormalizing frequently accessed fields.

11. Join Considerations (LEFT vs INNER)

INNER JOIN lets MySQL choose the smaller table to drive the larger one, usually yielding good performance.

<code>select o.id, o.code, u.name from `order` o inner join user u on o.user_id = u.id where u.status=1;</code>

LEFT JOIN should have the smaller table on the left side; otherwise, performance suffers.

<code>select o.id, o.code, u.name from `order` o left join user u on o.user_id = u.id where u.status=1;</code>

When using LEFT JOIN, ensure the left table is the smaller one; prefer INNER JOIN when possible.

12. Control Index Count

Too many indexes increase write overhead and storage. Alibaba recommends ≤5 indexes per table and ≤5 columns per composite index.

For high‑concurrency systems, keep indexes minimal, use composite indexes, and offload heavy search to specialized stores like Elasticsearch.

13. Choose Appropriate Column Types

Use

CHAR

for fixed‑length strings (e.g., phone numbers) and

VARCHAR

for variable‑length data (e.g., company names) to save space.

<code>alter table `order` add column code char(20) NOT NULL;</code>

<code>alter table `order` add column code varchar(20) NOT NULL;</code>

Prefer numeric types for numeric data, small types (BIT, TINYINT) for booleans or enums, and DECIMAL for monetary values.

14. Improve GROUP BY Efficiency

Filter rows before grouping to reduce the amount of data processed.

<code>-- Bad: group then filter
select user_id, user_name from `order` group by user_id having user_id <= 200;</code>

<code>-- Good: filter first then group
select user_id, user_name from `order` where user_id <= 200 group by user_id;</code>

Apply restrictive WHERE clauses before expensive operations like GROUP BY.

15. Index Optimization

First verify whether a query uses an index with

EXPLAIN

.

<code>explain select * from `order` where code='002';</code>

Common reasons for index loss include functions on indexed columns, type mismatches, and implicit conversions.

If MySQL chooses the wrong index, you can force the desired one with

FORCE INDEX

.