13. Optimizing SQL queries

 

Optimizing SQL queries

1. Avoiding subqueries when JOINs are better.

2. Replacing OR with UNION when indexes aren’t used efficiently

Problem:

MySQL might only use index on one column (e.g., `dept_id`) Or it might just ignore indexes and scan the full table.

Bad: SELECT * FROM employees WHERE dept_id = 101 OR manager_id = 10;

 

Better with `UNION`:

SELECT * FROM employees WHERE dept_id = 101
UNION
SELECT * FROM employees WHERE manager_id = 10;

 

When NOT to Use UNION

If both conditions are on the same column:

WHERE status = 'ACTIVE' OR status = 'PENDING'

 

3. Use SELECT only the columns you need, it reduces memory, I/O, and network load

Bad: SELECT * FROM orders

Good: SELECT id, total_amount FROM orders

 

 4. Use LIMIT when fetching sample data,it prevents full table scans when only partial results are needed.

EX: SELECT * FROM products LIMIT 10

  

5. Avoid functions on indexed columns in WHERE clause

This disables index: SELECT * FROM users WHERE YEAR(created_at) = 2024;

Better (preserves index use): SELECT * FROM users WHERE created_at BETWEEN '20240101' AND '20241231';

  

6. Use EXISTS instead of IN for subqueries with large result sets

Slower

SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status = 'active');

 

Faster

SELECT * FROM orders WHERE EXISTS (
        SELECT 1 FROM customers WHERE status = 'active' AND customers.id = orders.customer_id
);

 

7. Avoid leading wildcards in LIKE

No index usage: WHERE name LIKE '%john'

Uses index: WHERE name LIKE 'john%'

 

8. Use proper JOINs (avoid unnecessary ones)

Join only the tables you need. Too many JOINs make the optimizer’s job harder and increase processing time.

 

9. Use covering indexes when possible

If a query can be answered using only index data (no need to access table rows), it’s much faster.

Index: (email)

SELECT email FROM users WHERE email = 'abc@example.com'; 

 

10. Avoid SELECT DISTINCT unless necessary

It requires extra sorting or hashing — can slow down queries.

 

11. Be careful with GROUP BY and ORDER BY 

Both are expensive operations. Make sure:

You have indexes on columns used in `ORDER BY` or `GROUP BY`

You don’t sort unnecessarily

 

12. Use indexed columns in JOIN conditions

Make sure both `orders.customer_id` and `customers.id` are indexed

SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;