Tag - Optimizer

Why Optimization derived_merge can Break Your Queries

MySQL optimizer bugs

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 […]

Read more

ANALYZE TABLE Is No Longer a Blocking Operation

analyze table

In this post, I’ll discuss the fix for lp:1704195 (migrated to PS-2503), which prevents
ANALYZE TABLE from blocking all subsequent queries on the same table.
In November 2017, Percona released a fix for lp:1704195 (migrated to PS-2503), created by Laurynas Biveinis. The fix, included with Percona Server for MySQL since versions 5.6.38-83.0 and 5.7.20-18, stops
ANALYZE TABLE from invalidating […]

Read more

Percona Live Featured Tutorial with Morgan Tocker — MySQL 8.0 Optimizer Guide

Percona Live Featured Tutorial

Welcome to another post in the series of Percona Live featured tutorial speakers blogs! In these blogs, we’ll highlight some of the tutorial speakers that will be at this year’s Percona Live conference. We’ll also discuss how these tutorials can help you improve your database environment. Make sure to read to the end to get a special […]

Read more

Percona Live Europe 2016: “MySQL 8.0: what’s new in Optimizer” with Manyi Lu

Percona Live Europe

Today was the first day of sessions at Percona Live Europe 2016, and it was packed with exciting talks and important information on open source databases. Some of the most anticipated talks are those that cover what to expect in MySQL 8.0.
One of those talks was given by Oracle’s Manyi Lu, Director of Software Development. […]

Read more

Introduction into storage engine troubleshooting: Q & A

Overlooked MySQL performance optimization

In this blog, I will provide answers to the Q & A for the “Introduction into storage engine troubleshooting” webinar.
First, I want to thank everybody for attending the July 14 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 […]

Read more

MySQL indexing 101: a challenging single-table query

We discussed in an earlier post how to design indexes for many types of queries using a single table. Here is a real-world example of the challenges you will face when trying to optimize queries: two similar queries, but one is performing a full table scan while the other one is using the index […]

Read more

Optimizer hints in MySQL 5.7.7 – The missed manual

In version MySQL 5.7.7 Oracle presented a new promising feature: optimizer hints. However it did not publish any documentation about the hints. The only note which I found in the user manual about the hints is:

It is now possible to provide hints to the optimizer by including /*+ … */ comments following the SELECT, […]

Read more

Q&A: Even More Deadly Mistakes of MySQL Development

On Wednesday I gave a presentation on “How to Avoid Even More Common (but Deadly) MySQL Development Mistakes” for Percona MySQL Webinars.  If you missed it, you can still register to view the recording and my slides.
Thanks to everyone who attended, and especially to folks who asked the great questions.  I answered as many as we had […]

Read more

Many-table joins in MySQL 5.6

I recently worked on an uncommon slow query: less than 100 rows were read and returned, the whole dataset was fitting in memory but the query took several seconds to run. Long story short: the query was a join involving 21 tables, running on MySQL 5.1. But by default MySQL 5.1 is not good […]

Read more

Multiple column index vs multiple indexes with MySQL 5.6

A question often comes when talking about indexing: should we use multiple column indexes or multiple indexes on single columns? Peter Zaitsev wrote about it back in 2008 and the conclusion then was that a multiple column index is most often the best solution. But with all the recent optimizer improvements, is there anything different […]

Read more