15 Proven SQL Optimization Techniques to Boost Query Performance

1. Avoid Using SELECT *

Selecting all columns with select * often retrieves unnecessary data, wastes memory, CPU, and network I/O, and prevents the use of covering indexes, leading to costly table lookups.

Bad example: select * from user where id=1; Good example: select name, age from user where id=1; Only fetch the columns you actually need.

2. Use UNION ALL Instead of UNION When Duplicates Are Acceptable

union

removes duplicates and requires sorting, which is CPU‑intensive. union all simply concatenates results.

Bad example:

(select * from user where id=1)
union
(select * from user where id=2);

Good example:

(select * from user where id=1)
union all
(select * from user where id=2);

Use union all unless you must eliminate duplicates.

3. Drive Large Tables with Small Tables (IN vs EXISTS)

When a small table drives a large one, use IN; when the large table drives the small one, use exists. This lets the optimizer choose the most efficient execution plan.

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

or

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

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 causes many round‑trips to the database. Use a batch insert method instead.

Bad example:

for (Order order : list) {
    orderMapper.insert(order);
}

Good example:

orderMapper.insertBatch(list);
-- or --
INSERT INTO order (id, code, user_id) VALUES
(123, '001', 100),
(124, '002', 100),
(125, '003', 101);

Keep each batch size reasonable (e.g., ≤500 rows) to avoid overwhelming the DB.

5. Use LIMIT to Retrieve Only Needed Rows

When you need the first record (e.g., a user's first order), add LIMIT 1 instead of fetching the whole result set and slicing in application code.

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

LIMIT can also protect update/delete statements from accidental mass changes.

6. Avoid Too Many Values in IN Clauses

Large IN lists can cause timeouts. Limit the number of IDs (e.g., 500) or split the query into multiple batches.

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

In code, validate the size of the ID list and throw an error if it exceeds the threshold.

7. Incremental Queries

When syncing data, query only new rows using a moving window on primary key or timestamp, and limit the batch size.

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

Store the maximum id and timestamp after each batch for the next run.

8. Efficient Pagination for Large Datasets

Offset‑based pagination (e.g., limit 1000000,20) forces MySQL to scan and discard millions of rows. Instead, paginate by the last seen id.

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

Alternatively, use BETWEEN on a unique indexed column.

9. Replace Subqueries with Joins

Subqueries often create temporary tables. Converting them to joins can be more efficient.

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

10. Limit the Number of Joined Tables

Alibaba’s guidelines suggest no more than three tables per join. Excessive joins increase optimizer complexity and may lead to nested‑loop joins with quadratic cost.

When possible, denormalize frequently accessed fields.

11. Choose the Right Join Type

Use INNER JOIN when you only need matching rows; use LEFT JOIN sparingly and ensure the left table is the smaller one.

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

-- left join example (left table should be small)
select o.id, o.code, u.name
from `order` o
left join user u on o.user_id = u.id
where u.status = 1;

12. Control Index Count

Too many indexes increase write overhead and storage. Keep the number of indexes per table ≤5 and fields per composite index ≤5.

Prefer composite indexes over many single‑column indexes, and consider moving heavy analytics to specialized stores (e.g., Elasticsearch, HBase).

13. Choose Appropriate Column Types

Prefer numeric types over strings, use the smallest type that fits the data, and select CHAR for fixed‑length strings (e.g., phone numbers) and VARCHAR for variable‑length text.

-- fixed length example
alter table `order` add column code char(20) NOT NULL;

-- variable length example
alter table `order` add column code varchar(20) NOT NULL;

Store monetary values with DECIMAL to avoid precision loss.

14. Optimize GROUP BY

Filter rows before grouping to reduce the amount of data the engine must aggregate.

Bad example:

select user_id, user_name from `order`
group by user_id
having user_id <= 200;

Good example:

select user_id, user_name from `order`
where user_id <= 200
group by user_id;

15. Index Optimization

First verify whether a query uses an index with EXPLAIN. If the index is not used, investigate common causes of index loss (e.g., functions on indexed columns, type mismatches). explain select * from `order` where code='002'; If necessary, force the optimizer to use a specific index with FORCE INDEX.