Learn how to optimize queries in MySQL.
Provide optimization step-by-step query of a complex query with execution plan comparison.
- In query to optimize you should use at least 2 joins (you have to join at least 3 tables).
- You have to have at least 10000 rows in each table.
- You should demonstrate 2 variants of the query: optimized and non-optimized.
- Your 2 query variants have to return the same result.
- You should use CTE for optimization.
- You must use the index/indexes for optimization.
- You must show a comparison of execution plans.
- Your code should be on Github.
- Explain your solution using the correct terminology.
- Be ready to answer questions about query optimization.
I suggest you look at my example of the task, which is described below (MySQL Optimization Demo).
- Python 3.9.6
- MySQL Server
mysql-connector-python
packageFaker
packagepython-dotenv
package
- Run script_01_create_tables.sql in your database.
- Set environment variables (host, user, password, database).
- Run:
-
pip install -r requirements.txt
- Run main.py to insert fake data to the tables.
- Execute queries from optimization_demo.sql in your database using EXPLAIN.