In MySQL 8.0.1, we introduced support for recursive common table expressions (CTE). There are quite a few blog entries showcasing the feature, starting from this one, and there is also a complete documentation. Today, I would like to present a solution to a problem which nearly everybody meets when writing queries with recursive CTE’s: when infinite recursion happens, how to debug ?…
Category Archives: Performance
Antijoin in MySQL 8
In MySQL 8.0.17, we made an observation in the well-known TPC-H benchmark for one particular query. The query was executing 20% faster than in MySQL 8.0.16. This improvement is because of the “antijoin” optimization which I implemented. Here is its short mention in the release notes:
“The optimizer now transforms a WHERE condition having NOT IN (subquery), NOT EXISTS (subquery), IN (subquery) IS NOT TRUE, or EXISTS (subquery) IS NOT TRUE internally into an antijoin, thus removing the subquery.”…
Hash join in MySQL 8
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.…
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,…