Query Optimization Patterns
AdvancedCommon SQL anti-patterns — functions on indexed columns, SELECT *, implicit type casts, OFFSET pagination on large tables — prevent index use and degrade performance at scale.
Overview
Writing correct SQL is not the same as writing fast SQL. Sargability (Search ARGument ABLE) describes whether a predicate can use an index: applying a function to an indexed column makes it non-sargable because the index is built on raw values, not function outputs. Similarly, implicit type conversions force the database to cast and compare every row. SELECT * causes unnecessary column fetching, breaks covering indexes, and increases network payload. OFFSET pagination degrades linearly as the page number grows — keyset/cursor pagination avoids this. Each anti-pattern shown below has a direct, faster alternative.
Non-Sargable Predicates: Functions on Indexed Columns
Wrapping an indexed column in a function prevents the index from being used because the index stores raw values. Rewrite predicates so the function is applied to the constant, not the column.
-- NON-SARGABLE: function on indexed column → forces Seq Scan
-- created_at has an index, but YEAR() is applied to it
SELECT id, amount FROM orders
WHERE YEAR(created_at) = 2024; -- MySQL: index on created_at ignored
-- SARGABLE fix: use range predicate on the column itself
SELECT id, amount FROM orders
WHERE created_at >= '2024-01-01'
AND created_at < '2025-01-01'; -- ✓ Index Scan on created_at
-- Another common non-sargable pattern: implicit type conversion
-- orders.user_id is INT, but a string is passed
SELECT * FROM orders WHERE user_id = '42'; -- cast forces full scan in some DBs
-- Fix: match the data type
SELECT * FROM orders WHERE user_id = 42; -- ✓ no cast needed
-- Function on filter column — email has an index
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com'; -- index ignored
-- Fix: expression index OR rewrite
CREATE INDEX idx_email_lower ON users (LOWER(email));
-- OR: store emails in lowercase at write timeSELECT * and OR Condition Anti-Patterns
SELECT * prevents covering index optimizations, selects unused columns, and breaks application code when columns are added or reordered. OR conditions can prevent the optimizer from using a single index — rewrite as UNION ALL when each branch can use its own index.
-- Anti-pattern: SELECT *
SELECT * FROM orders WHERE user_id = 42;
-- Fetches all columns including large blobs; breaks covering index
-- Fix: select only needed columns
SELECT id, amount, status, created_at FROM orders WHERE user_id = 42;
-- OR bypassing index (when branches span different indexed columns):
SELECT * FROM orders
WHERE user_id = 42 OR product_id = 99; -- may force Seq Scan in MySQL
-- Fix: UNION ALL (each branch uses its own index)
SELECT id, amount FROM orders WHERE user_id = 42
UNION ALL
SELECT id, amount FROM orders WHERE product_id = 99;OFFSET Pagination vs Keyset (Cursor) Pagination
OFFSET N skips N rows by reading and discarding them — page 1000 of 20 items per page still reads 20 000 rows. Keyset pagination uses a WHERE clause on the last seen values and is O(log n) regardless of page depth.
-- Anti-pattern: OFFSET pagination — gets slower as page grows
SELECT id, name, email, created_at
FROM users
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000; -- reads + discards 100000 rows!
-- Fix: keyset (cursor) pagination — always fast
-- Client keeps track of (created_at, id) from the last row of previous page
SELECT id, name, email, created_at
FROM users
WHERE (created_at, id) < ('2024-03-15 10:23:00', 48291) -- cursor values
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- With covering index on (created_at DESC, id DESC) INCLUDE (name, email)
-- this is an Index Only Scan regardless of page depth
-- For simple numeric IDs: even simpler keyset
SELECT id, name FROM users
WHERE id < 48291 -- last seen id
ORDER BY id DESC
LIMIT 20;Key Points to Remember
- 1A predicate is sargable if it allows the database to binary-search the index; applying a function to the column makes it non-sargable.
- 2Rewrite date range filters as BETWEEN or >= / < on the raw column, never as YEAR(col) = 2024.
- 3Implicit type conversion (string to int, int to varchar) causes full scans — always match parameter types to column types.
- 4SELECT * prevents covering index use, increases network payload, and is fragile against schema changes.
- 5OR across differently-indexed columns may degrade to a seq scan — UNION ALL lets each branch use its own index.
- 6OFFSET pagination is O(n) in the offset value; keyset pagination is O(log n) and scales to millions of rows.
Interview Questions
Sign in to ask AriaWhat is a sargable predicate? Give an example of converting a non-sargable predicate to a sargable one.
Why is OFFSET pagination slow on large tables and what is the better alternative?
How does an implicit type conversion prevent index usage?
A query with OR on two different columns is slow. How would you rewrite it?
Ask Aria about Query Optimization Patterns
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.