Visualization of MySQL Execution Plans

By Itay Braun, CTO, Metis

Execution Plan: Unraveling the Blueprint of Database Queries

An execution plan is a detailed strategy that a database management system (DBMS) devises to carry out a specific query. It outlines the sequence of operations and steps the DBMS will take to retrieve, process, and present the requested data. Think of it as a roadmap that guides the database engine in navigating the intricate process of fulfilling a query. By revealing the behind-the-scenes decisions, such as which tables to access, how to join data, and which indexes to use, an execution plan provides crucial insights into the optimization and efficiency of database queries. Understanding execution plans is fundamental for database administrators and developers seeking to fine-tune query performance and enhance overall database operations.

A MySQL plan can look like this

| id | select_type  | table         | partitions | type | possible_keys                                                    | key                       | key_len | ref                         | rows | filtered | Extra                                      |
|----|--------------|---------------|------------|------|------------------------------------------------------------------|---------------------------|---------|-----------------------------|------|----------|--------------------------------------------|
|  1 | SIMPLE       | <subquery2>   |            | ALL  |                                                                  |                           |         |                             |      |   100.00 |                                            |
|  1 | SIMPLE       | film          |            | ALL  | PRIMARY                                                          |                           |         |                             | 1000 |     0.10 | Using where; Using join buffer (hash join) |
|  2 | MATERIALIZED | category      |            | ALL  | PRIMARY                                                          |                           |         |                             |   16 |    10.00 | Using where                                |
|  2 | MATERIALIZED | film_category |            | ref  | film_category_pk,film_category_category_id,film_category_film_id | film_category_category_id | 1       | sakila.category.category_id |   62 |   100.00 |                                            |

Or much more complicated.

A new free tool, https://explainmysql.com/, can help in visualizing the plan