"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:
- Full-Text Search (FTS): Built-in to most databases (like SQLite FTS5) for fast text searching.
- A Reversed Index: Store the reversed string in a separate column and index it.
Best Practices Checklist
- Avoid using functions on columns in the
WHEREclause. - Prefer range filters (>, <, BETWEEN) over date functions.
- Verify your data types match between your code and your schema.
- Avoid leading wildcards in
LIKEqueries if performance is critical. - Always use EXPLAIN to confirm your query is using an index.
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!