Boost MySQL Performance: 20 Proven SQL Optimization Techniques

1. Avoid SELECT *

Bad example: SELECT * FROM user Good example: SELECT id, username, tel FROM user Save resources and reduce network overhead.

Enable use of covering indexes, reducing table lookups.

2. Do not use OR in WHERE clauses

Bad example: SELECT * FROM user WHERE id=1 OR salary=5000 Good examples:

SELECT * FROM user WHERE id=1<br/>UNION ALL<br/>SELECT * FROM user WHERE salary=5000

SELECT * FROM user WHERE id=1;<br/><br/>SELECT * FROM user WHERE salary=5000;

Using OR may invalidate indexes, causing full table scans.

If the OR condition involves an unindexed column, the optimizer may still need to scan the whole table.

MySQL’s optimizer can still choose a full scan when OR is present, increasing cost.

3. Prefer numeric types over strings

Good practice:

Primary keys should use integer types such as int or tinyint.

For gender, use tinyint (0 for female, 1 for male) instead of VARCHAR.

String comparison requires character‑by‑character checks.

Numeric comparison is a single operation.

Strings increase storage and slow joins.

4. Use VARCHAR instead of CHAR

Bad example:

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

Good example:

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

VARCHAR stores only the actual length, saving space.

CHAR pads unused space with blanks.

Variable‑length fields improve query efficiency on small columns.

5. CHAR vs VARCHAR2 (Oracle)

CHAR has fixed length; VARCHAR2 stores variable length up to the defined maximum.

Example: storing "101" in CHAR(10) occupies 10 bytes, while VARCHAR2(10) uses only 3 bytes.

CHAR is slightly faster, but VARCHAR2 saves space; choose based on space‑vs‑speed trade‑off.

6. Use default values instead of NULL in WHERE

Bad example: SELECT * FROM user WHERE age IS NOT NULL Good example: SELECT * FROM user WHERE age>0 NULL checks can prevent index usage depending on MySQL version and cost.

Replacing NULL with a default value often enables index usage.

7. Avoid != or <> operators

Bad example:

SELECT * FROM user WHERE salary!=5000;<br/>SELECT * FROM user WHERE salary<>5000;

Reason: These operators may cause index loss; prefer using = or IN where possible.

8. Prefer INNER JOIN over LEFT/RIGHT JOIN when results are the same

INNER JOIN returns only matching rows, reducing result set size and improving performance. If LEFT JOIN is needed, keep the left table as small as possible.

9. Optimize GROUP BY by filtering before grouping

Bad example (filter after grouping):

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

Good example (filter before grouping):

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

Filtering first reduces the number of rows processed.

10. Use TRUNCATE instead of DELETE for emptying tables

TRUNCATE TABLE removes all rows quickly, uses fewer resources, and does not log each row deletion. DELETE logs each row and is slower. TRUNCATE cannot be used on tables referenced by foreign keys.

11. Add LIMIT or batch deletes/updates

Limiting the number of rows per statement reduces the risk of accidental full‑table deletions and can improve performance by avoiding long transactions and excessive locking.

12. Use UNION ALL instead of UNION when duplicate removal is not required

SELECT username, tel FROM user<br/>UNION<br/>SELECT departmentname FROM department;

Replace with UNION ALL for a simple concatenation without the sorting overhead.

13. Batch INSERT for better performance

Multiple single INSERT statements:

INSERT INTO user (id, username) VALUES (1, '编程');<br/>INSERT INTO user (id, username) VALUES (2, '妲己');

Batch INSERT:

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

Batching reduces transaction overhead.

14. Limit the number of joins and indexes (generally ≤5)

More joins increase compilation time and memory usage.

Each join creates a temporary table.

Complex joins often indicate poor schema design.

Too many indexes slow INSERT/UPDATE and increase storage.

15. Avoid built‑in functions on indexed columns

Bad example:

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

Good example (move function to constant side):

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

Using functions on indexed columns disables the index.

16. Composite indexes and left‑most rule

Create composite index:

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

Query using left‑most columns benefits from the index; omitting the leftmost column causes the index to be ignored.

17. Optimize LIKE queries

Bad: LIKE '%dalian' or LIKE '%dalian%' (no index).

Good: LIKE 'dalian%' uses index. For left‑wildcard searches, consider reverse indexes or full‑text search.

18. Use EXPLAIN to analyze execution plans

Common types (from best to worst): system, const, eq_ref, ref, range, index, all. Aim for ref or range. Look for Extra values like Using index, Using where, Using temporary to understand performance.

19. Additional best practices

Add comments to tables and columns.

Maintain consistent SQL formatting.

Backup before destructive operations.

Prefer EXISTS over IN where appropriate.

Avoid implicit type conversion in WHERE clauses.

Define columns as NOT NULL when possible.

Use UTF‑8 charset consistently.

Avoid SELECT COUNT(*) without filters.

Minimize expression use on indexed columns.

Limit temporary table creation and drop them explicitly.

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

Keep DISTINCT columns minimal.

Use InnoDB as the default storage engine.

Avoid cursors for large result sets.