The world of databases is often a race against time. Users expect applications to respond instantaneously, and slow queries can throw a wrench into that expectation. But are slow queries always occurring? The answer is Yes. It doesn’t mean that once taken care of, it will remain the same forever. Hence slow queries too can be recurring or even the optimized queries may act up. So optimization is an important slice of the database performance.

To identify performance bottlenecks, we can analyze the query's execution plan using a tool like EXPLAIN. This plan reveals how the database engine retrieves data, allowing us to pinpoint areas for optimization, like unnecessary table scans or inefficient joins.

Recently there’s a new feature which allows storing the explain plan output into a variable. In this blog let's see how we can optimize the queries using the new features introduced in the explain plan.

Innovative Releases in MySQL

From 5.7 to 8.0 MySQL version the Oracle team has been yearly providing support and new version updates with bug fixes including additional features. But currently, the team has taken a step further in catering to diverse user needs.

The Oracle team has introduced Long-term support and innovative releases. The main difference between the both is that one offers stability and long-term support while the other focuses on bug fixes and further modifications with short-term support. The innovative releases are mainly for those who want to have variations in using the versions.

Both types are production-grade quality but with slight differences based on the application needs. The first innovation release released was 8.1.0. Following the release the 8.3.0 version has been released. Furthermore, it’s been confirmed that 8.0.34+ versions will only get bug fixes till the End Of Life scheduled for April 2026. In the forthcoming days, the 8.4.0 version will be released under LTS support. The feature of storing the explain output was introduced in the innovation release of 8.1.0. Let's see more about how it is achieved below.

How to Use EXPLAIN FORMAT = JSON INTO Variable

In the MySQL innovative release, one of the features introduced is capturing the explain format = JSON output into a variable introduced in MySQL 8.1. What does this feature do? For example, an application with hundreds of frequently executed queries is managed by you. The queries executed are quite complex. To tune those queries we will have to execute the explain for the queries one by one which will be time-consuming and confusing. This is where this feature will give you a hand. We will be able to input the output of the explain format in JSON to a variable and then call the variable for the output.  

Let's see how we can use this feature in detail with examples

Example:

Query:

 
EXPLAIN FORMAT=JSON INTO @myvar SELECT * FROM city WHERE District='Texas';
	

Output:

 
select @myvar\G
*************************** 1. row ***************************
@myvar: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "428.50"
    },
    "table": {
      "table_name": "city",
      "access_type": "ALL",
      "rows_examined_per_scan": 4035,
      "rows_produced_per_join": 403,
      "filtered": "10.00",
      "cost_info": {
        "read_cost": "388.15",
        "eval_cost": "40.35",
        "prefix_cost": "428.50",
        "data_read_per_join": "97K"
      },
      "used_columns": [
        "ID",
        "Name",
        "CountryCode",
        "District",
        "Population"
      ],
      "attached_condition": "(`world`.`city`.`District` = 'Texas')"}}
	

Benefits of Capturing EXPLAIN Output

1. Improved Automation: The ability to store execution plans in variables was introduced to facilitate automated systems that require detailed and programmable access to query performance data. It allows for more sophisticated automation scripts that can programmatically analyze and act upon execution plans.
2. Enhanced Debugging: Developers and DBAs often need to debug complex queries and stored procedures. Having execution plans readily available in variables simplifies the debugging process and allows for more complex, context-aware analysis within scripts and stored procedures.
3. Performance Analysis: As part of performance tuning, being able to dynamically store and analyze execution plans allows for deeper insights and more flexible analysis techniques. It helps in building more robust performance monitoring and optimization tools.

Use Cases

These are some uses for the feature introduced,

• Automated Analysis: By storing the execution plan in a variable, you can automate the analysis of query performance. Scripts can be written to parse the JSON and extract key metrics, such as execution time, index usage, and cost estimates.
• Batch Processing: You can execute multiple queries and store their plans for batch processing. This is useful in automated testing environments where you need to analyze the performance of a suite of queries.
• Adaptive Systems: In adaptive query optimization systems, storing the execution plan allows the system to dynamically adjust strategies based on current performance metrics.
• Immediate Access: By storing the execution plan in a variable, you can immediately access and examine it without rerunning the EXPLAIN command. This is particularly useful in debugging sessions where you need to understand the execution plan in the context of other operations.
• Baseline Comparisons: Store execution plans as part of your performance benchmarks. This allows you to compare current plans against baselines to identify regressions or improvements.
• Test Automation: In continuous integration/continuous deployment (CI/CD) pipelines, you can automate the comparison of execution plans to ensure new changes do not negatively impact performance.

Who Will Benefit

• Database Administrators (DBAs): DBAs can use this feature to automate routine checks and performance monitoring tasks, making their workflows more efficient and less error-prone.
• Developers: Developers benefit by integrating execution plan analysis into their development and CI/CD pipelines, ensuring that performance considerations are part of the development process.
• Performance Analysts: Analysts can use stored execution plans to create detailed reports and perform in-depth analysis, helping to identify and resolve performance bottlenecks.
• DevOps Engineers: In DevOps practices, this feature aids in creating automated systems that monitor and optimize database performance, ensuring smooth and efficient operations.

Limitations and Considerations

• Memory Limitations: MySQL user-defined variables are stored in memory, and their size is limited by the maximum packet size (max_allowed_packet configuration parameter). If the JSON execution plan is large, it might exceed this limit, causing an error or truncation.
• Restriction: The INTO clause is only supported with FORMAT=JSON; the explain_format system variable does not influence this requirement. If the statement cannot be executed, such as due to a syntax error, the user variable remains unchanged. The INTO clause is not supported for EXPLAIN ANALYZE or EXPLAIN FOR CONNECTION.
• Performance Overhead: Generating the JSON format for the execution plan and storing it in a variable involves additional processing overhead. This might slightly impact the performance of the query being analyzed, especially in a high-throughput environment.
• Parsing Complexity: Extracting and analyzing specific details from the JSON execution plan can be complex and may require custom parsing logic, which is not straightforward to implement in SQL.
• Session Isolation: Each session has its own variable scope. You cannot easily share the stored execution plan variable between different sessions, limiting collaborative analysis.
• Tool Integration: While storing the execution plan in a variable is useful, integrating this functionality seamlessly into existing monitoring and performance analysis tools can be challenging. External tools often provide more comprehensive and user-friendly interfaces for performance analysis.

Adopting this innovative approach can lead to more robust performance monitoring and optimization practices, ensuring that applications remain responsive and efficient. Embrace the new capabilities offered by MySQL's EXPLAIN FORMAT = JSON feature to stay ahead in the competitive world of database management.

For expert assistance in optimizing your MySQL queries and overall database performance, consider leveraging Mydbops Managed, Consulting, and Remote DBA Services. Contact us today to learn how we can help you harness the full potential of MySQL’s innovative features.

{{cta}}