How to do SQL Query Optimization: A Beginner’s Guide

SQL query optimization is a very important skill to improve query performances by reducing load on server. As they say, one shouldn’t pull a sword for doing needle’s work. Whether you are a beginner or a pro, optimizing SQL queries from time to time goes a long way in improving database performance.

SQL is one of the most widely used tools for storing, managing and updating records / data in databases. Overtime, this database becomes quite big as we keep adding more and more records. Imagine a database of nation-wide hotel chain where millions of users browse their app daily to book their favorite hotels. If we write poor SQL queries to interact with this ever-growing database, it will lead to slow performance due to increased server load.

Hence, it is very important to optimize SQL queries to improve efficiency by reducing the time it takes to run a query. This results in smooth user experience. For ex – we can either write 2+2=4 or ((2+(4/2)) = 2. Both are same yet the difference in complexity becomes huge as the database grows over time.

In this article, we will walk you through basic techniques to optimize SQL queries easily. Doesn’t matter if you are just starting or you’re an intermediate level coder, we will help you in understanding how to write more efficient queries for a smooth user experience. Let’s begin –

1. Understanding SQL Query Optimization and Execution –

Let’s first understand the sequence of operations in which a query is executed –

a. Parsing: First the SQL engine evaluates the syntax of the query for any errors.
b. Optimization: Then the query planner decides the most efficient way to execute the query.
c. Execution: The query is run as per query planner’s decision.

So by optimizing queries, you are helping in the 2nd step – optimization by simplifying the task of query planner, thereby reducing resource consumption and execution time.

2. Use Indexing Wisely for SQL Query Optimization –

SQL indexes are just like the index of any book. Imaging finding the required chapter in a book without the index and page number. Horrible task, right? Hence, indexes are of big help in SQL query optimization. Indexes help in locating a record without scanning the entire table.,

a. When to Use Indexes –

i. If you’re frequently performing operations like WHERE, JOIN, GROUP BY and ORDER BY, adding an index will help in locating the required rows faster. Ex- If you’re often searching for customers by their name, adding an index on name column will fasten up searches.

ii. Use indexes on primary and foreign keys.

iii. Use composite indexes for queries that filter by multiple columns. For ex- If you’re frequently filtering by city and age, add an index on both columns.

b. When NOT to Use Indexes –

i. Small tables do not really require indexing. SQL query will not face any issue in sorting through data in case of small tables.
ii. If a column is frequently getting updated, do not index it as updating indexes is costly.
iii. Avoid too many indexes; they consume storage and slow down basic queries.

3. Avoid SELECT * (Retrieve Only Required Columns) –

We often fetch the entire table by running SELECT*. This slows down queries, especially when tables are large. Hence we should only fetch the required columns. Let’s look at a couple of examples –

Bad Example: – “SELECT * FROM employees;”
Optimized Example: – “SELECT first_name, last_name, department FROM employees;”

In the second table, we are only fetching first name, last name and department columns from employees table instead from fetching the entire table which may put huge load on server.

4. Use Proper Data Types for Effective SQL Query Optimization –

Imagine storing 1-litre of water in a 30-litre bucket. That would waste space, right? Similarly, we should be prudent in selecting right data type for storing space. Let’s look at following good practices of SQL query optimization –

a. INT instead of BIGINT –

INT can hold numbers up to billions. So if you’re going to write only small numbers like number of employees in a company, INT should be used. We should use BIGINT only when we are expecting some extremely large numbers, like high traffic web browsing data.

b. VARCHAR(N) instead of TEXT –

TEXT is like an unlimited storage bin. Suppose we’re going to store email ids, VARCHAR(100) should be sufficient since email ids will most likely be <100 characters.

c. DATE or DATETIME instead of VARCHAR –

There are many benefits of using DATE / DATETIME for recording dates instead of VARCHAR as below –

i. For storing the same date (e.g. ‘2025-12-03’), DATE will take 3 bytes, DATETIME will take 8 bytes and VARCHAR(10) will take 10 bytes.

ii. SQL queries can effectively sort through DATE and DATETIME values, resulting in faster calculations.

iii. SQL has some super helpful functions like DATEDIFF / DATEADD( ) etc. With VARCHAR, these functions won’t work.

5. Optimize Joins –

As we know, Joins are complex operations. Here are some best practices –

a. Always use indexes on columns used in JOIN conditions.

b. If you don’t need all the records, get rid of non-matching ones by running INNER JOIN instead of OUTER JOIN.

c. Avoid joining too many tables at once.

Example:

“SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;”

6. Use WHERE Instead of HAVING for SQL Query Optimization –

HAVING is used to filter records after aggregation. But WHERE filters records before aggregation happens. That’s why using WHERE as much as possible will reduce the number of rows processed, resulting in much better SQL query optimization.

Let’s look at below examples –

Bad Example:

“SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING department = ‘Sales’;”

Optimized Example:

“SELECT department, COUNT(*)
FROM employees
WHERE department = ‘Sales’
GROUP BY department;”

In the first example, HAVING will filter data after grouping, which is inefficient for large datasets. However, WHERE will filter the data before grouping, resulting in faster response time.

7. Avoid Using OR in WHERE Clauses –

This is a common bad practice. When we use OR in WHERE conditions, the entire table will be scanned to look for the OR part. Thus, we need to use IN to specify the values we are looking for in our query. Let’s understand with the below example –

Bad Example:

“SELECT * FROM orders WHERE status = ‘Pending’ OR status = ‘Shipped’;”

Optimized Example:

“SELECT * FROM orders WHERE status IN (‘Pending’, ‘Shipped’);”

In the bad example, database will be scanned separately for ‘pending’ and ‘shipped’, whereas IN will be more efficient as it will search each row only once.

8. Use EXISTS Instead of IN for Subqueries –

Using EXISTS is generally faster than IN when checking for record existence.

Bad Example:

“SELECT * FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE active = 1);”

