For a long time, the only algorithm for executing a join in MySQL has been variations of the nested loop algorithm. With the release of MySQL 8.0.18, the server can now execute joins using hash join. This blog post will have a look at how it works, when it is used, and how it compares to the old join algorithms in MySQL in terms of performance.…
Category Archives: Performance
MySQL EXPLAIN ANALYZE
MySQL 8.0.18 was just released, and it contains a brand new feature to analyze and understand how queries are executed: EXPLAIN ANALYZE.
What is it?
EXPLAIN ANALYZE is a profiling tool for your queries that will show you where MySQL spends time on your query and why.…
Support for LATERAL derived tables added to MySQL 8.0.14
In the just-released MySQL 8.0.14 I added a feature called LATERAL derived tables.
The manual describes the syntax and has examples of how the feature can be used to find greatest values in tables. In the present post I’m going to consider a different problem solved by LATERAL: let’s say that we have a bunch of nodes, and want to make a “random graph”, by connecting every node to other nodes.…
What is the “(scanning)” variant of a loose index scan?
A query plan uses loose index scan if “Using index for group-by” appears in the “Extra” column of the EXPLAIN output. In some plans though, “Using index for group-by (scanning)” appears. What does “(scanning)” mean and how is it different from the regular loose index scan?…
MySQL 8.0: New Lock free, scalable WAL design
The Write Ahead Log (WAL) is one of the most important components of a database. All the changes to data files are logged in the WAL (called the redo log in InnoDB). This allows to postpone the moment when the modified pages are flushed to disk, still protecting from data losses.…
Histogram statistics in MySQL
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.…
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.…