Best Practices for Writing Efficient & Optimized SQL Queries

Writing efficient SQL queries ensures faster execution, reduced server load, and better scalability. Here’s how to optimize your SQL queries like a pro.

1️⃣ Use SELECT Only What You Need 🎯

✅ Avoid SELECT * (retrieving all columns slows queries).
✅ Fetch only necessary columns to improve performance.

🔹 Bad Example:

sql
CopyEdit
SELECT * FROM users; 

🔹 Good Example:

sql
CopyEdit
SELECT id, name, email FROM users; 

2️⃣ Use Proper Indexing 📌

✅ Indexes speed up WHERE, JOIN, and ORDER BY operations.
✅ Use PRIMARY KEY, UNIQUE, and FOREIGN KEY indexes.
✅ Avoid indexing small tables or columns with high duplicate values.

🔹 Example (Creating an Index):

sql
CopyEdit
CREATE INDEX idx_user_email ON users(email); 

🔹 Check Existing Indexes:

sql
CopyEdit
SHOW INDEXES FROM users; 

3️⃣ Use WHERE Instead of HAVING 🚀

✅ WHERE filters before aggregation, HAVING filters after.
✅ Use HAVING only for aggregate functions.

🔹 Bad Example:

sql
CopyEdit
SELECT department, COUNT(*)  FROM employees  GROUP BY department  HAVING department = 'IT'; 

🔹 Good Example:

sql
CopyEdit
SELECT department, COUNT(*)  FROM employees  WHERE department = 'IT' GROUP BY department; 

4️⃣ Avoid Unnecessary Joins & Use EXISTS Instead of IN 🔄

✅ Minimize JOINs, as they slow down queries.
✅ Use EXISTS instead of IN for better performance on large datasets.

🔹 Bad Example (Using IN):

sql
CopyEdit
SELECT name FROM employees  WHERE department_id IN (SELECT id FROM departments WHERE name = 'IT'); 

🔹 Good Example (Using EXISTS):

sql
CopyEdit
SELECT name FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d.id = e.department_id AND d.name = 'IT'); 

5️⃣ Use LIMIT for Large Datasets 🔥

✅ Fetch only required rows instead of pulling all data.

🔹 Example:

sql
CopyEdit
SELECT * FROM orders ORDER BY order_date DESC LIMIT 100; 

6️⃣ Optimize JOINS & Choose the Right Type 🏎️

✅ Use INNER JOIN instead of LEFT/RIGHT JOIN when possible.
✅ Ensure JOIN keys are indexed.

🔹 Example:

sql
CopyEdit
SELECT e.name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.id; 

7️⃣ Use UNION ALL Instead of UNION (If Possible) 🚀

✅ UNION removes duplicates, making it slower.
✅ If duplicates aren’t an issue, use UNION ALL.

🔹 Bad Example:

sql
CopyEdit
SELECT name FROM employees WHERE role = 'Manager' UNION SELECT name FROM clients WHERE category = 'Premium'; 

🔹 Good Example:

sql
CopyEdit
SELECT name FROM employees WHERE role = 'Manager' UNION ALL SELECT name FROM clients WHERE category = 'Premium'; 

8️⃣ Use Stored Procedures for Repeated Queries 📦

✅ Stored procedures execute faster as they are precompiled.
✅ They reduce SQL injection risks.

🔹 Example (Creating a Stored Procedure):

sql
CopyEdit
CREATE PROCEDURE GetEmployee(IN emp_id INT) BEGIN     SELECT * FROM employees WHERE id = emp_id; END; 

🔹 Calling the Procedure:

sql
CopyEdit
CALL GetEmployee(101); 

9️⃣ Avoid SELECT N+1 Query Problem 🚨

✅ Fetch related data in a single query instead of multiple queries.

🔹 Bad Example:

sql
CopyEdit
SELECT * FROM departments; SELECT * FROM employees WHERE department_id = 1; SELECT * FROM employees WHERE department_id = 2; 

🔹 Good Example (Using JOIN):

sql
CopyEdit
SELECT d.name, e.name  FROM departments d LEFT JOIN employees e ON d.id = e.department_id; 

🔟 Optimize ORDER BY & GROUP BY

✅ Index the columns used in ORDER BY and GROUP BY for speed.
✅ Avoid ordering unindexed columns.

🔹 Bad Example:

sql
CopyEdit
SELECT name FROM employees ORDER BY salary; 

🔹 Good Example:

sql
CopyEdit
CREATE INDEX idx_salary ON employees(salary); SELECT name FROM employees ORDER BY salary; 

🚀 Bonus: Use Query Execution Plans for Optimization

🔹 Check how your query runs and optimize accordingly:

sql
CopyEdit
EXPLAIN SELECT * FROM employees WHERE department_id = 5; 

🎯 Final Thoughts

🔹 Use SELECT only required columns
🔹 Index frequently queried columns
🔹 Avoid HAVING when WHERE can be used
🔹 Use EXISTS instead of IN
🔹 Optimize JOINs and GROUP BY