Optimized Example:

“SELECT * FROM employees e
WHERE EXISTS (
SELECT 1 FROM departments d WHERE d.id = e.department_id AND d.active = 1
);”

Here, the optimized example is better because EXISTS will stop searching as soon as a match is found. On the contrary, IN will evaluate all matching values from the subquery.

9. Limit the Number of Rows Retrieved –

Most of the time we will need only top 10, top 15, bottom 20 etc results instead of full table. Hence, limiting the no. of rows helps in reducing the processing time. We can use LIMIT to do it for SQL query optimization.

Example:

“SELECT * FROM orders ORDER BY order_date DESC LIMIT 10;”

Here, we first used ORDER BY with DESC to sort the results in descending order and then applied LIMIT 10 to show only top 10 results.

10. Optimize ORDER BY with Indexing

Sorting (ORDER BY) can be slow if there is no supporting index.

Bad Example:

“SELECT * FROM employees ORDER BY last_name;”

Optimized Example:

“CREATE INDEX idx_last_name ON employees(last_name);
SELECT * FROM employees ORDER BY last_name;”

11. Avoid Using Functions on Indexed Columns –

We should not use functions on indexed columns because it will reduce the efficacy of indexes. Look at the below examples –

Bad Example:

“SELECT * FROM users WHERE LOWER(email) = ‘test@example.com’;”

Optimized Example:

“SELECT * FROM users WHERE email = ‘test@example.com’;”

In the optimized version, we did not use the LOWER () function, which allowed the database to use index on email column which therefore resulted in faster query execution.

12. Use Connection Pooling for SQL Query Optimization –

Imagine a coffee shop where whenever a customer arrive, staff sets up equipment from the start, make coffee and then pack the equipment again. This would lead to very high serving time. Instead, the staff should keep the equipment ready so that each customer can get their coffee quickly.

Connection pooling works similarly in databases. Connection pooling keeps a pool of ready to use connections instead of creating a new connection every time a query is run.

13. Use Query Caching for SQL Query Optimization –

There are always many queries which we run quite frequently. With query caching, we can thereby store results of such frequently executed queries which results in reduced execution time. This becomes extremely helpful in reducing load on server, especially when complex queries are given for query caching. Databases like MySQL support query caching.

Example:

“SELECT SQL_CACHE * FROM products WHERE category = ‘Electronics’;”

Conclusion –

Hence with these examples, we can understand how critical optimizing SQL queries can be for enhancing database performance. By following best practices like indexing wisely, retrieving only required data, optimizing joins, and using proper filtering techniques, we can significantly improve query execution time.

We should also keep in mind that SQL optimization is an ongoing learning process. As our dataset grows, we must monitor and fine-tune queries from time to time for the best performance. Happy querying!

Also check out our article on How to Write Complex SQL Queries Easily

Please also feel free to explore official Microsoft guide on Educational SQL Resources