30 Practical SQL Optimization Tips with Examples

This guide provides a comprehensive list of SQL optimization techniques, each accompanied by example queries and rationale.

1. Avoid SELECT *

Query only the needed columns to reduce data transfer and enable index usage.

SELECT id, name FROM employee;

2. Use LIMIT 1 When Only One Row Is Needed

Limit the result set to stop scanning after the first matching row.

SELECT id, name FROM employee WHERE name = 'jay' LIMIT 1;

3. Avoid OR in WHERE Clauses

OR can cause index loss; rewrite using UNION ALL or separate queries.

SELECT * FROM user WHERE userid = 1
UNION ALL
SELECT * FROM user WHERE age = 18;

4. Optimize LIMIT Pagination

Large offsets are inefficient; use "where id > last_id" or order‑by with index.

SELECT id, name FROM employee WHERE id > 10000 LIMIT 10;

5. Optimize LIKE Statements

Place the wildcard at the end (e.g., '123%') to allow index usage.

SELECT userId, name FROM user WHERE userId LIKE '123%';

6. Avoid Functions on Indexed Columns

Using functions (e.g., DATE_ADD) on indexed columns disables the index.

SELECT userId, loginTime FROM loginuser WHERE loginTime >= DATE_SUB(NOW(), INTERVAL 7 DAY);

7. Avoid Expressions on Indexed Fields

Expressions like "age-1 = 10" prevent index usage; compare directly.

SELECT * FROM user WHERE age = 10;

8. Prefer INNER JOIN; Keep LEFT JOIN Input Small

INNER JOIN returns only matching rows and can be faster; for LEFT JOIN, filter the left table first.

SELECT * FROM (SELECT * FROM tab1 WHERE id > 2) t1 LEFT JOIN tab2 ON t1.size = t2.size;

9. Avoid != or <> in WHERE

These operators often prevent index usage; split into two range conditions.

SELECT age, name FROM user WHERE age < 18;
SELECT age, name FROM user WHERE age > 18;

10. Respect Composite Index Order (Left‑most Prefix)

When using a composite index, query columns in the defined order.

SELECT * FROM user WHERE userid = 10 AND age = 10;

11. Add Appropriate Indexes for WHERE/ORDER BY

Create combined indexes on columns used together in filters and sorting.

ALTER TABLE user ADD INDEX idx_address_age (address, age);

12. Use Batch Inserts

Insert many rows in a single statement to improve performance.

INSERT INTO user (name, age) VALUES (#{item.name}, #{item.age}), (#{item.name2}, #{item.age2}), ...;

13. Use Covering Indexes When Possible

If the index contains all needed columns, the query can be satisfied without accessing the table.

SELECT id, name FROM user WHERE userid LIKE '%123%';

14. Use DISTINCT Sparingly

DISTINCT adds sorting and de‑duplication overhead; apply only when necessary.

SELECT DISTINCT name FROM user;

15. Remove Redundant or Duplicate Indexes

Extra indexes increase write cost and optimizer overhead.

-- Keep only the composite index idx_userId_age, drop idx_userId
DROP INDEX idx_userId ON user;

16. Batch Delete/Update Large Datasets

Process large modifications in small chunks to avoid lock timeouts.

DELETE FROM user WHERE id < 500;
DELETE FROM user WHERE id >= 500 AND id < 1000;

17. Prefer Default Values Over NULL in WHERE

Using a default (e.g., 0) can enable index usage.

SELECT * FROM user WHERE age > 0;

18. Limit Table Joins to Five or Fewer

Too many joins increase compilation cost and indicate poor schema design.

19. Use EXISTS When the Outer Table Is Larger

EXISTS can be more efficient than IN when the inner result set is large.

SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE A.deptId = B.deptId);

20. Prefer UNION ALL Over UNION When Duplicates Are Impossible

UNION ALL avoids the extra sorting step.

SELECT * FROM user WHERE userid = 1
UNION ALL
SELECT * FROM user WHERE age = 10;

21. Keep Index Count Reasonable (≤5 per Table)

Excessive indexes slow INSERT/UPDATE operations.

22. Use Numeric Types for Numeric Data

Numeric columns are more efficient for storage and indexing than VARCHAR.

23. Avoid Indexing Low‑Cardinality Columns (e.g., gender)

Indexes on columns with many duplicate values often get ignored by the optimizer.

24. Return Only Needed Data to Clients

Paginate results or limit columns to reduce network load.

SELECT * FROM LivingInfo WHERE watchId = ? AND watchTime >= DATE_SUB(NOW(), INTERVAL 1 YEAR) LIMIT 20 OFFSET 0;

25. Use Table Aliases with Column Prefixes

Aliases improve readability in multi‑table queries.

SELECT m.name, d.deptName FROM member m INNER JOIN department d ON m.deptId = d.deptId;

26. Prefer VARCHAR/NVARCHAR Over CHAR/NCHAR

Variable‑length strings save space and improve query speed.

27. Filter Rows Before GROUP BY

Apply WHERE conditions to reduce the dataset processed by GROUP BY.

SELECT job, AVG(salary) FROM employee WHERE job IN ('president','management') GROUP BY job;

28. Quote String Literals in WHERE Clauses

Unquoted numbers compared to strings cause implicit conversion and index loss.

SELECT * FROM user WHERE userid = '123';

29. Use EXPLAIN to Inspect Query Plans

Run EXPLAIN to verify index usage and identify bottlenecks.

EXPLAIN SELECT * FROM user WHERE userid = 10086 OR age = 18;

By following these guidelines, developers can write more efficient SQL statements, reduce resource consumption, and improve overall database performance.