by Peter Zaitsev | Jun 1, 2023 | Insight for Developers, MySQL
Updated June 1, 2023. Suboptimal MySQL ORDER BY implementation, especially together with MySQL LIMIT is often the cause of MySQL performance problems. Here is what you need to know about MySQL ORDER BY LIMIT optimization to avoid these problems. Try Now: Free your...
by Sveta Smirnova | Sep 25, 2018 | MySQL
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...
by Sveta Smirnova | Mar 27, 2018 | Insight for DBAs, MySQL, Storage Engine
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...
by Dave Avery | Jan 16, 2017 | MySQL, Percona Live
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...
by Dave Avery | Oct 4, 2016 | MySQL, Percona Live
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 Sveta Smirnova | Aug 1, 2016 | MySQL, Webinars
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...
by Stephane Combaudon | May 8, 2015 | Insight for DBAs, MySQL
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...
by Sveta Smirnova | Apr 30, 2015 | Insight for DBAs, MySQL, Percona Live
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...
by Bill Karwin | Jul 17, 2014 | Insight for Developers, MySQL, Webinars
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...
by Stephane Combaudon | Mar 6, 2014 | Insight for DBAs, MySQL
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...
by Stephane Combaudon | Jan 3, 2014 | Insight for DBAs, MySQL
by Bill Karwin | Jun 3, 2013 | Insight for Developers, MySQL, Webinars
On Friday I gave a presentation on “MySQL Query Patterns, Optimized” 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...
by Bill Karwin | May 20, 2013 | MySQL, Webinars
Next Friday, May 31 at 10 a.m. Pacific, I’ll present Percona’s next webinar, “SQL Query Patterns, Optimized.” Based on my experiences solving tough SQL problems for Percona training and consulting, I’ll classify several common types of...
by Ovais Tariq | Apr 4, 2012 | Benchmarks, Insight for DBAs, MySQL
This is the third blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is targeted at the join related optimizations introduced in the optimizer. These optimizations are...
by Ovais Tariq | Mar 21, 2012 | Benchmarks, Insight for DBAs, MySQL
This is the second blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is aimed at the optimizer enhancement Multi Range Read (MRR). Its available in both MySQL 5.6 and MariaDB...
by Ovais Tariq | Mar 12, 2012 | Benchmarks, Insight for DBAs, MySQL
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer...
by Baron Schwartz | Jun 15, 2010 | Insight for Developers, MySQL
MySQL’s SHOW STATUS command has two counters that are often confusing and result in “what does that mean?” questions: Handler_read_rnd Handler_read_rnd_next As I understand it, there is some historical context to the choice of names here, hearkening...
by Justin Swanhart | Jun 15, 2010 | Insight for Developers, MySQL
While many people are familiar with the MySQL EXPLAIN command, fewer people are familiar with “explain extended” which was added in MySQL 4.1 EXPLAIN EXTENDED …can show you what the MySQL optimizer does to your query. You might not know this, but...
by Peter Zaitsev | May 22, 2010 | Insight for DBAs, MySQL
One schema optimization we often do is extending index when there are queries which can use more key part. Typically this is safe operation, unless index length increases dramatically queries which can use index can also use prefix of the new index are they ? It turns...
by Justin Swanhart | May 19, 2010 | Insight for Developers, MySQL
MySQL supports two different algorithms for views: the MERGE algorithm and the TEMPTABLE algorithm. These two algorithms differ greatly. A view which uses the MERGE algorithm can merge filter conditions into the view query itself. This has significant performance...
