SQL Optimization Tips and Best Practices

SQL Optimization Tips and Best Practices

This guide summarizes practical MySQL performance recommendations, illustrating common pitfalls and their correct alternatives.

1. Avoid SELECT * and specify fields

Bad example: SELECT * FROM user Good example: SELECT id, username, tel FROM user Benefits: reduces resource consumption, may enable covering indexes, and improves query speed.

2. Do not use OR in WHERE clauses

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

Use UNION ALL:

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

Separate queries:

SELECT * FROM user WHERE id=1

SELECT * FROM user WHERE salary=5000

Reason: OR often disables indexes, causing full‑table scans.

3. Prefer numeric types over strings

Use INT for primary keys, TINYINT for boolean flags (e.g., gender), because numeric comparison is faster and consumes less storage.

4. Use VARCHAR instead of CHAR

Bad example: address CHAR(100) DEFAULT NULL COMMENT '地址' Good example: address VARCHAR(100) DEFAULT NULL COMMENT '地址' Reason: VARCHAR stores actual length, saving space and improving query efficiency.

5. Char vs. VARCHAR2 differences

CHAR(10)

always occupies 10 bytes (padded with spaces), while VARCHAR2(10) stores only the actual length, reducing storage and I/O.

6. Replace NULL checks with default values

Bad: SELECT * FROM user WHERE age IS NOT NULL Good: SELECT * FROM user WHERE age > 0 Reason: Certain conditions ( !=, IS NULL) may cause the optimizer to skip indexes.

7. Avoid != or <> in WHERE

These operators can invalidate indexes, leading to full‑table scans.

8. Prefer INNER JOIN over LEFT/RIGHT JOIN when result sets are identical

Inner joins return only matching rows, usually requiring less processing.

9. Filter before GROUP BY

Bad pattern (filter after grouping):

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

Good pattern (filter before grouping):

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

10. Use TRUNCATE to empty tables

TRUNCATE TABLE

removes all rows faster than DELETE and uses fewer transaction‑log resources.

11. Add LIMIT or batch deletes/updates

Limiting the number of rows reduces the risk of accidental data loss and can improve execution speed.

12. Prefer UNION ALL over UNION

UNION

removes duplicates, requiring sorting and extra I/O; UNION ALL simply concatenates result sets.

13. Batch insert statements

Single‑row inserts:

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

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)

Too many joins increase compilation cost and temporary table usage; excessive indexes degrade insert/update performance.

15. Avoid functions on indexed columns

Bad:

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

Good:

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

16. Composite (multi‑column) indexes and left‑most rule

Create index:

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

Query using leftmost columns benefits from the index; missing the leftmost column causes the index to be ignored.

17. Composite index leftmost principle examples

Index on (name, salary). Query on name uses the index; query on salary alone does not.

18. Optimize LIKE patterns

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

Good: LIKE 'dalian%' (uses index). For leading wildcards, consider reversing the string and using a function index.

19. Use EXPLAIN to analyze execution plans

Key type values (system, const, eq_ref, ref, range, index, all) indicate plan quality; aim for ref or range. Common Extra flags: Using index, Using where, Using temporary.

20. Additional best practices

Add comments to tables/columns.

Maintain consistent SQL formatting and indentation.

Backup before modifying or deleting important data.

Prefer EXISTS over IN when appropriate.

Watch for implicit type conversions in WHERE clauses.

Define columns as NOT NULL to save space.

Implement soft‑delete (logical delete) instead of physical delete.

Use a unified UTF‑8 charset for databases and tables.

Avoid SELECT COUNT(*) without filters (full scan).

Do not apply expressions to indexed columns in WHERE.

Minimize creation/deletion of temporary tables; use SELECT INTO for large inserts.

Limit use of DISTINCT to necessary columns.

Avoid large transactions to improve concurrency.

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

Avoid cursors for large data sets (performance penalty).

Following these guidelines helps write efficient, maintainable, and reliable SQL code.