Course contentsShow
← Computer Science
Lesson 1707 of 2,87238. Database Transactions and IndexingFree lesson

Common Query Anti-Patterns

Identifying inefficient query patterns like N+1 queries, non-sargable predicates, and implicit conversions.

Common Query Anti-Patterns

What you'll learn: You'll discover three frequent query mistakes that silently destroy database performance—and how to spot them before they wreak havoc.

What Are Query Anti-Patterns?

After learning about query optimization, execution plans, and indexes, it's crucial to recognize patterns that undo all that good work. Query anti-patterns are common coding habits that seem innocent but cause catastrophic performance problems at scale. Think of them as termites eating away at your database's foundation—invisible until the damage is severe.

Three Critical Anti-Patterns

N+1 Queries

This happens when you fetch a list of items, then loop through each one issuing a separate query. If you retrieve 100 users, then query for each user's orders individually, you've made 1 + 100 = 101 queries instead of 2 (one for users, one JOIN for all orders). The query optimizer can't help you here—the problem is between queries.

Non-Sargable Predicates

A "sargable" predicate lets the database use indexes effectively (Search ARGument ABLE). Wrapping indexed columns in functions breaks this: WHERE UPPER(name) = 'ALICE' can't use an index on name, forcing a full table scan. Similarly, WHERE salary * 12 > 100000 prevents index use on salary.

Implicit Conversions

When you compare columns of different data types (WHERE user_id = '123' when user_id is an integer), the database must convert every row's value before comparing—again defeating indexes. The optimizer's statistics and cardinality estimates become useless.

The Common Thread

All three anti-patterns bypass the query optimizer's carefully chosen execution plans, forcing expensive full scans or repetitive operations that dwarf the cost of proper queries.

Key Takeaway: Anti-patterns like N+1 queries, non-sargable predicates, and implicit conversions sabotage indexes and optimizer decisions—learn to recognize and eliminate them through code reviews and EXPLAIN analysis.