Top 20 MySQL Query Optimization Tips to Boost Performance

1. Avoid SELECT *

Use specific column names instead of SELECT * to save resources, reduce network overhead, and enable covering indexes.

SELECT id,username,tel FROM user

2. Do not use OR in WHERE

OR can cause index loss and full table scans. Prefer UNION ALL or separate queries.

SELECT * FROM user WHERE id=1 UNION ALL SELECT * FROM user WHERE salary=5000

3. Prefer numeric over string types

Use integer types for primary keys and tinyint for gender flags to avoid costly character comparisons.

4. Use VARCHAR instead of CHAR

VARCHAR stores actual length, saving space and improving query efficiency compared to fixed‑length CHAR.

`address` varchar(100) DEFAULT NULL COMMENT '地址'

5. CHAR vs VARCHAR2 differences

CHAR has fixed length and may be slightly faster; VARCHAR2 stores variable length, saving space but may cause row migration on updates.

6. Replace NULL with default values in WHERE

Using default values can allow index usage and clearer intent.

SELECT * FROM user WHERE age>0

7. Avoid != or <> operators

These operators often invalidate indexes, leading to full scans.

8. Prefer INNER JOIN over LEFT/RIGHT JOIN

When result sets are the same, inner joins are more efficient; keep left tables small if left joins are necessary.

9. Optimize GROUP BY

Filter rows before grouping to reduce processed data.

SELECT job,AVG(salary) FROM employee WHERE job='develop' GROUP BY job

10. Use TRUNCATE instead of DELETE for emptying tables

TRUNCATE is faster and uses fewer resources, but cannot be used with foreign key constraints.

11. Add LIMIT or batch deletes/updates

Limits reduce risk of accidental full deletions, improve performance, and avoid long transactions.

12. Prefer UNION ALL over UNION

UNION ALL simply concatenates results without sorting, saving time and disk usage.

SELECT username,tel FROM user UNION ALL SELECT departmentname FROM department

13. Batch INSERT for better performance

Insert multiple rows in a single statement to reduce transaction overhead.

INSERT INTO user (id,username) VALUES (1,'哪吒编程'),(2,'妲己');

14. Limit number of joins and indexes

Keep joins and indexes to around five to avoid excessive compilation time and memory usage.

15. Do not apply functions on indexed columns

Functions like DATE_ADD on indexed columns prevent index usage.

16. Use composite indexes correctly

Order columns in ORDER BY to match the index sequence for optimal performance.

CREATE INDEX IDX_USERNAME_TEL ON user(deptid,position,createtime);
SELECT username,tel FROM user WHERE deptid=1 AND position='java开发' ORDER BY deptid,position,createtime DESC;

17. Understand leftmost prefix rule

Composite indexes work when the leftmost columns are used in the query.

18. Optimize LIKE queries

Use right‑anchored patterns (e.g., LIKE 'prefix%') to allow index usage; avoid leading wildcards.

19. Use EXPLAIN to analyze execution plans

Types range from system (best) to all (full scan); aim for ref or range.

20. Additional best practices

Add comments to tables and columns.

Maintain consistent SQL formatting.

Backup before modifying or deleting important data.

Prefer EXISTS over IN where appropriate.

Ensure proper data types to avoid implicit conversions.

Define columns as NOT NULL when possible.

Use InnoDB engine for transaction support and row‑level locking.

Avoid cursors for large data sets.