As of MySQL 8.0.3, you now have the ability to create histogram statistics in order to provide more statistics to the optimizer. In this blog post, we will have a look at how you can create histogram statistics, and we will explain when it might be useful to have histogram statistics.…
Category Archives: Performance
MySQL 8.0: Improved performance with CTE
MySQL 8.0 introduces Common Table Expressions (CTE). My colleague Guilhem has written several blog posts on how to use CTEs , and you can also read about it in the MySQL 8.0 Reference Manual. In this blog post, I will focus on how using a CTE instead of a view or a derived table can improve performance.…
MySQL 8.0 Labs: [Recursive] Common Table Expressions in MySQL (CTEs), Part Three – hierarchies
[Update of April 2017: the features discussed here are now also available in the official release 8.0.1 of MySQL. And this series of posts has been continued, here.]
Here is the third in a series of posts about CTEs, a new feature of MySQL 8.0, available in this Labs release.…
MySQL 8.0 Labs – Descending Indexes in MySQL
Starting with the 8.0 optimizer labs release the MySQL server now supports descending indexes. As I will detail in this post, this new feature can be used to eliminate the need for sorting results, and lead to performance improvements in a number of queries.…
MySQL 8.0: Faster batch record retrieval
One of the things we have been working on for MySQL 8, is speeding up scans that read multiple records from a table or an index. Such scans benefit from reading the records in batches, so that they don’t get the overhead of latching data pages and navigating in the B-tree for every single record that is read.…
Indexing JSON documents via Virtual Columns
In a previous post I took the new JSON datatype and functions for a test drive, using sample data provided by SF OpenData. Today I will extend that example to include indexing.
Introducing Generated Columns
MySQL 5.7 introduces a new feature called generated columns.…
What to do with optimizer hints after an upgrade?
MySQL 5.7: Improved Performance of Queries with Derived Tables
In a recent blog post my colleague Roy presented his work for MySQL 5.7 that makes the query optimizer merge sub-queries in the FROM clause (so-called derived tables) with the outer query. I will in this blog post show an example of how this work improves the performance of a MySQL query.…
Derived Tables in MySQL 5.7
In MySQL 5.7, we have enhanced the optimizer so that derived tables and views are handled more consistently.
Just to recap: A derived table is a subquery that can take the place of a table in the FROM clause of an SQL statement.…
MySQL 5.7 Labs — Using Loopback Fast Path With Windows 8/2012
TCP Loopback fast path
In Windows 8 and Windows server 2012, Microsoft introduced a new “TCP Loopback fast path” feature (see the Microsoft Technet article: Fast TCP Loopback Performance and Low Latency with Windows Server 2012 TCP Loopback Fast Path).…