Practice & Assessment
Test your understanding of Indexing and Query Optimization
Multiple Choice Questions
5What does 'type = ALL' mean in a MySQL EXPLAIN output?
What is a covering index?
Why does WHERE YEAR(created_at) = 2024 prevent index usage on the created_at column?
What is the 'leftmost prefix rule' for composite indexes?
What should you do if EXPLAIN ANALYZE shows the estimated row count is vastly different from the actual row count?
Coding Challenges
1Index Design and Query Optimization
You have a slow reporting database with these tables: orders (order_id, customer_id, status, order_date, total_amount, region), order_items (order_id, product_id, quantity, unit_price), products (product_id, name, category_id, price, brand). Each table has millions of rows and no indexes beyond the primary keys. Task: (1) For each of the following queries, identify what is causing slowness and design the optimal index: (a) SELECT * FROM orders WHERE customer_id = ? AND status = 'completed' (b) SELECT category_id, SUM(quantity*unit_price) FROM order_items JOIN products USING(product_id) GROUP BY category_id (c) SELECT * FROM orders WHERE order_date BETWEEN ? AND ? ORDER BY total_amount DESC LIMIT 50. (2) Write EXPLAIN output you would expect before and after adding each index. (3) Identify one query that would benefit from a covering index and write the CREATE INDEX statement.
Mini Project
Full Database Performance Audit and Optimization
You are given a poorly performing e-commerce database with tables: users, products, categories, orders, order_items, reviews, inventory. The application is slow and DBA reports high CPU from full table scans. Task: (1) Write EXPLAIN statements for 5 common application queries (orders by user, product search by category and price range, top-rated products, recent orders with items, inventory status). (2) For each query, identify missing indexes and write CREATE INDEX statements with justification. (3) Design a summary VIEW that combines the most common query pattern (orders with customer, products, and totals). (4) Write a stored procedure that runs weekly to ANALYZE all tables and log statistics to a maintenance_log table. (5) Identify 3 queries that would benefit from being rewritten (e.g., replace correlated subquery with JOIN, replace function on indexed column with range condition) and show the before and after SQL with EXPLAIN improvement.