🛢️ 18 SQL Anti-Patterns That Are Slowing Down Your Database

SQL performance problems often arise from common anti-patterns. Understanding these 10 mistakes will help you write faster, cleaner, and more maintainable SQL queries.

1. 🚫 SELECT * (Lazy Column Fetching)

Selecting all columns fetches unnecessary data and increases memory and network load. 

-- BAD
SELECT * FROM users;

-- GOOD
SELECT id, username FROM users;

✅ Always select only the columns you need.

2. 📦 Storing Comma-Separated Values

Storing multiple values in one column violates normalization and makes queries slow and complex. 

-- BAD
-- hobbies column: "reading,swimming,gaming"
SELECT * FROM users WHERE hobbies LIKE '%swimming%';

-- GOOD: normalized table
CREATE TABLE user_hobbies (
    user_id INT,
    hobby VARCHAR(50)
);

✅ Normalize data for better performance and maintainability.

3. 🔍 Using LIKE with Leading Wildcards

Leading wildcards prevent indexes from being used, causing full table scans. 

-- BAD
SELECT * FROM products WHERE name LIKE '%phone';

-- GOOD
SELECT * FROM products WHERE name LIKE 'phone%';

✅ Avoid leading wildcards in LIKE queries; consider full-text search for complex patterns.

4. 🧱 Ignoring Indexes

Queries on unindexed columns require full table scans, which slow down large tables. 

-- Create an index on frequently queried columns
CREATE INDEX idx_user_id ON orders(user_id);

✅ Index columns used in WHERE, JOIN, and ORDER BY clauses.

5. 🔄 Using Subqueries Instead of Joins

Subqueries are often less efficient and harder to optimize than joins. 

-- BAD
SELECT * FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE name='Sales');

-- GOOD
SELECT e.* FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Sales';

✅ Prefer joins for better performance and readability.

6. 🧮 Using COUNT(*) on Large Tables

Counting all rows scans the entire table and locks resources. 

-- BAD
SELECT COUNT(*) FROM large_table;

-- GOOD
SELECT COUNT(id) FROM large_table;

✅ Count indexed columns instead of *, especially on large tables.

7. 🧠 Using NULL for Missing Data

NULL values complicate calculations and logic. 

-- BAD
SELECT * FROM users WHERE age IS NULL;

-- GOOD: use default value or flag
SELECT * FROM users WHERE age = 0; -- or add 'is_unknown' flag

✅ Use default values or flags instead of NULL when possible.

8. 🔁 Repeatedly Writing the Same Query

Repetition increases maintenance effort and can slow down development. 

-- BAD
SELECT * FROM users WHERE status='active';
SELECT * FROM users WHERE status='inactive';

-- GOOD
SELECT * FROM users WHERE status IN ('active','inactive');

✅ Consolidate similar queries for cleaner, faster code.

9. 🧩 Not Using Constraints

Lack of constraints allows invalid data, making queries unreliable and slow. 

-- BAD: no foreign key
SELECT * FROM orders WHERE customer_id = 123;

-- GOOD
ALTER TABLE orders ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);

✅ Use primary keys, foreign keys, and unique constraints to enforce data integrity.

10. 🧪 Using Transactions Inefficiently

Long-running transactions lock resources and can cause deadlocks. 

-- BAD
BEGIN TRANSACTION;
UPDATE users SET status='active';
UPDATE orders SET status='processed';
COMMIT;

-- GOOD
BEGIN TRANSACTION;
UPDATE users SET status='active';
COMMIT;

BEGIN TRANSACTION;
UPDATE orders SET status='processed';
COMMIT;

✅ Keep transactions short and commit promptly.

11. 🛑 Overusing DISTINCT

Using DISTINCT forces the database to sort or hash all rows to remove duplicates, which can be very expensive on large tables. 

-- BAD
SELECT DISTINCT username FROM users;

-- GOOD: remove duplicates at insertion or use GROUP BY if aggregation is needed
SELECT username FROM users
GROUP BY username;

✅ Avoid DISTINCT when possible; fix duplicates at the data level.

12. ⚖️ Inefficient JOIN Order or Missing Indexes on Join Keys

Joining large tables without indexing the join columns can cause full table scans and slow queries. 

-- BAD
SELECT * 
FROM orders o
JOIN customers c ON o.customer_id = c.id;

-- GOOD: Ensure customer_id is indexed
CREATE INDEX idx_customer_id ON orders(customer_id);

✅ Always index join columns and check the execution plan for performance.

13. 🕳 Using Functions on Indexed Columns in WHERE

Functions like UPPER(), TRIM(), or DATE() on indexed columns prevent the database from using the index. 

-- BAD
SELECT * FROM users WHERE UPPER(username) = 'ADMIN';

-- GOOD
-- Store usernames in uppercase or use generated columns
SELECT * FROM users WHERE username = 'ADMIN';

✅ Avoid functions on indexed columns in WHERE clauses.

