20 Essential MySQL Optimization Tips to Boost Query Performance

1. Avoid SELECT *

Use explicit column lists instead of SELECT * to save resources, reduce network overhead, and allow covering indexes for faster queries.

SELECT id,username,tel FROM user

2. Do Not Use OR in WHERE Clauses

Replace OR conditions with UNION ALL or separate queries to prevent index loss and full‑table scans.

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

3. Prefer Numeric Types Over Strings

Store values such as primary keys and gender flags as integers (e.g., INT, TINYINT) to avoid character‑by‑character comparisons and reduce storage.

4. Use VARCHAR Instead of CHAR

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

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

5. Replace NULL Checks with Default Values

Using a default value (e.g., age>0) can enable index usage and make the condition clearer.

SELECT * FROM user WHERE age>0

6. Avoid != or <> Operators

These operators often cause index invalidation; use alternative logic or rewrite the condition.

SELECT * FROM user WHERE salary<>5000

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

If the result set is the same, INNER JOIN is usually faster; keep the left table small when using LEFT JOIN.

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 Full Table Deletion

TRUNCATE TABLE

removes all rows faster and with less transaction‑log overhead than DELETE, but cannot be used with foreign‑key constraints.

10. Add LIMIT or Batch Deletions

Limiting deletions reduces the risk of accidental data loss, improves performance, and avoids long‑running transactions.

11. Use UNION ALL Instead of UNION

UNION ALL

simply concatenates result sets without deduplication, avoiding the extra sorting step required by UNION.

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

12. Batch INSERT Statements

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

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

13. Limit Table Joins and Index Count

Keep the number of joined tables and indexes per table generally below five to reduce compilation cost and memory usage.

14. Avoid Functions on Indexed Columns

Applying functions to indexed columns (e.g., DATE_ADD) disables the index; rewrite the condition to keep the column on the left side.

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

15. Use Composite Indexes Correctly

Order columns in ORDER BY to match the composite index sequence for optimal sorting 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;

16. Follow the Left‑most Prefix Rule

A composite index works if the leftmost columns are used; missing the leftmost column causes the index to be ignored.

ALTER TABLE employee ADD INDEX idx_name_salary (name,salary);
SELECT * FROM employee WHERE name='哪吒编程';

17. Optimize LIKE Queries

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

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

18. Analyze Execution Plans with EXPLAIN

Understand MySQL's join types (system, const, eq_ref, ref, range, index, all) and Extra information (Using index, Using where, Using temporary) to tune queries.

19. General Best Practices

Add comments to tables and columns.

Maintain consistent keyword case and indentation.

Backup important data before modifications.

Prefer EXISTS over IN where appropriate.

Avoid implicit type conversions in WHERE clauses.

Define columns as NOT NULL when possible.

Use a unified UTF‑8 charset to prevent encoding issues.

Avoid SELECT COUNT(*) without filters as it forces full‑table scans.

Minimize expression usage on indexed fields.

Reuse temporary tables wisely and drop them explicitly.

Do not index high‑cardinality duplicate data (e.g., gender).

Limit DISTINCT to necessary columns to reduce CPU load.

Keep transactions short to improve concurrency.

Use InnoDB as the default storage engine for its transactional and row‑level locking benefits.

Avoid cursors for large data sets; rewrite set‑based operations instead.

20. Additional Tips

Use EXPLAIN regularly, monitor query cost, and continuously refactor SQL to align with evolving data and workload patterns.