**Course Title:** SQL Mastery: From Fundamentals to Advanced Techniques **Section Title:** Indexing and Query Optimization **Topic:** Analyze the performance of various queries and apply indexing techniques for optimization.(Lab topic) **Objective:** By the end of this lab topic, you will be able to analyze the performance of various queries, identify bottlenecks, and apply indexing techniques to optimize query performance. **Introduction:** In this lab, we will dive deeper into query optimization techniques, specifically focusing on indexing. We will use a sample database and perform various queries to analyze their performance. Then, we will apply indexing techniques to optimize the queries and compare the results. **Prerequisites:** Before starting this lab, make sure you have completed the following topics: * Indexing and Query Optimization (previous topics) * MySQL or PostgreSQL installed on your local machine (you can use either one) * A sample database (e.g., the Sakila database for MySQL or the Pagila database for PostgreSQL) **Materials:** * Sample database (download from [Sakila database for MySQL](https://dev.mysql.com/doc/sakila/en/) or [Pagila database for PostgreSQL](https://github.com/devrimgunduz/pagila)) * MySQL or PostgreSQL client (e.g., MySQL Workbench or psql) * Query editor or IDE (e.g., Visual Studio Code or IntelliJ IDEA) **Lab Exercises:** **Exercise 1: Analyze Query Performance** 1. Connect to your sample database using the MySQL or PostgreSQL client. 2. Run the following queries and note their execution times: ```sql -- Query 1 SELECT * FROM customers WHERE customer_id = 123; -- Query 2 SELECT * FROM orders WHERE order_date > '2020-01-01' AND order_date < '2020-12-31'; -- Query 3 SELECT * FROM products WHERE product_name LIKE '%apple%'; ``` 3. Use the EXPLAIN command to analyze the query plans: ```sql -- Query 1 EXPLAIN SELECT * FROM customers WHERE customer_id = 123; -- Query 2 EXPLAIN SELECT * FROM orders WHERE order_date > '2020-01-01' AND order_date < '2020-12-31'; -- Query 3 EXPLAIN SELECT * FROM products WHERE product_name LIKE '%apple%'; ``` 4. Observe the query plans and identify potential bottlenecks. **Exercise 2: Apply Indexing Techniques** 1. Based on the query plans, create indexes on the following columns: * customers (customer_id) * orders (order_date) * products (product_name) ```sql -- Create indexes CREATE INDEX idx_customers_customer_id ON customers (customer_id); CREATE INDEX idx_orders_order_date ON orders (order_date); CREATE INDEX idx_products_product_name ON products (product_name); ``` 2. Re-run the queries from Exercise 1 and note their execution times. 3. Use the EXPLAIN command to analyze the query plans again: ```sql -- Query 1 EXPLAIN SELECT * FROM customers WHERE customer_id = 123; -- Query 2 EXPLAIN SELECT * FROM orders WHERE order_date > '2020-01-01' AND order_date < '2020-12-31'; -- Query 3 EXPLAIN SELECT * FROM products WHERE product_name LIKE '%apple%'; ``` 4. Observe the query plans and compare the execution times before and after indexing. **Exercise 3: Optimize Queries** 1. Based on the query plans and execution times, optimize the queries by: * Using covering indexes * Avoiding select * * Using efficient join methods ```sql -- Optimized Query 1 SELECT customer_id, name FROM customers WHERE customer_id = 123; -- Optimized Query 2 SELECT * FROM orders WHERE order_date > '2020-01-01' AND order_date < '2020-12-31' ORDER BY order_date; -- Optimized Query 3 SELECT * FROM products WHERE product_name LIKE '%apple%' LIMIT 10; ``` 2. Re-run the optimized queries and note their execution times. 3. Use the EXPLAIN command to analyze the query plans again: ```sql -- Optimized Query 1 EXPLAIN SELECT customer_id, name FROM customers WHERE customer_id = 123; -- Optimized Query 2 EXPLAIN SELECT * FROM orders WHERE order_date > '2020-01-01' AND order_date < '2020-12-31' ORDER BY order_date; -- Optimized Query 3 EXPLAIN SELECT * FROM products WHERE product_name LIKE '%apple%' LIMIT 10; ``` 4. Observe the query plans and compare the execution times before and after optimization. **Conclusion:** In this lab, you have analyzed the performance of various queries, applied indexing techniques to optimize query performance, and compared the results. You have also optimized queries using efficient join methods, covering indexes, and proper query syntax. **Next Steps:** Your next topic is **Introduction to SQL views and their use cases**. Please proceed to the next topic for more information. **Feedback and Help:** If you have any questions or need help with this lab, please leave a comment below.