Top 20 MySQL Optimization Tips to Boost Query Performance

1. Avoid SELECT *

Always specify the required columns instead of using SELECT * to reduce network traffic and enable index covering.

SELECT id, username, tel FROM user;

2. Do not use OR in WHERE

Replace OR conditions with UNION ALL or separate queries to prevent index loss.

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

3. Prefer numeric types over strings

Use integer primary keys and store enumerations as tinyint values (e.g., 0 for female, 1 for male) to speed up comparisons.

4. Use VARCHAR instead of CHAR

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

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

5. CHAR vs VARCHAR2

CHAR has fixed length and pads with spaces; VARCHAR2 stores variable length. CHAR can be slightly faster but wastes space; VARCHAR2 is more storage‑efficient.

6. Replace NULL with default values in WHERE

Using a default (e.g., age>0) often allows the optimizer to use indexes, whereas IS NOT NULL may cause full scans.

SELECT * FROM user WHERE age>0;

7. Avoid != or <> operators

These operators frequently invalidate indexes; prefer = with logical negation when possible.

8. Prefer INNER JOIN

If result sets are identical, use INNER JOIN. When using LEFT JOIN, keep the left table small.

9. Filter before GROUP BY

Apply WHERE conditions first, then group, to reduce the number of rows 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 logs less than DELETE, but cannot be used with foreign‑key constraints.

11. Add LIMIT or batch deletes

Limiting deletions reduces the risk of accidental data loss and can improve performance.

DELETE FROM user WHERE age>0 LIMIT 1000;

12. Use UNION ALL instead of UNION

UNION ALL

simply concatenates result sets without deduplication, avoiding costly sorting.

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 the number of joins and indexes

Keep joins and indexes to a reasonable number (generally ≤5) to reduce compilation cost and memory usage.

15. Avoid functions on indexed columns

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

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

16. Composite index order matters

When using a composite index, sort results according to the index column order for optimal 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;

17. Left‑most prefix rule

A composite index can be used if the query filters on the leftmost columns; otherwise the index is ignored.

SELECT * FROM employee WHERE name='哪吒编程';  -- uses (name, salary) index
SELECT * FROM employee WHERE salary=5000;   -- does NOT use the index

18. Optimize LIKE patterns

Use right‑anchored patterns ( LIKE 'prefix%') to allow index usage; left‑anchored patterns require reverse indexes or full scans.

19. Analyze with EXPLAIN

Check the execution plan types (system, const, eq_ref, ref, range, index, all) and Extra information (Using index, Using where, Using temporary) to identify bottlenecks.

20. Miscellaneous best practices

Add comments to tables and columns.

Maintain consistent SQL formatting and keyword case.

Back up 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 a unified UTF‑8 character set.

Avoid full‑table COUNT(*) without filters.

Minimize temporary table creation and drop them explicitly.

Limit use of DISTINCT to necessary columns.

Keep transactions small to improve concurrency.

Use InnoDB as the default storage engine.

Avoid cursors for large result sets.