SQL Query Optimizations

Poorly written SQL queries can make your database slow, use too many resources, cause locking problems, and give a bad experience to users. Following best practices for writing efficient SQL queries helps improve database performance and ensures optimal use of system resources.

• Reduces query execution time and improves overall performance.
• Minimizes resource consumption while avoiding locking and blocking problems.

1. Use Indexes Wisely

Indexes help the database find data faster without scanning the whole table.

Example: Creating an index on customer_id if there are frequent queries on this column like the following query.

SELECT * FROM orders WHERE customer_id = 123;

Creating an index on customer_id makes this query much faster:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

The above query will run much faster if customer_id is indexed.

• Primary Index: Automatically created on the primary key; keeps values unique and fast to access.
• Secondary Index: Created on non-primary key columns to improve query performance. Need to be created manually.
• Clustered Index: Determines the arrangement of data in a table; only one per table. In some databases primary indexes are automatically clustered.
• Non-Clustered Index: Contains pointers to the data; multiple can exist per table

Indexing Guidelines

• Index columns used often in WHERE, JOIN, or ORDER BY clauses.
• Avoid too many indexes—they slow down INSERT, UPDATE, and DELETE.
• Check and monitor index usage regularly to keep queries fast.

2. Avoid SELECT *: Choose Only Required Columns

Using SELECT * can make queries slow, especially on large tables or when joining multiple tables. This is because the database retrieves all columns, even the ones you don’t need. It uses more memory, takes longer to transfer data, and makes the query harder for the database to optimize.

Avoid this:

SELECT * FROM products;

Use this instead:

SELECT product_id, product_name, price FROM products;

Benefits:

• Uses less memory and runs faster.
• Lets the database skip unneeded columns.
• Makes queries simpler and easier to read.

3. Limit Rows with WHERE and LIMIT

Fetching too many rows can make your query slow. Even if your app needs only 10 rows, the database might return thousands. Use WHERE to filter data and LIMIT to get only the rows you need.

Example:

SELECT name FROM customers WHERE country = 'USA'  ORDER BY signup_date DESCLIMIT 50;

Benefits:

• Makes queries faster and uses less CPU.
• Sends only the data you need, avoiding overload.
• Useful for testing and previewing results.

4. Write Efficient WHERE Clauses

The WHERE clause filters rows in a query, but how you write it affects performance. Using functions or calculations on columns can stop the database from using indexes, which makes the query slower.

Poor Example:

SELECT * FROM employees WHERE YEAR(joining_date) = 2022;

Drawback: Applying YEAR() to every row stops the database from using indexes.

Optimized Example:

SELECT * FROM employees WHERE joining_date >= '2022-01-01' AND joining_date < '2023-01-01';

Performance Tips:

• Don’t use functions on columns (YEAR(), LOWER(), etc.).
• Avoid calculations on columns (salary + 5000 = 100000).
• Write conditions so indexes can be used efficiently.

5. Use Joins Smartly

Join only the tables you need and filter data before joining. Use INNER JOIN instead of OUTER JOIN if you don’t need unmatched rows.

Example:

SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE o.amount > 100;

Benefits:

• Faster join processing.
• INNER JOIN combines rows based on a matching condition using ON, ensuring only related records are returned.
• Helps the database choose an efficient execution plan.

6. Avoid N+1 Query Problems

N+1 happens when you run one query to get a list, then run extra queries for each item. Fetch related data in a single query using JOINs instead.

Poor Approach:

SELECT * FROM users;
-- For each user: SELECT * FROM orders WHERE user_id = ?

Recommended Approach:

SELECT u.user_id, u.name, o.order_id, o.amount
FROM users u
JOIN orders o ON u.user_id = o.user_id;

Benefits:

• Fewer database calls.
• Faster response time.
• Reduces load on the database.

7. Use EXISTS Instead of IN (for Subqueries)

When you want to check whether a specific record exists in a table, using the EXISTS operator is often faster than using IN. This is particularly true when the subquery returns a large number of rows, because EXISTS stops searching as soon as it finds the first matching record, whereas IN has to process all the results before making the comparison.

Poor Approach:

SELECT name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

Recommended Approach:

SELECT name FROM customers
WHERE EXISTS (
  SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id
);

Benefits:

• Stops searching once a match is found.
• Uses less memory for large subqueries.
• Often faster and better optimized.

8. Avoid Wildcards at the Start of LIKE

Don’t start a LIKE pattern with % because it disables index use and causes a full table scan.

Poor Approach:

SELECT * FROM users WHERE name LIKE '%john';

Recommended Approach:

SELECT * FROM users WHERE name LIKE 'john%';

Benefits:

• Keeps searches fast and index-friendly.
• Reduces scanning overhead.

9. Use Query Execution Plan

Check how the database runs your query using EXPLAIN (MySQL/PostgreSQL) to see slow parts.

Example:

EXPLAIN SELECT * FROM orders WHERE user_id = 42;

Benefits:

• Helps identify full table scans
• Reveals if indexes are used
• Guides optimization decisions

10. Use UNION ALL Instead of UNION (if possible)

UNION removes duplicates, which adds sorting overhead. Use UNION ALL if duplicates don’t matter.

Poor Approach:

SELECT col FROM table1
UNION
SELECT col FROM table2;

Recommended Approach:

SELECT col FROM table1
UNION ALL
SELECT col FROM table2;

Benefits:

• Avoids unnecessary sorting.
• Merges results faster.
• Better for large datasets.