14. 🔢 Inefficient Pagination

Using OFFSET with large tables can cause the database to scan all previous rows. 

-- BAD
SELECT * FROM orders ORDER BY created_at LIMIT 50 OFFSET 100000;

-- GOOD: Keyset pagination
SELECT * FROM orders
WHERE created_at > '2025-09-01'
ORDER BY created_at
LIMIT 50;

✅ Use keyset pagination for large datasets.

15. 🏗 Not Using Batching for Inserts/Updates

Inserting or updating one row at a time creates excessive I/O and transaction overhead. 

-- BAD
INSERT INTO users (username) VALUES ('user1');
INSERT INTO users (username) VALUES ('user2');

-- GOOD: batch insert
INSERT INTO users (username) VALUES ('user1'), ('user2'), ('user3');

✅ Batch operations improve throughput and reduce disk overhead.

16. 🗑 Not Archiving Old Data

Large, unpartitioned tables slow down queries as the dataset grows. 

-- GOOD PRACTICE
-- Move historical orders to a separate table or partition
INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < '2023-01-01';
DELETE FROM orders WHERE created_at < '2023-01-01';

✅ Archive or partition old data to keep tables fast.

17. 🔒 Holding Locks Too Long

Long-running transactions block other queries, decreasing concurrency and causing deadlocks. 

-- BAD
BEGIN TRANSACTION;
UPDATE users SET status='active';
-- transaction remains open for other operations
COMMIT;

-- GOOD
BEGIN TRANSACTION;
UPDATE users SET status='active';
COMMIT;

✅ Keep transactions short and commit promptly.

18. 🌐 Ignoring Execution Plans

Writing queries without analyzing their execution plans can hide bottlenecks and inefficiencies. 

-- Example
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

✅ Use EXPLAIN or EXPLAIN ANALYZE to optimize queries and check if indexes are used.

Conclusion

Avoiding these 18 SQL anti-patterns improves performance, reduces database load, and prevents maintenance headaches. Always consider **indexes, normalization, and efficient query design** when writing SQL for production.

Labels: SQL, Database, Performance, Optimization, Best Practices, Queries

Comments

Post a Comment

Popular posts from this blog

🛠️ The Code Hut - Index

🌐 Distributed Systems Series 📖 Distributed Systems with Java (Series Intro) 📖 Intro to Distributed Systems ☁️ Cloud-Native Considerations ⚠️ Distributed System Anti-Patterns 🧪 Testing Strategies for Distributed Systems 🛡️ Advanced Fault Tolerance 🔗 Distributed Transactions Deep Dive 📈 Microservices Scaling Patterns 🔔 Monitoring & Alerting 📡 Event-Driven Architecture 🔐 Security in Distributed Systems 👀 Observability ⚡ Caching Strategies 📊 Consistency Models 🔍 Service Discovery & Load Balancing 💪 Resilience Patterns 💬 Communication Patterns 🧱 Fault Tolerance & Reliability 🔄 Transactions & Sagas 🤝 Consensus & Coordination 🔒 Concurrency & Locking 🏗️ Microservices & Architecture 🧩 Key Microservices Design Patterns 📐 Applying SOLID Principles ☕ Java & Design / Performance / Interview 🎨 Most Important Java Design Patterns 🚀 Java 17 Features You Need to Kn...
Read more

📘 Distributed Systems with Java — Series Index

Welcome to The Code Hut Distributed Systems series! This page serves as your central hub to explore all posts in the series. Each article dives into a key concept, mechanism, or pattern with practical Java examples. 📝 Intro to Distributed Systems Overview of distributed systems, why we need them, and what you'll learn in this series. 🔒 Concurrency & Locking Explore optimistic vs pessimistic locking and distributed locks in Java. 🤝 Consensus & Coordination Leader election, Raft, Paxos, and coordination patterns for distributed nodes. 💰 Transactions & Sagas Distributed transactions, two-phase commit, and Saga pattern in Java. ⚡ Fault Tolerance & Reliability Replication, partitioning, idempotency, and error handling in distributed systems. 📡 Communication Patt...
Read more

🔄 Distributed Transactions Deep Dive

Welcome back to The Code Hut Distributed Systems series! In this post, we’ll dive deeper into distributed transactions, advanced Saga patterns, and techniques to maintain consistency across microservices. Why Distributed Transactions Are Tricky In a distributed system, each service typically manages its own database. Coordinating multiple services to commit or roll back changes atomically is challenging because: Network failures can occur Partial failures can leave inconsistent state Global locking is expensive and reduces availability 1. Saga Pattern Deep Dive The Saga pattern breaks a transaction into a series of smaller, independent steps with compensating actions if something fails. Choreography: Services emit events that trigger the next step. Orchestration: A central coordinator tells each service what action to take next. 2. Example: Orchestrated Saga in Java // Orchestrator triggers a series of service calls public class OrderSagaOrche...
Read more