MySQL Query Optimization Guidelines and Best Practices

1. Avoid SELECT * – Use explicit column lists to reduce resource consumption, network overhead, and enable index coverage.

SELECT * FROM user;

SELECT id, username, tel FROM user;

2. Do not use OR in WHERE clauses – It can invalidate indexes; prefer UNION ALL or separate queries.

SELECT * FROM user WHERE id = 1 OR salary = 5000;

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

3. Prefer numeric types over strings – Use INT/TINYINT for IDs and enumerations to speed up comparisons and reduce storage.

PRIMARY KEY (id INT), sex TINYINT(1) COMMENT '0=Female,1=Male';

4. Use VARCHAR instead of CHAR – Variable‑length fields save space and improve query speed.

`address` VARCHAR(100) DEFAULT NULL COMMENT '地址';

5. Char vs. VARCHAR2 – CHAR is fixed‑length, VARCHAR2 is variable; choose based on space vs. speed trade‑offs.

6. Replace NULL checks with default values – Improves index usage and query clarity.

SELECT * FROM user WHERE age > 0;

7. Avoid != or <> operators – They often cause full table scans; use alternative logic when possible.

8. Prefer INNER JOIN – Use LEFT/RIGHT JOIN only when necessary and keep the left table small.

9. Optimize GROUP BY – Filter rows before grouping to reduce work.

SELECT job, AVG(salary) FROM employee WHERE job IN ('develop','test') GROUP BY job;

10. Use TRUNCATE for table clearing – Faster and less log intensive than DELETE without WHERE.

11. Add LIMIT or batch deletes/updates – Reduces transaction size, lock contention, and CPU load.

DELETE FROM user WHERE age > 30 LIMIT 1000;

12. UNION vs. UNION ALL – Use UNION ALL when duplicate removal is not required for better performance.

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

13. Batch INSERT – Combine multiple rows into a single INSERT statement.

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

14. Limit the number of joins and indexes – Generally keep joins and indexes under five to avoid excessive compilation and memory overhead.

15. Do not apply functions on indexed columns – Functions like DATE_ADD on indexed fields prevent index usage.

SELECT * FROM user WHERE birthday >= DATE_ADD(NOW(), INTERVAL 7 DAY);

16. Composite indexes and leftmost rule – Indexes are effective when the query predicates use the leftmost columns.

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. Optimize LIKE patterns – Use right‑anchored patterns (e.g., 'prefix%') to allow index usage; avoid leading wildcards.

SELECT * FROM citys WHERE name LIKE '大连%';

18. Use EXPLAIN to analyze execution plans – Aim for REF or RANGE access types and avoid ALL scans.

19. Miscellaneous tips – Add comments to tables/columns, keep SQL formatting consistent, back up before destructive operations, prefer NOT NULL, unify character sets to UTF8, avoid unnecessary COUNT(*), limit large transactions, use InnoDB, and avoid cursors for large data sets.