Mastering SQL Optimization: 15 Practical Tips to Supercharge Your Queries

SQL optimization is a core subset of database tuning, often highlighted in interview discussions where candidates claim expertise simply by mentioning "SQL optimization" or "MySQL optimization". In practice, most interviewees resort to the generic answer "add indexes".

1. Clever Use of LIMIT

When deep pagination forces MySQL to scan millions of rows before discarding them, replace SELECT ... LIMIT 100000, 30 with a range query that uses the primary key:

SELECT id, name, status, detail FROM product WHERE id > 100000 LIMIT 30;

This reduces execution time from seconds to milliseconds by avoiding full scans.

2. LIKE Pattern Placement

Place the wildcard on the right side of the pattern to allow index usage:

SELECT * FROM employee WHERE address LIKE '北京市通州区%';

Using a leading wildcard ( %) disables the index.

3. UNION vs UNION ALL

Use UNION ALL when duplicate removal is unnecessary, as it avoids the costly distinct and sorting steps:

SELECT product_id FROM orders WHERE id = 100
UNION ALL
SELECT product_id FROM orders WHERE id = 200;

4. STRAIGHT_JOIN

STRAIGHT_JOIN

forces the left table to drive the join order, which can be beneficial when the left table is significantly smaller.

SELECT * FROM t2 STRAIGHT_JOIN t1 ON t2.a = t1.a;

5. EXISTS vs IN

Choose IN when the subquery returns few rows and the outer table is large with an index; choose EXISTS when the outer table is small and the subquery is large.

6. Use TRUNCATE for Table Clearing

TRUNCATE

drops and recreates the table, which is much faster than DELETE because it bypasses row‑by‑row logging.

7. Batch Operations

Insert multiple rows in a single statement to reduce parsing and I/O overhead:

INSERT INTO student(name, sex, age) VALUES ('Tom', 1, 20), ('Tony', 1, 18);

8. Filter Early

Apply WHERE conditions before GROUP BY or ORDER BY to limit the data set early:

SELECT city, AVG(area) FROM country WHERE city IN ('beijing','shanghai') GROUP BY city;

9. Functions on the Right Side

Avoid wrapping indexed columns in functions; instead, apply the function to the constant side:

SELECT * FROM article WHERE title = LEFT('环球资讯', 4);

10. Minimal Data Types

Use the smallest appropriate data type (e.g., TINYINT UNSIGNED for 0‑200) to reduce storage and improve cache efficiency.

11. CHAR vs VARCHAR

Use CHAR for fixed‑length fields (ID numbers, phone numbers) and VARCHAR for variable‑length text, but beware of fragmentation on updates.

12. Minimal VARCHAR Length

Setting VARCHAR to the smallest length that fits the data improves sorting performance.

13. Index Strategy

Index frequently queried columns, avoid indexing columns that change often.

Create indexes on join, filter, group, and order columns.

Prefer high‑cardinality columns for indexes.

Use composite indexes following the left‑most prefix rule.

Avoid redundant indexes and full‑text indexes when a prefix index suffices.

Leverage covering indexes to satisfy queries without touching the table.

Ensure WHERE clauses on DELETE / UPDATE use indexes to prevent table locks.

14. FORCE INDEX

When the optimizer chooses a suboptimal index, explicitly force the desired one:

SELECT * FROM msg FORCE INDEX(idx_dest_src) WHERE dest='18736809673' AND src IN ('15144804019','18674654894');

15. Control Join Count

Limit the number of joined tables (commonly to three) to keep queries manageable; excessive joins often indicate schema issues or the need for a search engine like Elasticsearch.

These fifteen tips, illustrated with concrete SQL examples and performance measurements, provide a practical toolbox for developers to diagnose and accelerate MySQL queries.