SQL Optimization Best Practices: Avoid SELECT *, Use Specific Fields, Index Strategies, and Other Performance Tips

1. Avoid SELECT *

Use explicit column lists instead of SELECT * to save resources, reduce network overhead, and enable index covering.

SELECT * FROM user

SELECT id, username, tel FROM user

2. Do Not Use OR in WHERE

OR can cause index loss and full table scans; replace with 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

Store identifiers and flags as integers (e.g., INT, TINYINT) to reduce comparison cost and storage.

4. Use VARCHAR Instead of CHAR

VARCHAR stores variable-length data, saving space and improving query speed.

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

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

5. Replace NULL Checks with Default Values

Using default values (e.g., age>0) can allow index usage and avoid full scans.

SELECT * FROM user WHERE age IS NOT NULL

SELECT * FROM user WHERE age>0

6. Avoid != or <> Operators

These operators often invalidate indexes; use alternative logic when possible.

SELECT * FROM user WHERE salary!=5000

7. Prefer INNER JOIN Over LEFT/RIGHT JOIN When Possible

INNER JOIN returns only matching rows and is generally faster.

8. 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;

9. Use TRUNCATE for Table Clearing

TRUNCATE TABLE

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

10. Add LIMIT or Batch Deletions

Limiting deletions reduces risk of accidental data loss and improves performance.

11. Prefer UNION ALL Over UNION

UNION ALL

simply concatenates result sets without deduplication, avoiding costly sorting.

12. Batch Inserts

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

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

13. Limit Number of Joins and Indexes

Keep joins and indexes under five to avoid excessive compilation time and storage overhead.

14. Avoid Functions on Indexed Columns

Applying functions (e.g., DATE_ADD) to indexed columns disables index usage.

15. Composite Indexes and Leftmost Prefix

Use composite indexes wisely; the leftmost column must be present in queries for the index to be effective.

CREATE INDEX IDX_USERNAME_TEL ON user(deptid, position, createtime);

16. Optimize LIKE Queries

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

17. Analyze Execution Plans with EXPLAIN

Understand MySQL’s join types (system, const, eq_ref, ref, range, index, all) and Extra information to identify bottlenecks.

18. Additional Tips

Add comments to tables and columns.

Maintain consistent SQL formatting.

Backup data before destructive operations.

Prefer EXISTS over IN.

Define columns as NOT NULL when possible.

Use UTF‑8 charset uniformly.

Avoid full‑table COUNT(*) without conditions.

Minimize use of cursors and large transactions.

Always use InnoDB storage engine for transactional safety and row‑level locking.