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

Below are practical MySQL optimization recommendations gathered by the author, aimed at reducing resource consumption, improving query speed, and ensuring efficient database design.

1. Avoid SELECT *

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

SELECT * FROM user

Correct example:

SELECT id, username, tel FROM user

2. Do Not Use OR in WHERE Clauses

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

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

Correct approach using UNION ALL:

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

3. Prefer Numeric Types Over Strings

Store identifiers and enumerations 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 efficiency compared to fixed‑length CHAR.

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

5. CHAR vs VARCHAR2 Differences

CHAR has fixed length with space padding; VARCHAR2 stores only actual characters, saving space but may cause row migration on updates.

6. Replace NULL with Default Values in WHERE

Using default values can keep indexes usable and simplify conditions.

SELECT * FROM user WHERE age>0

7. Avoid != or <> Operators

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

8. Prefer INNER JOIN Over LEFT/RIGHT JOIN When Results Are Equivalent

INNER JOIN returns only matching rows and is generally more efficient.

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 logs than DELETE , but cannot be used with foreign‑key constraints.

11. Add LIMIT or Batch Deletions

Limiting deletions reduces the risk of accidental data loss and lowers transaction cost.

12. Prefer UNION ALL Over UNION

UNION ALL simply concatenates results without removing duplicates, avoiding costly sorting.

13. Batch Inserts

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 a reasonable number (generally ≤5) to avoid excessive compilation and memory usage.

15. Avoid Functions on Indexed Columns

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

16. Composite Indexes and Left‑most Rule

Composite indexes work when the leftmost columns are used in the query; otherwise, they may be ignored.

17. Optimize LIKE Queries

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

18. Use EXPLAIN to Analyze Execution Plans

Check the type and Extra fields to ensure queries use indexes (prefer ref or range ).

19. Additional Tips

Add comments to tables and columns.

Maintain consistent SQL formatting.

Backup data before destructive operations.

Prefer EXISTS over IN when appropriate.

Avoid implicit type conversions in WHERE clauses.

Define columns as NOT NULL when possible.

Use InnoDB as the default storage engine.

Avoid cursors for large data sets.

Following these practices can significantly improve MySQL query performance and overall database health.