In this blog post, I want to share a case we worked on a few days ago. I’ll show you how we approached the resolution of a MySQL performance issue and used Percona Monitoring and Management PMM to support troubleshooting. The customer had noticed a linear high CPU usage in one of their MySQL […]Read more
Some time ago, a customer had a performance issue with an internal process. He was comparing, finding, and reporting the rows that were different between two tables. This is simple if you use a LEFT JOIN and an
IS NULL comparison over the second table in the WHERE clause, but what if the column could be […]
Lately, I worked on several queries which started returning wrong results after upgrading MySQL Server to version 5.7 The reason for the failure was derived merge optimization which is one of the default
optimizer_switch options. Issues were solved, though at the price of performance, when we turned it
OFF . But, more importantly, we could […]
Please join Percona’s MySQL Database Administrator, Brad Mickel as he presents How to Analyze and Tune MySQL Queries for Better Performance on Thursday, June 21st, 2018, at 10:00 AM PDT (UTC-7) / 1:00 PM EDT (UTC-4).
Query performance is essential in making any application successful. In order to finely tune your queries you first […]
This blog is an EXPLAIN FORMAT=JSON wrap-up for the series of posts I’ve done in the last few months.
In this series, we’ve discussed everything unique to
EXPLAIN FORMAT=JSON. I intentionally skipped a description of members such as
select_id, which are not unique.
In this series, I only mentioned in passing members that […]
Once again it’s time for another EXPLAIN FORMAT=JSON is cool! post. This post will discuss how EXPLAIN FORMAT=JSON allows the nested_loop command to make the JOIN operation hierarchy transparent.
EXPLAIN command lists each table that participates in a
JOIN operation on a single row. This works perfectly for simple queries:
mysql> explain select * from […]Read more
Time for another entry in the EXPLAIN FORMAT=JSON is cool! series of blog posts. This time we’ll discuss how using EXPLAIN FORMAT=JSON allows you to see that
cost_info knows why the optimizer prefers one index to another.
Tables often have more than one index. Any of these indexes can be used to resolve query. The optimizer has […]
Time for another entry in the EXPLAIN FORMAT=JSON is cool! series. Today we’re going to look at how you can view the buffer result using JSON (instead of the regular
EXPLAIN does not identify if
SQL_BUFFER_RESULT was used at all. To demonstrate, let’s run this query:
mysql> explain select * from salariesG
*************************** 1. row ***************************
Ready for another post in the EXPLAIN FORMAT=JSON is Cool series! Great! This post will discuss how to see all the information that is contained in optimized queries with
UNION using the
When optimizing complicated queries with
UNION, it is easy to get lost in the regular
EXPLAIN output trying to identify which part of […]
EXPLAIN FORMAT=JSON has details for subqueries in HAVING, nested selects and subqueries that update values
Over several previous blog posts, we’ve already discussed what information the
EXPLAIN FORMAT=JSON output provides for some subqueries. You can review those discussions here, here and here. EXPLAIN FORMAT=JSON shows many details that you can’t get with other commands. Let’s now finish this topic and discuss the output for the rest of the subquery types.
First, let’s look at the […]