Query planning is hard # Have you ever wondered what goes on behind the scenes when you execute a SQL query? What steps are taken to access your data? In this article, I'll talk about the history of Vitess's V3 query planner, why we created a new query planner, and the development of the new Gen4 query planner. Vitess is a horizontally scalable database solution which means that a single table can be spread out across multiple database instances.
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>EXPLAINFORMAT=TREE->SELECT*->FROMt1->JOINt2->ON(t1.c1=t2.c1ANDt1.c2<t2.c2)->JOINt3->ON(t2.c1=t3.c1)\G***************************1.row***************************EXPLAIN:->Innerhashjoin(t3.c1=t1.c1)(cost=1.05rows=1)->Tablescanont3(cost=0.35rows=1)->Hash->Filter:(t1.c2<t2.c2)(cost=0.70rows=1)->Innerhashjoin(t2.c1=t1.c1)(cost=0.70rows=1)->Tablescanont2(cost=0.35rows=1)->Hash->Tablescanont1(cost=0.35rows=1)Here we can see that the MySQL optimizer thinks the best plan is to start reading from t1 using a table scan.
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.
Releases
[Read more]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
select *
; what do you recommend?
A: When I used
SELECT *
in my slides, I wanted to underline the idea that sometimes you need to select all columns …
[Read more]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
table_name
,
access_type
or
select_id
, which are not unique.
In this series, I only mentioned in passing members that replace information from the
Extra
column in the regular
EXPLAIN
output, such as
using_join_buffer
,
partitions
,
using_temporary_table
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.
The regular
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 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
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 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
titles
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
EXPLAIN
command.
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 *************************** 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]