A query that takes eight seconds instead of eight milliseconds usually isn't a database problem. It's a query-writing habit problem, and most of the fixes take five minutes once you know what to look for. You don't need to be a database administrator or understand every internal detail of how a query engine works to get most of the available speed.
Here are eight wins that consistently make the biggest difference for anyone writing everyday SQL, explained without the jargon.
Quick Answer: Where should you start?
Stop using SELECT star and name only the columns you actually need. Add an index to any column you filter or join on regularly. Avoid a wildcard at the start of a LIKE pattern. Those three changes alone resolve most everyday SQL performance complaints, and none of them require special database privileges or deep internal knowledge to apply.
On this page
- Win 1: Stop Using SELECT Star
- Win 2: Index the Columns You Filter and Join On
- Win 3: Avoid a Wildcard at the Start of a LIKE Pattern
- Win 4: Filter Before You Join, Not After
- Win 5: Only Sort When You Actually Need To
- Win 6: Watch for Functions Wrapped Around Indexed Columns
- Win 7: Use EXISTS Instead of IN for Large Subqueries
- Win 8: Read the Execution Plan Before Guessing
- A Quick Before-You-Run-It Checklist
- Key Takeaways
- Frequently Asked Questions (FAQs)
Win 1: Stop Using SELECT Star
SELECT * is probably the single most common SQL performance mistake, and it's also one of the easiest habits to fix. It forces the database to read every column from disk, even the ones your query never actually uses, and it can prevent the database from using a fast index-only read that a more targeted query could take advantage of.
-- Reads all 35 columns from every matching row
SELECT * FROM orders WHERE order_date >= '2026-01-01';
-- Reads only the 3 columns actually needed
SELECT order_id, customer_id, order_total
FROM orders WHERE order_date >= '2026-01-01';On a table with dozens of columns, this alone can meaningfully cut down how much data the database has to move around, especially on wide tables with large text or binary columns you don't need for a given query.
Win 2: Index the Columns You Filter and Join On
An index works like the index at the back of a book. Instead of scanning every page to find a topic, the database jumps straight to the right spot. Without an index on a column you filter or join on frequently, the database has to check every single row to find matches, which gets painfully slow as a table grows.
CREATE INDEX idx_orders_customer_id ON orders(customer_id);You don't need to index everything. Focus on columns that show up regularly in WHERE clauses, JOIN conditions, and ORDER BY clauses, since those are where an index delivers the most consistent payoff.
Win 3: Avoid a Wildcard at the Start of a LIKE Pattern
A search pattern like '%son' forces the database to check every row individually, because it has no way to know how a match should start, so it can't use an index to jump ahead. A pattern like 'John%' behaves completely differently, since the database can seek directly to entries starting with "John."
-- Forces a full scan (slow)
SELECT * FROM customers WHERE last_name LIKE '%son';
-- Uses the index efficiently (fast)
SELECT * FROM customers WHERE last_name LIKE 'John%';If you genuinely need to search by suffix, like the last four digits of a card number, a computed reversed column with its own index can restore that index efficiency, though this is a more advanced fix worth reaching for only once you've confirmed you need it.
Win 4: Filter Before You Join, Not After
If you're joining two large tables and only care about a small slice of one of them, filtering that table down before the join, rather than joining everything and filtering afterward, gives the database less data to work with at every subsequent step.
-- Joins everything, then filters
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01';
-- Filters first with a subquery, joins less data
SELECT o.order_id, c.customer_name
FROM (SELECT * FROM orders WHERE order_date >= '2026-01-01') o
JOIN customers c ON o.customer_id = c.customer_id;In practice, modern query optimizers often rewrite the first version to behave like the second automatically. Still, understanding this pattern helps you reason about performance when the optimizer doesn't do it for you, particularly in more complex, multi-join queries.
Win 5: Only Sort When You Actually Need To
ORDER BY is one of the more expensive operations a database performs, since it often has to hold and rearrange the entire result set in memory. If you don't genuinely need sorted output, for example when the result is only being aggregated or counted afterward, dropping an unnecessary ORDER BY can save real time on large result sets.
If you do need sorting regularly on a specific column, indexing that column can help the database retrieve rows in something close to sorted order already, reducing how much sorting work is needed at query time.
Win 6: Watch for Functions Wrapped Around Indexed Columns
Wrapping an indexed column in a function inside your WHERE clause quietly disables the index for that column, because the database can no longer match the raw stored values directly against your condition.
-- Disables the index on order_date
SELECT * FROM orders WHERE YEAR(order_date) = 2026;
-- Keeps the index usable
SELECT * FROM orders
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';Rewriting the condition as a plain range comparison, as shown in the second query, lets the database use the index normally, while producing exactly the same result.
Win 7: Use EXISTS Instead of IN for Large Subqueries
When checking whether a row has a match in another, potentially large table, EXISTS generally outperforms IN, because it can stop checking as soon as it finds one match, while IN often has to build out the full list of subquery results first.
-- Can be slower on a large subquery
SELECT * FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);
-- Generally faster: stops at the first match
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);This difference matters most when the subquery involves a genuinely large table. On small subqueries, the difference is often negligible, so this is worth reaching for specifically when performance is already a concern.
Win 8: Read the Execution Plan Before Guessing
Instead of guessing which of these fixes to apply, most database tools let you view an execution plan, a breakdown of exactly how the database plans to retrieve your data and roughly how expensive each step is. Look specifically for a full table scan on a large table, which is usually the clearest sign that an index would help.
A Quick Before-You-Run-It Checklist
- Am I selecting only the columns I actually need, instead of SELECT star?
- Are the columns in my WHERE clause and JOIN conditions indexed?
- Does any LIKE pattern start with a wildcard when it doesn't need to?
- Am I filtering data down before joining large tables together?
- Is my ORDER BY actually necessary for this query's purpose?
- Is a function wrapped around a column that should be using an index?
- Would EXISTS be a better fit than IN for this subquery?
If your query is returning the correct data but the row count looks off after adding a join, that's a separate issue worth checking. Our guide on SQL JOIN returning wrong row count covers the most common causes of that specifically.
Key Takeaways
- Avoiding SELECT star and indexing your most-filtered columns resolve the majority of everyday SQL slowness.
- A wildcard at the start of a LIKE pattern disables index usage entirely, forcing a full scan.
- Wrapping an indexed column in a function inside a WHERE clause quietly disables that index. Rewrite as a plain comparison instead.
- EXISTS generally outperforms IN for large subqueries, since it can stop at the first match.
- Check the execution plan before and after optimizing, rather than guessing whether a change actually helped.
Frequently Asked Questions (FAQs)
What is the single biggest SQL performance mistake beginners make?
Using SELECT star instead of naming the specific columns needed. It forces the database to read every column from disk, even ones the query never uses, and prevents faster index-only reads that a more targeted query could take advantage of.
Do I need to understand indexes deeply to get performance benefits from them?
No. You get most of the practical benefit just by indexing the columns you filter and join on most often, particularly in WHERE clauses and JOIN conditions. Deeper index tuning matters more at scale, but a beginner can capture a large share of the available speedup with a few well-placed indexes.
Why does a leading wildcard in a LIKE search slow a query down so much?
A search pattern like percent-son forces the database to check every single row, because it cannot use an index to jump directly to matching rows when it does not know how the match starts. A pattern like John-percent can use the index efficiently, since the database can seek straight to rows starting with John.
Is query optimization something only database administrators need to worry about?
No. Any analyst who writes SQL regularly benefits from these habits, since slow queries waste your own time waiting for results and can slow down shared systems for other users. Most of the wins here require no special database privileges, just different habits in how you write everyday queries.
How can I tell if my query is actually slow before optimizing it?
Most database tools let you view an execution plan, which shows exactly how the database is retrieving your data and how long each step takes. Looking for a full table scan on a large table, or a much higher estimated cost than expected, is a reliable way to spot a query worth optimizing before you invest time fixing it.
Related Articles
External References
- DataCamp. "SQL Query Optimization: 15 Techniques for Better Performance." datacamp.com
- GeeksforGeeks. "Best Practices for SQL Query Optimizations." geeksforgeeks.org
- Devart. "SQL Query Optimization: 12 Useful Performance Tuning Tips and Techniques." blog.devart.com
0 Comments