15 Common MySQL Pitfalls and How to Avoid Them

15 Common MySQL Pitfalls

MySQL is widely used because it is free and performs well, but many developers easily fall into traps. Below are fifteen typical pitfalls and how to fix them.

1. Query without WHERE clause

Fetching all rows and processing them in memory can cause OOM on large tables. SELECT * FROM users; Use a specific condition to filter data first.

SELECT * FROM users WHERE code = '1001';

2. Missing indexes

Without indexes, queries become slower as data grows. SELECT * FROM orders WHERE customer_id = 123; Add an index on the filtered column.

CREATE INDEX idx_customer ON orders(customer_id);

3. Ignoring NULL values

COUNT(column) only counts non‑NULL values. SELECT COUNT(name) FROM users; Use COUNT(*) to count all rows.

SELECT COUNT(*) FROM users;

4. Wrong data types

Using overly large types like VARCHAR(255) wastes space.

CREATE TABLE products (
    id INT,
    status VARCHAR(255)
);

Choose appropriate types, e.g., tinyint for status flags.

CREATE TABLE products (
    id INT,
    status tinyint(1) DEFAULT '0' COMMENT 'status 1: active 0: inactive'
);

5. Deep pagination

Using LIMIT offset, count on high offsets degrades performance. SELECT * FROM users LIMIT 0,10; Solutions include remembering the last ID, sub‑queries, or inner joins.

5.1 Record previous ID

SELECT id, name FROM orders WHERE id > 1000000 LIMIT 100000,10;

5.2 Sub‑query

SELECT * FROM orders WHERE id IN (
    SELECT id FROM orders WHERE time > '2024-08-11' LIMIT 100000,10
);

5.3 Inner join

SELECT * FROM orders o1 INNER JOIN (
    SELECT id FROM orders WHERE create_time > '2024-08-11' LIMIT 100000,10
) o2 ON o1.id = o2.id;

6. Not using EXPLAIN

Running slow queries without analyzing the execution plan hides bottlenecks.

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

7. Improper character set

Using utf8 may cause emoji or Chinese character issues.

CREATE TABLE messages (
    id INT,
    content TEXT
) CHARACTER SET utf8mb4;

8. SQL injection risk

String concatenation allows injection.

String query = "SELECT * FROM users WHERE email = '" + userInput + "'";

Use prepared statements instead.

PreparedStatement stmt = connection.prepareStatement("SELECT * FROM users WHERE email = ?");
stmt.setString(1, userInput);

9. Transaction issues

Updating multiple tables without a transaction can leave data inconsistent.

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

Wrap the statements in a transaction.

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

10. Collation misconfiguration

Choosing a case‑insensitive collation while code performs case‑sensitive comparisons can cause bugs.

11. Overusing SELECT *

Fetching unnecessary columns wastes bandwidth and CPU. SELECT * FROM orders; Specify only required columns.

SELECT id, total FROM orders;

12. Indexes becoming ineffective

Indexes may be ignored due to various reasons; use EXPLAIN to diagnose.

13. Frequent schema changes

Altering tables or bulk updates during peak traffic can lock tables and degrade performance. Use online tools like Percona Toolkit or gh‑ost, and schedule heavy DDL during off‑peak hours.

14. No regular backups

Data loss can be catastrophic. Use mysqldump on a schedule.

mysqldump -u root -p database_name > backup.sql

15. Ignoring data archiving

Historical data slows queries. Archive old rows to a separate store and keep only recent data in the main table.