30 Real-World SQL Query Pitfalls and How to Fix Them

1. Slow Queries

Problem cause: Large data scans, missing indexes, or inefficient SQL statements.

Optimization strategy:

Add appropriate indexes.

Rewrite the query for efficiency.

Limit the number of rows returned.

Example:

-- Original query
SELECT COUNT(*) FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-03-01';

-- Optimized query
CREATE INDEX idx_order_date ON orders (order_date);
SELECT COUNT(*) FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-03-01';

2. Join Performance Issues

Problem cause: Joins across many tables without proper indexes.

Optimization strategy:

Ensure indexes exist on join columns.

Prefer INNER JOIN over other join types when possible.

Example:

-- Original query
SELECT o.*, c.name FROM orders o JOIN customers c ON o.customer_id = c.id;

-- Optimized query
CREATE INDEX idx_customer_id ON orders (customer_id);
SELECT o.*, c.name FROM orders o JOIN customers c ON o.customer_id = c.id;

3. Subquery Performance Issues

Problem cause: Subqueries may be executed repeatedly.

Optimization strategy:

Convert subqueries to joins or set‑based queries.

Optimize the subquery itself.

Example:

-- Original query
SELECT product_id, product_name FROM products WHERE price > (SELECT AVG(price) FROM products);

-- Optimized query
SELECT p.product_id, p.product_name FROM products p JOIN (SELECT AVG(price) AS avg_price FROM products) avg_table ON p.price > avg_table.avg_price;

4. Overusing Wildcard % in LIKE

Problem cause: Leading % disables index usage.

Optimization strategy:

Avoid leading % in LIKE patterns.

If unavoidable, consider full‑text search or other indexable methods.

Example:

-- Original query
SELECT * FROM products WHERE product_name LIKE 'abc%';

-- Optimized query
SELECT * FROM products WHERE product_name >= 'abc' AND product_name < 'abd';

5. Bulk Inserts/Updates

Problem cause: Large numbers of single‑row DML statements increase transaction overhead.

Optimization strategy:

Use bulk DML statements.

Disable or defer index updates during massive loads.

Example:

-- Original inserts
INSERT INTO orders (order_id, customer_id, order_date) VALUES (1, 1001, '2024-03-15');
INSERT INTO orders (order_id, customer_id, order_date) VALUES (2, 1002, '2024-03-15');
...

-- Optimized bulk insert
INSERT INTO orders (order_id, customer_id, order_date) VALUES (1, 1001, '2024-03-15'), (2, 1002, '2024-03-15'), ...;

6. Frequent Repeated Queries

Problem cause: Re‑executing identical queries wastes resources.

Optimization strategy:

Cache query results at the DB or application level.

Use prepared statements or stored procedures.

Example:

-- Original query
SELECT stock_quantity FROM products WHERE product_id = 123;

-- Optimized (cached) query
SELECT stock_quantity FROM cached_stock WHERE product_id = 123;

7. Overusing GROUP BY and Aggregations

Problem cause: Large datasets grouped without proper indexes or memory.

Optimization strategy:

Ensure suitable indexes on grouping columns.

Use summary tables or pre‑aggregated data.

Example:

-- Original query
SELECT product_id, AVG(amount) AS avg_amount FROM sales GROUP BY product_id;

-- Optimized using summary table
SELECT product_id, avg_amount FROM product_sales_summary;

8. Queries on Highly Repetitive Data

Problem cause: Redundant rows increase processing time.

Optimization strategy:

Use DISTINCT where appropriate.

Re‑evaluate data model to avoid duplication.

Example:

-- Original query
SELECT COUNT(customer_id) FROM orders;

-- Optimized query
SELECT COUNT(DISTINCT customer_id) FROM orders;

9. Overusing OR Conditions

Problem cause: Multiple ORs prevent index usage, causing full scans.

Optimization strategy:

Replace OR with IN or UNION.

Ensure each column in the OR has an index.

Example:

-- Original query
SELECT COUNT(*) FROM orders WHERE customer_id = 1001 OR product_id = 123;

-- Optimized using UNION
SELECT COUNT(*) FROM (SELECT * FROM orders WHERE customer_id = 1001 UNION SELECT * FROM orders WHERE product_id = 123) AS combined_orders;

10. Large‑Scale Pagination

Problem cause: LIMIT … OFFSET on big offsets is costly.

Optimization strategy:

Use cursor‑based pagination.

Consider application‑side or cache‑side pagination.

Example:

-- Original pagination
SELECT * FROM orders LIMIT 50 OFFSET 1000;

-- Optimized using cursor logic
SELECT * FROM orders WHERE order_id > (SELECT order_id FROM orders ORDER BY order_id LIMIT 1 OFFSET 1000) LIMIT 50;

