SQL Anti-Patterns: The Silent Performance Killers in Your Queries

"But the query is correct! It returns the right data!"

As a database engineer, I hear this all the time. In the world of SQL, there is a massive difference between a query that is logically correct and one that is performant.

Most developers know about indexing, but many don't realize that they are writing queries that make those indexes completely useless. These are SQL Anti-Patterns—common ways of writing SQL that feel natural but force the database to do 100x more work than necessary.

Today, we're going to look at the three most common performance killers and how to fix them.

---

1. The SARGability Trap (Functions in WHERE)

SARGable stands for Search ARGument ABLE. A query is SARGable if the database engine can use an index to speed up the execution.

The most common way developers break SARGability is by wrapping a column in a function inside the WHERE clause.

The Anti-Pattern

Suppose you want to find all orders from the year 2023. You might write:

SELECT * FROM orders 
WHERE YEAR(order_date) = 2023;

Why it's slow: Even if you have an index on order_date, the database cannot use it. To evaluate YEAR(order_date), the database must first fetch every single row, apply the YEAR() function to it, and then check if the result is 2023. This is a Full Table Scan.

The Fix: Range Queries

Instead of modifying the column, modify your search criteria to a range:

Check the EXPLAIN tab: If you run these in the playground and check the execution plan, you'll see a SCAN for the first query and a SEARCH (index seek) for the second!

---

2. Implicit Conversions

Implicit conversion happens when you compare two different data types (e.g., a String and an Integer). The database will try to be "helpful" by converting one to the other behind the scenes.

The Anti-Pattern

Imagine your user_id is a VARCHAR (string) column, but you query it as an integer:

-- user_id is a string, but we pass an int
SELECT * FROM users WHERE user_id = 12345;

The Performance Hit: To make this comparison, the database must convert every user_id in the table from a string to an integer before checking the value. Just like the YEAR() function example, this destroys the index.

The Fix: Match Your Types

Always pass parameters that match the column data type exactly.

-- Perfect! No conversion needed.
SELECT * FROM users WHERE user_id = '12345';

---

3. The Wildcard Start (%term)

The LIKE operator is powerful, but where you put your % matters immensely.

• LIKE 'ABC%': SARGable. The database can jump to 'A' in the index and read forward. (Index Seek)
• LIKE '%ABC': NOT SARGable. The database has no idea where the match might start, so it has to read everything. (Full Table Scan)

The Fix

If you find yourself needing to search the end of strings frequently, consider:

1. Full-Text Search (FTS): Built-in to most databases (like SQLite FTS5) for fast text searching.
2. A Reversed Index: Store the reversed string in a separate column and index it.

---

4. SELECT * on Wide Tables

SELECT * is one of the most common performance anti-patterns because it rarely looks dangerous. The query still works. The result still looks correct. But the database may now read many more bytes than the application actually needs.

The Anti-Pattern

SELECT *
FROM orders
WHERE customer_id = 42;

If the UI only needs order_id, created_at, and total_amount, every extra column adds read, transfer, and memory cost for no product benefit.

The Fix

Be explicit:

SELECT order_id, created_at, total_amount
FROM orders
WHERE customer_id = 42;

This also increases the chance that the optimizer can use an index-only access path instead of touching the full table pages.

---

5. ORDER BY Without a Practical Limit

Sorting is expensive because the engine often has to hold a large working set in memory or spill it to disk.

The Anti-Pattern

SELECT id, created_at, total_amount
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC;

If the application only displays the first page, this asks the database to do more work than the user will ever see.

The Fix

SELECT id, created_at, total_amount
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Better again: pair the sort with an index that matches the real access pattern.

---

6. Repeating Work in Correlated Logic

Correlated subqueries and row-by-row checks are not always wrong, but they often hide repeated work.

The Anti-Pattern

SELECT c.customer_name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.total_amount > 100
);

Some engines optimize this well. Some data shapes do not. The danger is assuming that concise SQL is automatically cheap SQL.

The Fix

If performance becomes a problem, compare the correlated form against a join or a pre-filtered CTE, then inspect the execution plan instead of trusting intuition.

---

Diagnose Anti-Patterns with a Workflow, Not a Hunch

The shared theme across all these anti-patterns is hidden work. A practical review loop looks like this:

1. Look for query shapes that block index usage.
2. Run EXPLAIN to confirm whether the engine scanned, sorted, or repeatedly probed more rows than expected.
3. Rewrite one issue at a time.
4. Compare the new plan before moving on.

This matters because anti-pattern articles give you strong hypotheses, but the execution plan tells you whether that hypothesis is actually true for this query and this dataset.

---

Best Practices Checklist

• Avoid using functions on columns in the WHERE clause.
• Prefer range filters (>, <, BETWEEN) over date functions.
• Verify your data types match between your code and your schema.
• Avoid leading wildcards in LIKE queries if performance is critical.
• Always use EXPLAIN to confirm your query is using an index.

Related Articles

• Understanding Database Indexes for the indexing foundations behind SARGability and scan avoidance.
• How to Read SQL Execution Plans for validating whether an anti-pattern really caused the expensive node you are seeing.
• SQL Optimization Techniques for the broader workflow around joins, batching, pagination, and cost-based tuning.

Conclusion

SQL is a declarative language, which means we often forget about the "engine" humming under the hood. By following SARGable patterns and avoiding implicit conversions, you shift the work from the CPU to the Index, leading to queries that remain fast even as your data grows from thousands to millions of rows.

---

Ready to test your skills?

Try our Daily Challenges to practice writing optimized SQL queries every day!