This is the final post in my SQL Optimization series. In my earlier posts, I covered:
- Optimize SQL Queries for Performance using Indexing
- Optimize SQL Queries for Better Performance using Execution Plan
By now, we should have a solid understanding of why SQL optimization matters for your query to load faster and how to identify potential bottlenecks.
Writing an efficient SQL query is crucial for ensuring your database performs well, especially when dealing with large datasets. Well-written queries are not only faster but also reduce strain on system resources and improve user experience. In today’s post, I’ll focus on practical techniques for writing efficient SQL queries to achieve better performance.
(1) Selecting only the column you need
One common mistake is using SELECT *, which retrieves all columns from a table. Even the ones you don’t need. This increases memory usage, network transfer, and processing time.
Example of a Less Efficient Query
SELECT *
FROM customers;This query retrieves every column from the customers table, even if you only need a few.
Example of a More Efficient Query
SELECT customer_id, first_name, last_name, email
FROM customers;(2) Using the Where Clause to Filter Rows Early
One of the simplest ways to improve query performance is to filter rows as early as possible using the WHERE clause.
When you don’t filter your data, the database must scan and process every row in the table. Even if you only need a small portion of it.
Example of a Less Efficient Query
SELECT order_id, customer_id, order_date, total_amount
FROM orders;This query retrieves all orders from the table, even if you only need recent ones.
Example of a More Efficient Query
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE order_date >= '2025-01-01';Now the database only processes orders from 2025 onward.
Why This Improves Performance
- Reduces the number of rows scanned
- Lowers memory usage
- Decreases CPU processing time
- Allows indexes (if present on
order_date) to be used effectively
(3) Avoid using Functions in WHERE Clause
SQL functions like UPPER(), LOWER() and CONCAT()When applied to columns in the WHERE clause can prevent the database from using indexes efficiently. When possible, avoid these functions on indexed columns, as this will force a full table scan, which is less efficient.
Example of a Less Efficient Query
SELECT customer_id, first_name, email
FROM customers
WHERE LOWER(email) = 'john@example.com';Even if the email column is indexed, applying LOWER() forces the database to compute the function for every row before comparing values. This typically prevents the index from being used.
Example of a More Efficient Query
SELECT customer_id, first_name, email
FROM customers
WHERE email = 'john@example.com';Now the database can directly use the index on email to locate the matching row efficiently. In general, it’s a good practice to store data in a consistent format so you don’t need to transform it during querying.
Another Example with Dates
Example of a Less Efficient Query
SELECT order_id, order_date, total_amount
FROM orders
WHERE YEAR(order_date) = 2025;Even applying YEAR() to the order_date column prevents the use of an index on that column.
Example of a More Efficient Query
SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date >= '2025-01-01'
AND order_date < '2026-01-01';This version allows the database to use an index range scan on order_date.
Why This Improves Performance
- Allows index seeks instead of full table scans
- Reduces the number of rows evaluated
- Improves execution time on large tables
- Scales better as data grows
(4) Use LIMIT to Restrict the Number of Results
When you don’t need the entire dataset, use LIMIT (or TOP in some databases) to restrict the number of rows returned.
Returning fewer rows reduces memory usage, network transfer time, and overall query cost.
Example of a Less Efficient Query
SELECT order_id, customer_id, total_amount
FROM orders
ORDER BY order_date DESC;This query sorts and returns all rows in the orders table, even if you only need the most recent ones.
Example of a More Efficient Query
SELECT order_id, customer_id, total_amount
FROM orders
ORDER BY order_date DESC
LIMIT 10;Now the database only returns the 10 most recent orders.
Why This Improves Performance
- Reduces the number of rows returned
- Minimises memory usage
- Decreases sorting cost
- Improves response time (especially for dashboards and APIs)
If you only need a preview of data, always limit it.
(5) Avoid Using DISTINCT Unless Necessary
DISTINCT removes duplicate rows, but it requires additional processing such as sorting or hashing.
Example of a Less Efficient Query
SELECT DISTINCT country
FROM customers;If the country column already has an index, or the table design ensures minimal duplication, using DISTINCT may force extra sorting or grouping work.
Example of a More Efficient Query
SELECT country
FROM customers
GROUP BY country;It is always good to avoid deduplication entirely if your schema guarantees uniqueness. Distinct can also be avoided with proper filtering and joins.
Why Avoiding Unnecessary DISTINCT Helps
- Prevents extra sorting operations
- Reduces CPU usage
- Avoids unnecessary temporary memory usage
- Improves performance on large tables
The key idea: Only remove duplicates when you truly need to. If your table structure or filtering already ensures uniqueness, DISTINCT is just extra work.
(6) Using Joins Appropriately
Joins are powerful, but using the wrong type of join or joining unnecessary data can significantly impact performance. Inefficient joins can cause unnecessary row duplications, excessive memory usage and longer execution times.
Always choose the correct join type and only join tables when needed.
Example of a Less Efficient Query (Unnecessary Columns + Broad Join)
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;This query:
- Retrieves all columns from both tables
- Returns all matching rows
- Transfers more data than necessary
If you only need a few columns, this adds unnecessary overhead.
Example of a More Efficient Query (Select Only What You Need)
SELECT o.order_id, o.order_date, c.first_name, c.last_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;Now:
- Only required columns are selected
- Less data is processed and transferred
- Memory and network usage are reduced
Choosing the Correct Join Type
Using the wrong join type can also increase workload.
Example of a Less Efficient Query (Using LEFT JOIN Unnecessarily)
SELECT o.order_id, c.first_name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;If every order must have a valid customer, a LEFT JOIN is unnecessary and may produce extra processing.
Example of a More Efficient Query (Use INNER JOIN When Appropriate)
SELECT o.order_id, c.first_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;If matching rows are guaranteed, INNER JOIN is clearer and avoids handling unmatched rows.
Why This Improves Performance
- Reduces unnecessary data retrieval
- Minimises join processing
- Avoids extra row handling
- Improves clarity and maintainability
(7) Optimising Subqueries
Subqueries can be useful, but if not written carefully, they may execute repeatedly or process more data than necessary. This can hurt performance.
Whenever possible, rewrite subqueries to reduce repeated work or improve index usage.
Example of a Less Efficient Query (Correlated Subquery)
SELECT customer_id, first_name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);This is a correlated subquery. It runs once for every row in the customers table.
If the table is large, this can become expensive.
Example of a More Efficient Query Using JOIN
SELECT c.customer_id, c.first_name
FROM customers c
INNER JOIN orders o
ON o.customer_id = c.customer_id;Using a join allows the database optimizer to generate a more efficient execution plan and process the data in a set-based manner.
Why Optimising Subqueries Improves Performance
- Avoids repeated execution of correlated subqueries
- Reduces unnecessary sorting and grouping
- Encourages set-based operations instead of row-by-row processing
- Allows the optimiser to choose more efficient join strategies
SQL optimization isn’t about writing fancy or complicated queries. It’s about being intentional with what you ask the database to do.
At the end of the day, performance comes down to one simple idea: don’t make the database work harder than it needs to.
- Only select the columns you actually need.
- Filter your data as early as possible.
- Write queries that allow indexes to be used.
- Avoid unnecessary processing.
- And always think in sets and not row by row.
Also, keep in mind that your database schema and dataset will continue to grow as your business grows. What works well today may become a bottleneck in the future, so writing efficient queries from the start really matters.
Thanks for reading 🙂
