Top SQL Performance Anti-Patterns

1. Non-sargable predicates

Wrapping an indexed column in a function — WHERE YEAR(order_date) = 2026, WHERE LOWER(email) = 'a@b.com' — forces the database to compute that function for every row before it can compare the result, which blocks a standard index range scan. This is covered in full in Why SQL Queries Are Slow.

-- Anti-pattern
WHERE LOWER(email) = 'a@b.com'

-- Fix: store/compare case-normalized, or use a functional index
WHERE email = 'a@b.com'
-- or: CREATE INDEX idx_email_lower ON customers (LOWER(email));

2. SELECT *

SELECT * pulls more bytes than needed and can disqualify a covering index, forcing an extra row lookup per match. Name the columns you actually use — full reasoning in Indexes Explained With Real Examples.

3. The N+1 query problem

Fetching a list, then looping through it in application code to run one query per item, turns what should be one or two round trips into hundreds or thousands. This is often invisible in SQL logs viewed one query at a time — it only shows up as a pattern.

-- Anti-pattern: one query per order, run 200 times in a loop
-- SELECT * FROM line_items WHERE order_id = ?;  (x200)

-- Fix: one query for all orders
SELECT order_id, sku, qty
FROM line_items
WHERE order_id IN (101, 102, 103 /* ...200 ids */);

4. OR across different columns

A predicate like WHERE email = ? OR phone = ? asks the database to match either of two independently-indexed columns in one pass. Most optimizers can't satisfy this with a single efficient index scan, and often fall back to a full table scan or a costly combination of separate index scans merged together.

-- Anti-pattern
SELECT * FROM customers
WHERE email = 'a@b.com' OR phone = '5551234567';

-- Fix: UNION of two independently indexed lookups
SELECT * FROM customers WHERE email = 'a@b.com'
UNION
SELECT * FROM customers WHERE phone = '5551234567';

5. Implicit type casts

Comparing an indexed text column to a number, or an indexed integer to a string literal, forces the database to convert every stored value to a common type before comparing — behaving exactly like a function wrapped around the column, and blocking a normal index scan the same way.

-- Anti-pattern: order_number is stored as VARCHAR
WHERE order_number = 918204

-- Fix: match the literal's type to the column's actual type
WHERE order_number = '918204'

6. Leading wildcard searches

LIKE '%smith' can't use a standard B-tree index, because the index is sorted by the start of the string and a leading wildcard means the match could start with anything. A trailing wildcard, LIKE 'smith%', can use the index normally, since the index can jump straight to the "smith" range.

-- Anti-pattern: can't use a standard index
WHERE last_name LIKE '%smith'

-- Fix: trailing wildcard uses the index normally
WHERE last_name LIKE 'smith%'
-- For real substring search, use a trigram or full-text index instead

7. Unindexed join keys

Joining two large tables on a column with no index on either side forces the optimizer into a full scan of at least one of them, since there's no shortcut to match rows — this is exactly the join-algorithm decision covered in How SQL Optimizers Actually Work. Foreign key columns are frequently left unindexed by default in many databases, since the constraint itself doesn't automatically create one.

-- A foreign key constraint alone does NOT create an index
ALTER TABLE line_items
  ADD CONSTRAINT fk_order
  FOREIGN KEY (order_id) REFERENCES orders(id);

-- This is a separate, required step
CREATE INDEX idx_line_items_order_id ON line_items (order_id);

Before/after: three anti-patterns, one query

A search screen backed by an unindexed foreign key, a leading wildcard, and an implicit cast — all three fixed at once.

SELECT o.id FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.last_name LIKE '%smith'
  AND o.order_number = 918204;

Nested Loop
  -> Seq Scan on customers
       Filter: last_name LIKE '%smith'
  -> Seq Scan on orders

SELECT o.id FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.last_name LIKE 'smith%'
  AND o.order_number = '918204';

Nested Loop
  -> Index Scan on idx_customers_last_name
  -> Index Scan on idx_orders_order_number

~860x faster trailing wildcard, matched literal type, indexed join key — no schema redesign required