8 Common SQL Mistakes That Are Destroying Your Database

Many database performance problems are not caused by high‑concurrency architectures but by seemingly harmless SQL habits that become fatal as tables grow and workloads increase.

1. SELECT *

Using SELECT * reads unnecessary columns, increasing I/O and network traffic, breaking when the schema changes, making covering indexes harder to use, and exploding join result sizes.

SELECT *
FROM orders
WHERE user_id = 1001;

A safer approach is to select only the required fields:

SELECT order_id, order_no, total_amount, created_at
FROM orders
WHERE user_id = 1001;

Why it matters

When the table is small the impact is invisible, but with large tables the extra page reads, larger result sets, heavier sorting/re‑lookup costs, and poorer cache hit rates dramatically slow queries.

2. WHERE with function on indexed column

SELECT *
FROM orders
WHERE DATE(created_at) = '2026-03-26';

Applying a function to an indexed column prevents the optimizer from using the index, often forcing a full table scan.

Rewrite as a range query that can use the index directly:

SELECT order_id, user_id, created_at
FROM orders
WHERE created_at >= '2026-03-26 00:00:00'
  AND created_at <  '2026-03-27 00:00:00';

Simple principle

Never transform the column; transform the constant. This lets the optimizer hit the index without scanning every row.

3. Fuzzy search starting with %

SELECT *
FROM users
WHERE name LIKE '%周%';

Leading wildcards make ordinary indexes unusable, causing a full‑table scan and row‑by‑row comparison—disastrous for millions of rows.

How to fix

For prefix matches, place the wildcard at the end:

SELECT user_id, name
FROM users
WHERE name LIKE '周%';

For full‑text needs, use a dedicated full‑text index or an external search engine (e.g., Elasticsearch).

4. Misusing IN , OR and join conditions

Complex OR or large IN lists can break the optimizer’s plan, leading to:

Failure to use ideal indexes

Poor join order

Huge intermediate result sets

CPU spikes

Better practices include:

For massive IN values, use a temporary table or batch‑driven approach.

Replace tangled OR with UNION ALL queries.

Filter small result sets before joining.

5. Deep pagination with LIMIT offset, size

SELECT *
FROM orders
ORDER BY id
LIMIT 100000, 20;

Offset pagination forces the database to scan and sort all preceding rows before discarding them, making later pages increasingly slow.

Better alternatives

Use cursor‑based pagination or “seek” pagination based on the last seen key:

SELECT order_id, id, created_at
FROM orders
WHERE id > 100000
ORDER BY id
LIMIT 20;

When ordering by time plus ID:

SELECT order_id, created_at
FROM orders
WHERE (created_at, id) > ('2026-03-26 08:00:00', 100000)
ORDER BY created_at, id
LIMIT 20;

Offset pagination is acceptable only for small tables, admin panels, or when accessing the first few pages.

6. UPDATE / DELETE without proper conditions

DELETE FROM orders;

Running such statements without a WHERE clause (or with an overly confident condition) can wipe data unintentionally.

Safer patterns:

Perform a SELECT first to verify the affected rows.

Apply a LIMIT when supported.

DELETE FROM logs
WHERE created_at < '2024-01-01'
LIMIT 1000;

Wrap modifications in a transaction and back up critical data before execution.

7. Blindly adding indexes

Teams often add indexes whenever a query feels slow, leading to storage overhead, slower writes, and more complex optimizer choices. Redundant or overlapping indexes (e.g., (user_id), (user_id, status), (user_id, status, created_at)) waste resources.

Proper indexing strategy:

Build indexes based on actual query patterns.

Inspect execution plans instead of guessing.

Follow the left‑most prefix rule for composite indexes.

Periodically clean up duplicate or unused indexes.

8. Ignoring the execution plan

Optimizing SQL based on intuition or copied snippets without checking EXPLAIN is akin to performing blind surgery on the database.

EXPLAIN SELECT order_id, user_id, total_amount
FROM orders
WHERE user_id = 1001
  AND created_at >= '2026-03-01 00:00:00';

Key fields to examine: type: access method quality. key: which index is used. rows: estimated rows scanned. Extra: presence of Using filesort or Using temporary.

Skipping this step leads to ineffective “optimizations” that may worsen performance.

Final checklist

Avoid SELECT * —select only needed columns.

Do not apply functions to indexed columns; use range conditions.

Never start a LIKE pattern with % unless using full‑text search.

Write clear IN / OR logic; consider UNION ALL or temporary tables.

Replace deep offset pagination with cursor‑based pagination.

Always include precise WHERE clauses for UPDATE/DELETE.

Add indexes deliberately, based on query analysis.

Inspect the execution plan before changing SQL.

These habits share one trait: they appear harmless in small‑scale testing but become painful failures in production. Mastering how the database thinks, when indexes fail, and the impact of each statement is the true path to reliable SQL development.