Boost MySQL Performance: 20 Proven SQL Optimization Tips

1. Avoid SELECT *

Never use SELECT * in production queries; specify required columns instead. SELECT * FROM user; Correct example: SELECT id, username, tel FROM user; Benefits: saves resources, reduces network overhead, and may enable covering indexes.

2. Do Not Use OR in WHERE

Using OR can invalidate indexes and cause full table scans. SELECT * FROM user WHERE id=1 OR salary=5000; Preferred solutions:

Use UNION ALL to split conditions.

Write separate queries for each condition.

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

3. Prefer Numeric Types Over Strings

Use integer types for primary keys and small enumerations (e.g., tinyint for gender).

4. Use VARCHAR Instead of CHAR

Variable‑length fields store only actual data, saving space and improving query speed.

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

5. Char vs VARCHAR2

CHAR

stores fixed length (padded with spaces); VARCHAR2 stores actual length up to the defined maximum. Choose based on space vs speed trade‑offs.

6. Replace NULL with Default Values in WHERE

SELECT * FROM user WHERE age>0;

Using default values often allows index usage and clearer intent.

7. Avoid != and <> Operators

These operators may cause index loss; prefer positive conditions or rewrite logic.

8. Prefer INNER JOIN

When result sets are identical, use INNER JOIN. If a LEFT JOIN is necessary, keep the left table as small as possible.

9. Optimize GROUP BY

Filter rows before grouping to reduce the amount of data processed.

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

10. Use TRUNCATE to Empty Tables

TRUNCATE TABLE

is faster and uses fewer resources than DELETE without a WHERE clause.

11. Add LIMIT or Batch Deletions

Limiting deletes reduces the risk of accidental full‑table loss and lowers lock contention.

DELETE FROM user WHERE id=123 LIMIT 1;

12. Prefer UNION ALL Over UNION

UNION ALL

simply concatenates result sets without the overhead of duplicate elimination.

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 table joins ≤5 and indexes ≤5 per table to avoid excessive compilation time and storage overhead.

15. Avoid Functions on Indexed Columns

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

Applying functions to indexed columns disables index usage.

16. Composite Index Order

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. Leftmost Prefix Rule for Composite Indexes

ALTER TABLE employee ADD INDEX idx_name_salary (name, salary);
SELECT * FROM employee WHERE name='哪吒编程';   -- uses index (leftmost column)
SELECT * FROM employee WHERE salary=5000;      -- index not used (leftmost column missing)

18. Optimize LIKE Queries

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

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

19. Use EXPLAIN to Analyze Execution Plans

Check type (system, const, eq_ref, ref, range, index, all) and Extra fields (Using index, Using where, Using temporary) to identify bottlenecks.

20. Additional Best Practices

Add comments to tables and columns.

Maintain consistent SQL formatting.

Backup data before destructive operations.

Prefer EXISTS over IN where appropriate.

Avoid implicit type conversions in WHERE clauses.

Define columns as NOT NULL when possible.

Use UTF‑8 charset uniformly.

Avoid SELECT COUNT(*) without filters.

Minimize expressions on indexed columns.

Use temporary tables judiciously.

Limit use of DISTINCT and large transactions.

Prefer InnoDB storage engine.

Avoid cursors for large data sets.