11. Selecting Unnecessary Columns

Problem cause: Retrieving columns that are not needed wastes I/O.

Optimization strategy:

Project only required columns.

Avoid selecting large BLOB/CLOB fields unless needed.

Example:

-- Original query
SELECT * FROM orders;

-- Optimized query
SELECT order_id, order_date FROM orders;

12. Frequently Updated Tables

Problem cause: High update frequency leads to locking and performance loss.

Optimization strategy:

Minimize updates; move hot data to less‑contended tables.

Batch updates to reduce transaction overhead.

Example:

-- Original update
UPDATE login_logs SET last_login = NOW() WHERE user_id = 123;

-- Optimized batch update
UPDATE login_logs SET last_login = NOW() WHERE user_id IN (123, 124, 125);

13. Foreign Keys Without Indexes

Problem cause: Unindexed foreign‑key columns cause slow joins and deletes.

Optimization strategy:

Create indexes on foreign‑key columns.

Plan for foreign‑key indexing during schema design.

Example:

-- Create foreign key
ALTER TABLE orders ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers(id);

-- Add index for the foreign key
CREATE INDEX idx_customer_id ON orders (customer_id);

14. Large Query Batch Processing

Problem cause: Processing massive result sets in one go exhausts memory/CPU.

Optimization strategy:

Break the query into smaller batches.

Use cursors or pagination.

Example:

-- Original query
SELECT * FROM users;

-- Optimized using cursor
DECLARE cursor_name CURSOR FOR SELECT * FROM users;
OPEN cursor_name;
FETCH NEXT FROM cursor_name;
-- Process rows ...
CLOSE cursor_name;

15. Repeated Logic Without Stored Procedures

Problem cause: Duplicate business logic scattered across queries.

Optimization strategy:

Encapsulate reusable logic in stored procedures.

Stored procedures reduce network overhead and improve security.

Example:

-- Original ad‑hoc query
SELECT SUM(total_amount) FROM orders WHERE customer_id = 123;

-- Stored procedure
CREATE PROCEDURE CalculateOrderTotal(IN cust_id INT)
BEGIN
  SELECT SUM(total_amount) FROM orders WHERE customer_id = cust_id;
END;

16. Inappropriate Data Types

Problem cause: Using oversized or mismatched data types wastes space and slows operations.

Optimization strategy:

Choose the smallest suitable type (e.g., INT for integers).

Prefer numeric types for numeric data.

Example:

-- Original table definition
CREATE TABLE orders (order_id INT, quantity VARCHAR(10));

-- Optimized definition
CREATE TABLE orders (order_id INT, quantity INT);

17. Write‑Heavy Lock Contention

Problem cause: Many concurrent writes cause lock contention.

Optimization strategy:

Keep transactions short; commit/rollback promptly.

Batch writes to reduce lock duration.

Example:

-- Original transaction
BEGIN TRANSACTION;
INSERT INTO orders (order_id, customer_id, order_date) VALUES (1, 1001, '2024-03-15');
-- many more writes ...
COMMIT;

-- Optimized (batch) transaction
BEGIN TRANSACTION;
INSERT INTO orders (order_id, customer_id, order_date) VALUES (1, 1001, '2024-03-15');
COMMIT;
-- Subsequent batches processed separately

18. Frequent Use of Functions/Expressions

Problem cause: Complex functions and expressions increase CPU usage.

Optimization strategy:

Move calculations to the application or cache layer.

Store frequently used computed results in a column.

Example:

-- Original query
SELECT SUM(total_amount * (1 + tax_rate)) FROM orders;

-- Optimized: pre‑compute total_with_tax column
ALTER TABLE orders ADD COLUMN total_with_tax DECIMAL(10,2);
UPDATE orders SET total_with_tax = total_amount * (1 + tax_rate);
SELECT SUM(total_with_tax) FROM orders;

19. Poor Index Strategy

Problem cause: Ineffective or missing indexes cause full scans.

Optimization strategy:

Regularly review and tune indexes.

Choose index types that match query patterns (B‑Tree, hash, etc.).

Example:

-- Original index
CREATE INDEX idx_order_date ON orders (order_date);

-- Optimized covering index
CREATE INDEX idx_order_date_amount ON orders (order_date, total_amount);

20. Large UNION Operations

Problem cause: UNION forces sorting and deduplication of large result sets.

Optimization strategy:

Avoid UNION when possible; use JOIN or filtered subqueries.

Ensure each subquery returns a minimal, pre‑filtered set.

Example:

-- Original UNION
SELECT * FROM table1 UNION SELECT * FROM table2;

