Mini project: speed up ShopLite queries

60 min · Advanced

Query tuning is evidence first: measure what a query costs, change one thing, and measure again. ShopLite has grown, the team has five slow queries, and your job is to make them fast and prove it.

What you'll build

Your script builds a large copy of the ShopLite store with recursive CTEs: 10,000 customers, 2,000 products, 60,000 orders and 1,20,000 order items, in tables prefixed sl_ so they never clash with the practice data. It then runs the five queries the team reported as slow and records, for each one, its answer and the rows it examined.

Next it adds the indexes those queries need, rewrites the one query that cannot use an index as written, and runs all five again. It finishes with a tuning report: rows examined before and after, and a check that every query still gives the same answer, followed by the list of indexes it created. You also capture EXPLAIN ANALYZE before and after for the README.

Sample run

+----------------------+-------------+------------+-------------+
| query                | rows_before | rows_after | same_answer |
+----------------------+-------------+------------+-------------+
| Q1 customer spend    |       60000 |          6 |           1 |
| Q2 new PLACED orders |       60000 |       1018 |           1 |
| Q3 login by email    |       10000 |          1 |           1 |
| Q4 product revenue   |      120000 |         60 |           1 |
| Q5 orders on one day |       60000 |        330 |           1 |
+----------------------+-------------+------------+-------------+
+----------------+-----------------------------+------------------------+
| TABLE_NAME     | INDEX_NAME                  | columns                |
+----------------+-----------------------------+------------------------+
| sl_customers   | uq_sl_customers_email       | email                  |
| sl_order_items | idx_sl_order_items_product  | product_id             |
| sl_orders      | idx_sl_orders_customer_date | customer_id,order_date |
| sl_orders      | idx_sl_orders_date          | order_date             |
| sl_orders      | idx_sl_orders_status_date   | status,order_date      |
+----------------+-----------------------------+------------------------+

Requirements

Your program should do all of these.

queries.sql runs from start to end on an empty shop database and can be run again (it drops its own tables and view first). Do not use CREATE DATABASE, DROP DATABASE or USE.
All test data is generated with recursive CTEs: 10,000 customers, 2,000 products, 60,000 orders and 1,20,000 order items, in the tables sl_customers, sl_products, sl_orders and sl_order_items.
The five team queries run before any secondary index exists, and their answers and rows examined are saved in user variables (@a1, @b1, ...).
The script adds only indexes that a team query needs, with equality columns before range columns, named exactly: uq_sl_customers_email, idx_sl_orders_customer_date, idx_sl_orders_status_date, idx_sl_orders_date and idx_sl_order_items_product.
Query 5 is rewritten so it no longer wraps order_date in a function.
The first report has the columns query, rows_before, rows_after and same_answer, one row per query in the order Q1 to Q5, named Q1 customer spend, Q2 new PLACED orders, Q3 login by email, Q4 product revenue and Q5 orders on one day, with same_answer = 1 for all five.
The second report lists the new indexes with the columns TABLE_NAME, INDEX_NAME and columns (the index columns in order, separated by commas), sorted by table name and then index name.
The README holds the report, EXPLAIN ANALYZE evidence for at least one query, and a note on the write cost of the new indexes.

Build it step by step in the Build tab (5 milestones, each ends with a program that runs). Commit and push after each one: MentorNxt checks every push and shows the result in the Submit tab.

What's next

You finished the preview of Indexes & Query Performance

Python Programming has 21 sections, 110 modules and 609 lessons. An account opens every lesson and saves your progress.

Create account I already have an account

Next step

Start building

5 milestones

What you'll build

Sample run

+----------------------+-------------+------------+-------------+
| query                | rows_before | rows_after | same_answer |
+----------------------+-------------+------------+-------------+
| Q1 customer spend    |       60000 |          6 |           1 |
| Q2 new PLACED orders |       60000 |       1018 |           1 |
| Q3 login by email    |       10000 |          1 |           1 |
| Q4 product revenue   |      120000 |         60 |           1 |
| Q5 orders on one day |       60000 |        330 |           1 |
+----------------------+-------------+------------+-------------+
+----------------+-----------------------------+------------------------+
| TABLE_NAME     | INDEX_NAME                  | columns                |
+----------------+-----------------------------+------------------------+
| sl_customers   | uq_sl_customers_email       | email                  |
| sl_order_items | idx_sl_order_items_product  | product_id             |
| sl_orders      | idx_sl_orders_customer_date | customer_id,order_date |
| sl_orders      | idx_sl_orders_date          | order_date             |
| sl_orders      | idx_sl_orders_status_date   | status,order_date      |
+----------------+-----------------------------+------------------------+

Requirements

Your program should do all of these.

queries.sql runs from start to end on an empty shop database and can be run again (it drops its own tables and view first). Do not use CREATE DATABASE, DROP DATABASE or USE.

All test data is generated with recursive CTEs: 10,000 customers, 2,000 products, 60,000 orders and 1,20,000 order items, in the tables sl_customers, sl_products, sl_orders and sl_order_items.

The five team queries run before any secondary index exists, and their answers and rows examined are saved in user variables (@a1, @b1, ...).

The script adds only indexes that a team query needs, with equality columns before range columns, named exactly: uq_sl_customers_email, idx_sl_orders_customer_date, idx_sl_orders_status_date, idx_sl_orders_date and idx_sl_order_items_product.

Query 5 is rewritten so it no longer wraps order_date in a function.

The first report has the columns query, rows_before, rows_after and same_answer, one row per query in the order Q1 to Q5, named Q1 customer spend, Q2 new PLACED orders, Q3 login by email, Q4 product revenue and Q5 orders on one day, with same_answer = 1 for all five.

The second report lists the new indexes with the columns TABLE_NAME, INDEX_NAME and columns (the index columns in order, separated by commas), sorted by table name and then index name.

The README holds the report, EXPLAIN ANALYZE evidence for at least one query, and a note on the write cost of the new indexes.