๐ง SQL Level 9 โ Query Performance & Optimization
Knowing how to write a SQL query is one skill. Knowing how to write a query that is correct, efficient, scalable, and easy to maintain is another.
As a Data Analyst, you may eventually work with tables containing:
๐ Millions of rows
๐ฆ Large transaction datasets
๐ฅ Millions of customers
๐ Years of historical data
A query that works perfectly on 10,000 rows may become extremely slow on 100 million rows. That's why understanding SQL performance matters.
๐น 1. What Is Query Optimization?
Query optimization means improving a query so that it:
โ Returns the correct result
โ Uses fewer resources
โ Processes less unnecessary data
โ Executes faster
โ Scales better as data grows
๐น 2. Select Only the Columns You Need
Avoid:
SELECT * FROM Orders;
when you only need:
SELECT Order_ID, Customer_ID, Sales FROM Orders;
SELECT * can:โข Read unnecessary columns
โข Increase data transfer
โข Make queries harder to maintain
โข Become problematic when tables change
For analytical work, explicitly selecting required columns is usually better.
๐น 3. Filter Data Early
Suppose you only need 2026 orders:
SELECT Customer_ID, SUM(Sales) AS Total_Sales
FROM Orders
WHERE Order_Date >= '2026-01-01'
AND Order_Date < '2027-01-01'
GROUP BY Customer_ID;
Filtering reduces the amount of data that later operations need to process.
๐น 4. Understand Indexes
An index is a database structure that can help locate rows more efficiently. Think of it like the index of a book.
Without an index: Database โ potentially scan many rows
With a useful index: Database โ locate relevant data more efficiently
Indexes can be especially useful for columns frequently used in:
WHERE, JOIN, ORDER BY, and sometimes GROUP BY.Example:
CREATE INDEX idx_orders_customer ON Orders(Customer_ID);
โ ๏ธ Index syntax and behavior vary by database.
๐น 5. Indexes Are Not Always Better
Indexes have costs. They can:
Consume storage, slow down
INSERT, UPDATE, DELETE, and require maintenance.Therefore:
Don't create indexes on every column. Database systems and workloads determine which indexes are useful.
๐น 6. Index Columns Used for JOINs
SELECT c.Customer_Name, o.Sales
FROM Customers c
JOIN Orders o ON c.Customer_ID = o.Customer_ID;
The join depends on:
Customer_ID.Appropriate indexing can improve join performance, especially for large tables. But the database optimizer decides whether an index actually provides a benefit.
๐น 7. Avoid Functions on Filtered Columns When Possible
Consider:
WHERE YEAR(Order_Date) = 2026
A range condition is often preferable:
WHERE Order_Date >= '2026-01-01' AND Order_Date < '2027-01-01'
Why?
Applying a function to every row can make it harder for some database systems to use an index efficiently. This is called maintaining sargability.
๐น 8. What Is Sargability?
A condition is generally considered sargable when the database can efficiently use an index to find matching rows.
Compare:
WHERE Order_Date >= '2026-01-01'
with:
WHERE YEAR(Order_Date) = 2026
The first form usually gives the optimizer more opportunity to use an index on
Order_Date.๐น 9. Use "EXPLAIN"
One of the most important tools for understanding query performance is:
EXPLAIN
SELECT * FROM Orders WHERE Customer_ID = 1001;