SQL Query Optimization Assignment
Section 1: Conceptual Questions
1. What is the purpose of the EXPLAIN keyword in MySQL?
EXPLAIN helps visualize how MySQL executes a query by showing the execution plan –
including join types, used indexes, and table scan details. It’s used to detect bottlenecks and
optimize query performance.
2. Why should we avoid using functions like YEAR(), UPPER() in WHERE clauses on
indexed columns?
Using functions on indexed columns prevents index usage, causing full table scans. Avoiding
them allows indexes to work effectively.
3. Define a covering index and explain how it improves query performance.
A covering index includes all columns required by a query, so the engine can retrieve data
directly from the index without reading the full table.
4. What are the differences between a single-column index and a composite index?
Single-column index indexes one column, whereas a composite index covers multiple
columns in a left-to-right manner, improving performance for multi-column filtering.
5. When should you consider deleting an index from a table?
Delete an index if it’s unused, redundant, or negatively impacts write performance (inserts,
updates, deletes).
Section 2: Query Optimization Tasks
Task 1: Function Optimization
Original Query:
SELECT * FROM employees WHERE UPPER(city) = 'DELHI';
Optimized Query:
SELECT * FROM employees WHERE city = 'DELHI';
Index Added:
CREATE INDEX idx_city ON employees(city);
Explanation:
Stored all cities in uppercase at insert/update time to avoid using UPPER() in query,
allowing index usage.
Task 2: Join Optimization
Original Query:
SELECT o.OrderID, c.FirstName, p.PaymentType FROM orders o
JOIN customers c ON o.CustomerID = c.CustomerID JOIN payments
p ON o.PaymentID = p.PaymentID;
Optimized Query:
SELECT o.OrderID, c.FirstName, p.PaymentType FROM orders o
JOIN customers c ON o.CustomerID = c.CustomerID JOIN payments
p ON o.PaymentID = p.PaymentID;
Index Added:
CREATE INDEX idx_orders_customerid ON orders(CustomerID);
CREATE INDEX idx_orders_paymentid ON orders(PaymentID);
Explanation:
Indexes on CustomerID and PaymentID help optimize join performance by reducing table
scans.
Task 3: Filtering Without Blocking Indexes
Original Query:
SELECT * FROM orders WHERE YEAR(orderDate) = 2023;
Optimized Query:
SELECT * FROM orders WHERE orderDate BETWEEN '2023-01-01'
AND '2023-12-31';
Index Added:
CREATE INDEX idx_orderdate ON orders(orderDate);
Explanation:
Rewriting query without YEAR() enables index usage on orderDate column.
Task 4: Aggregation Optimization
Original Query:
SELECT c.FirstName, COUNT(*) AS order_count FROM customers c
JOIN orders o ON c.CustomerID = o.CustomerID GROUP BY
c.CustomerID ORDER BY order_count DESC LIMIT 5;
Optimized Query:
SELECT c.FirstName, COUNT(*) AS order_count FROM customers c
JOIN orders o ON c.CustomerID = o.CustomerID GROUP BY
c.CustomerID ORDER BY order_count DESC LIMIT 5;
Index Added:
CREATE INDEX idx_orders_customerid ON orders(CustomerID);
Explanation:
Index improves join and GROUP BY performance by reducing data scan.
Section 4: Final Task – Query Review & Optimization
Original Query:
SELECT * FROM orders o JOIN customers c ON o.CustomerID = c.CustomerID
WHERE UPPER(c.State) = 'KARNATAKA' AND YEAR(o.orderDate) = 2023
ORDER BY o.orderDate DESC;
Optimized Query:
SELECT o.*, c.* FROM orders o JOIN customers c ON o.CustomerID = c.CustomerID
WHERE c.State = 'KARNATAKA' AND o.orderDate BETWEEN '2023-01-01' AND '2023-
12-31' ORDER BY o.orderDate DESC;
Index Added:
cc
CREATE INDEX idx_orders_orderdate ON orders(orderDate);
CREATE INDEX idx_orders_customerid ON orders(CustomerID);
Removed UPPER() → index can be used.
Replaced YEAR() with BETWEEN.
Ordered on indexed column.
