Originally posted at Andres’s blog. Traditional query optimizing is mostly about two things: first, in which order and from where to access data, and then how to then combine it. You have probably seen the tree shapes execution plans that are produced from query planning. I’ll use an example from the MySQL docs, using FORMAT=TREE which was introduced in MySQL 8.0: mysql> EXPLAIN FORMAT=TREE -> SELECT * -> FROM t1 -> JOIN t2 -> ON (t1.
10 Older Entries »
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 need to understand how MySQL executes them, and what tools are available to help identify problems.
In this session you will learn:
- The common tools for researching problem queries
- What an Index is, and why you should use one
- Index limitations
- When to rewrite the …
Join Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community.
I highly recommend submitting to the CFP for Percona Live Santa Clara 2018 even though it only closes December 22 2017. By the 3rd week of December, i.e. before the CfP closes, it is very likely that we will announce some of the schedule. So get in early! Keep in mind the broad topics, there are some ideas here.
Also: we are looking for sponsors for Percona Live – you can email me for more information.
Join Percona’s Senior Architect, Matthew Boehm, as he presents What You Need to Get the Most Out of Indexes – Part 2 webinar on Thursday, October 19, 2017, at 11:00 am PDT / 2:00 pm EDT (UTC-7).
Proper indexing is key to database performance. Finely tune your query writing and database performance with tips from the experts. MySQL offers a few different types of indexes and uses them in a variety of ways.
In this session you’ll learn:
- How to use composite indexes
- Other index usages besides lookup
- How to find …
In this blog, I will provide answers to the Q & A for the Troubleshooting Slow Queries webinar.
First, I want to thank you for attending the April 28 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:
Q: I’ve heard that is a bad idea to use
; what do you recommend?
A: When I used
in my slides, I wanted to underline the idea that sometimes you need to select all columns …[Read more]
In this series, we’ve discussed everything unique to
. I intentionally skipped a description of members such as
, which are not unique.
In this series, I only mentioned in passing members that replace information from the
column in the regular
output, such as
or …[Read more]
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.
command lists each table that participates in a
operation on a single row. This works perfectly for simple queries:
mysql> explain select * from employees join titles join salariesG *************************** 1. row *************************** id: 1 select_type: SIMPLE table: employees partitions: NULL type: ALL possible_keys: NULL key: NULL …[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
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 to make a choice in this case. One of the metrics that can be used to help make the choice is the potential cost of the query evaluation.
For example, let’s take the table
from the standard employees database:…[Read more]
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
does not identify if
was used at all. To demonstrate, let’s run this query:
mysql> explain select * from salariesG *************************** 1. row *************************** id: 1 select_type: SIMPLE table: salaries partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2557022 filtered: 100.00 …[Read more]
When optimizing complicated queries with
, it is easy to get lost in the regular
output trying to identify which part of the output belongs to each part of the
Let’s consider the following example:
mysql> explain -> select emp_no, last_name, 'low_salary' from employees -> where emp_no in (select emp_no from salaries -> where salary < …[Read more]
10 Older Entries »