intro
In this blog, we discuss everything you need to know about MySQL 9.0 and its new EXPLAIN features that are under the hood. Dig into the EXPLAIN MySQL query type!
MySQL 9.0 is a completely new version of our favorite database management system and in this blog, we walk you through the new capabilities of EXPLAIN mysql query type in the DBMS.
Preface
Many users of MySQL know what the EXPLAIN clause is — them knowing what it actually does is a different topic though. Though, at its core, EXPLAIN isn’t very hard to comprehend.
The EXPLAIN MySQL query type digs into how your queries work under the hood with an aim to help you understand their internals; it provides many details for everyone to understand how their queries work and modify them to make their queries able to achieve a goal.
EXPLAIN can be used with the following syntax:
1
EXPLAIN [query]
Meaning that if you want to explain a particular SELECT, you would provide the entirety of your query after the EXPLAIN query type clause like so:
1
EXPLAIN SELECT * FROM `demo` WHERE a = b;
The same works with all other types of queries, including INSERTs, UPDATEs, and DELETEs.
MySQL and EXPLAIN Features
To understand the capabilities of EXPLAIN, you first have to understand how the query can assist you (and your database.) The output is split into a couple of parts:
For those evaluating query performance, the last columns would probably bear the most significance. Arguably the most important column would be the Extra column that provides information on how your database uses and accesses data using the index. The Extra column can have multiple values:
EXPLAIN MySQL Type Query in Action
To demonstrate the capabilities of EXPLAIN, we’ll run a couple of queries:
1
EXPLAIN SELECT * FROM `car_data` WHERE `car_brand` = 'Volvo';
1
EXPLAIN SELECT `id`, `car_model` FROM `car_data` WHERE `year` = '1995' AND `car_model` = '911';
In this example, both queries are using a composite index (we can deduct that from the name of the index), but the second query seems to choose the index without even considering indexes. That’s understandable — we’re selecting a car model and provide a year and the car model. Who does that? Even DBAs would be confused, so why are we surprised that our database is too?
There are many things that can be said about the EXPLAIN MySQL query type — dig into some of them using the documentation, and once you’re done, consider exploring new features of EXPLAIN, too.
New EXPLAIN Features in MySQL 9.0
Newer versions of MySQL (we’re talking about MySQL ≥ 9.0 here) also introduced a bunch of new features to EXPLAIN too. Now, users can use the clause like so:
1
EXPLAIN ANALYZE FORMAT=JSON INTO @variable [options] query
The new part of the query is the EXPLAIN ANALYZE FORMAT=JSON INTO... allowing you to save the output of the EXPLAIN clause into a variable. Discover what is new in MySQL 9.0!
Such an EXPLAIN query format would be very useful in scenarios where you would find yourself in a situation where you would have to select data from a table according to some filters (clauses) and want to save the output of how it’s executed into a variable. In that case, your query could be split into 2 separate parts (see example below) and you could use it like so:
1
EXPLAIN ANALYZE FORMAT=JSON INTO @savedq [1]
2
SELECT * FROM `car_data` WHERE `year` = '2001' AND `model` = 'Mazda'; [2]
In other words, first, you tell your database to save the output of the EXPLAIN query in a JSON format in a variable named savedq, and then execute the query that you would want analyzed. As a result, you would obtain a JSON array like so. Run SELECT @savedq to select data stored in the variable, and you will be provided with the following:
1
@savedq: {
2
"query": "/* select#1 */ select `hacking_mysql`.`car_data`.`id` AS `id`,`hacking_mysql`.`car_data`.`car_brand` AS `car_brand`,`hacking_mysql`.`car_model`.`car_model` AS `car_model`,`hacking_mysql`.`car_data`.`brand` AS `brand`,`hacking_mysql`.`car_data`.`car_vin` AS `car_vin` from `hacking_mysql`.`car_data` where ((`hacking_mysql`.`car_data`.`car_model` = 'Mazda')) order by `hacking_mysql`.`car_data`.`car_model`",
3
"inputs": [
4
{
5
"inputs": [
6
{
7
"operation": "Table scan on car_data",
8
"table_name": "car_data",
9
"access_type": "table",
10
"actual_rows": 10050,
11
"schema_name": "hacking_mysql",
12
"actual_loops": 1,
13
"used_columns": [
14
"id",
15
"car_brand",
16
"car_model",
17
"year",
18
"car_vin"
19
],
20
"estimated_rows": 10050,
21
"actual_last_row_ms": 3.9765349999999997,
22
"actual_first_row_ms": 0.381938,
23
"estimated_total_cost": 410.85
24
}
25
],
26
"condition": "((car_data.car_brand = 'Mazda') and (car_data.year = '2001') and (car_data.model = 'Mazda'))",
27
"operation": "Filter: ((car_data.year = '2001') and (car_data.model = 'Mazda'))",
28
"access_type": "filter",
29
"actual_rows": 26,
30
"actual_loops": 1,
31
"estimated_rows": 4046,
32
"filter_columns": [
33
"hacking_mysql.car_data.car_brand",
34
"hacking_mysql.car_data.model"
35
],
36
"actual_last_row_ms": 4.76715,
37
"actual_first_row_ms": 4.4423010000000005,
38
"estimated_total_cost": 410.85
39
}
40
],
41
"operation": "Sort: car_data.`car_brand`",
42
"query_type": "select",
43
"access_type": "sort",
44
"actual_rows": 26,
45
"sort_fields": [
46
"car_data.`Name`"
47
],
48
"actual_loops": 1,
49
"estimated_rows": 4046,
50
"actual_last_row_ms": 4.868095,
51
"actual_first_row_ms": 4.863844,
52
"estimated_total_cost": 410.85
53
}
Wow — a lot of information, right? That’s the power of the EXPLAIN MySQL query type, and it’s formatted as JSON data for easy observability. The rationale behind this is that the clause can provide you with a bunch of interesting information including, but not limited to the analysis of exact query conditions, the number of scanned rows, loops that your query went through, rows examined, the query cost, time to access the first row in milliseconds, etc.
SQL Clients and DbVisualizer
All this information may be vital for DBAs or those working with bigger data sets — but given that, it may be hard to interpret through a vanilla CLI appliance. Here’s where SQL clients like DbVisualizer come in handy — by providing you with an avenue to clearly observe, build, and execute your SQL queries, they can ensure that no matter what MySQL version you’re using, your database stays performant, stable, and available at all times.
Its powerful features like editing data like a spreadsheet, the ability to export result sets in a variety of different of formats like Excel, Text, SQL files, and the like, and the SQL client itself make even the best EXPLAIN MySQL query type queries pale in comparison.
With that being said, DbVisualizer does have a feature to analyze query performance that acts very similarly to EXPLAIN: it’s called the explain plan feature and through it, users of DbVisualizer are enabled to highlight statements, run scripts or load them from files — and after that’s done, DbVisualizer will tell you how the query was executed and how you can improve its performance.
From assisting in understanding the internals of simple queries such as a basicSELECT like the following (DbVisualizer’s auto-complete capability will help you complete it if you don’t know what to select too):
1
SELECT username,gender,ip_address FROM demo_data WHERE country = 'Brazil' LIMIT 10;
After you’re done, feel free to export data that is presented to you similarly to what you’d do with EXPLAIN — in this case, though, there are many options you can adjust before doing so:
And that’s only the tip of the iceberg of available features — no matter what kind of database management system you use (DbVisualizer supports a dozen), grab a free 21-day trial of DbVisualizer and try it for yourself! And tweet us once you do, too (@DbVisualizer).
Summary
The EXPLAIN MySQL query type is certainly a reliable partner you can count on — although it won’t solve your problems related to SQL queries, it will provide you with a bunch of reliable information you can act on when understanding your query internals.
MySQL 9.0 and up include the EXPLAIN ANALYZE FORMAT=JSON INTO... clause: its purpose is to save the analyzed query into a variable using a JSON-based format. This clause isn’t for everyone as it certainly provides information that would be hard to understand without having a DBA by your side, but if you want to study the EXPLAIN MySQL query type internals on a deeper level, certainly look into that one as well.
Aside from EXPLAIN, there are many other helpful tools to lend you a helping hand when dealing with data in your database and, you’ve guessed it, DbVisualizer is one of them. Download it for free, and until next time!
FAQ
What is the MySQL EXPLAIN query type?
EXPLAIN in MySQL is a query clause that helps you understand the internals of your query. This query clause provides you a bunch of interesting information that you can use at your discretion when understanding how your queries operate, including whether your query uses any indexes and what its type is, as well as in newer versions of MySQL (MySQL ≥9.0) provides you with a clause that enables you to save query analytic details in a variable with JSON output.
Why should I use DbVisualizer?
Given that you’re reading a blog about EXPLAIN queries in MySQL, you’re already interested in improving your query performance. DbVisualizer is not only a SQL client, but it also comes with a wide variety of other features that help you understand how your queries work internally and optimize them for performance. The best thing of all is that it’s absolutely free to try for 21 days — no strings attached. Give it a whirl and experience it for yourself!
Where can I learn more about the EXPLAIN query clause?
To learn more about the EXPLAIN MySQL query type and clause, follow the documentation of MySQL, follow industry events and updates on Twitter and LinkedIn, go to industry conferences like Percona LIVE, SQLBits, or MariaDB Server Fest, and stay updated by reading blogs just like this one.