-- Optimized using JOIN
SELECT DISTINCT t1.* FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id;

21. Skewed Index Data Distribution

Problem cause: Uneven data distribution leads to hot index pages.

Optimization strategy:

Re‑organize or rebuild indexes periodically.

Consider partitioning or other structures for better balance.

Example:

-- Reorganize index
ALTER INDEX idx_username REORGANIZE;

22. Overusing Subqueries

Problem cause: Nested subqueries increase execution depth.

Optimization strategy:

Replace subqueries with joins.

Avoid subqueries in the SELECT list.

Example:

-- Original subquery
SELECT user_id, (SELECT MAX(order_date) FROM orders WHERE orders.user_id = users.user_id) AS latest_order_date FROM users;

-- Optimized join
SELECT u.user_id, MAX(o.order_date) AS latest_order_date FROM users u LEFT JOIN orders o ON u.user_id = o.user_id GROUP BY u.user_id;

23. Not Using Batch Operations

Problem cause: Executing many single‑row statements incurs connection overhead.

Optimization strategy:

Prefer batch DML (INSERT … VALUES (…), (…), … or UPDATE … WHERE id IN (…)).

Example:

-- Original single updates
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE orders SET status = 'shipped' WHERE order_id = 2;
-- ...

-- Optimized batch update
UPDATE orders SET status = 'shipped' WHERE order_id IN (1, 2, ...);

24. Overusing Memory Tables

Problem cause: Memory tables consume excessive RAM and may affect stability.

Optimization strategy:

Use memory tables only when truly beneficial.

For large or persistent data, prefer disk‑based tables.

25. Lack of Regular Statistics and Tuning

Problem cause: Stale statistics cause the optimizer to choose poor plans.

Optimization strategy:

Run ANALYZE periodically.

Follow with OPTIMIZE or REBUILD indexes as needed.

Example:

ANALYZE TABLE orders;
OPTIMIZE TABLE orders;

26. Wrong Database Engine

Problem cause: Selecting an engine that does not match workload characteristics.

Optimization strategy:

Choose InnoDB for transactional workloads, MyISAM for read‑heavy static data, etc.

Evaluate engine features against business needs.

Example:

-- Transactional table
CREATE TABLE orders (order_id INT, customer_id INT, order_date DATE) ENGINE=InnoDB;

-- Non‑transactional log table
CREATE TABLE logs (log_id INT, log_message TEXT) ENGINE=MyISAM;

27. Unnecessary Type Casting

Problem cause: Casting forces the engine to convert data per row.

Optimization strategy:

Store data in the correct type to avoid casts.

If casting is required, do it in the application layer.

Example:

-- Original with cast
SELECT * FROM orders WHERE DATE(order_date) = '2024-03-15';

-- Optimized without cast
SELECT * FROM orders WHERE order_date = '2024-03-15';

28. Long‑Running Transactions

Problem cause: Prolonged transactions hold locks and block others.

Optimization strategy:

Keep transactions short; commit/rollback early.

If long work is needed, break it into smaller batches.

Example:

-- Original long transaction
BEGIN TRANSACTION;
-- process many rows ...
COMMIT;

-- Optimized batch commits
BEGIN TRANSACTION;
-- process a subset of rows ...
COMMIT;
-- repeat for remaining rows

29. Unoptimized Stored Procedures

Problem cause: Stored procedures may contain inefficient SQL.

Optimization strategy:

Review and tune the SQL inside procedures.

Apply proper indexes and avoid unnecessary loops.

Example:

-- Original procedure
CREATE PROCEDURE CalculateOrderTotal(IN order_id INT)
BEGIN
  DECLARE total DECIMAL(10,2);
  SELECT SUM(amount) INTO total FROM order_details WHERE order_id = order_id;
  SELECT total;
END;

-- Optimized (ensure index on order_id in order_details)
CREATE PROCEDURE CalculateOrderTotal(IN order_id INT)
BEGIN
  DECLARE total DECIMAL(10,2);
  SELECT SUM(amount) INTO total FROM order_details WHERE order_id = order_id;
  SELECT total;
END;

30. Ignoring Server Configuration & Hardware

Problem cause: Inadequate CPU, memory, or disk resources limit throughput.

Optimization strategy:

Size hardware to match workload (more cores, RAM, SSDs).

Continuously monitor performance metrics and tune or upgrade as needed.

Example:

-- Check server status
SHOW STATUS;

-- After analysis, upgrade CPU cores, add RAM, or switch to faster storage.

By applying the appropriate strategy for each of these thirty scenarios—index tuning, query rewriting, batch processing, proper schema design, and hardware provisioning—developers can substantially improve SQL query performance and overall database efficiency.