About MySQL development (Optimizer mainly)

In MySQL 8.0.1, I introduced support for recursive common table expressions (CTE). In the just-released version 8.0.19, I added an easy solution to a problem which nearly everybody meets when writing queries with recursive CTE’s: when infinite recursion happens, how to debug? You can read about it in my blog post on mysqlserverteam .

Hello! 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. You can find out more in my blog post on mysqlserverteam.

Hi. In my earlier post , I showed how MySQL, since version 8.0.14, has support for LATERAL derived tables. With LATERAL, a JOIN can have a second table – a subquery-based derived table – be defined based on values from columns of the first table, and thus be re-calculated for each row of the first table. Typically: SELECT ... FROM t1, LATERAL (SELECT ... FROM t2 ^ WHERE t2.col=t1.col ... ) AS derived; | | | | +---------------------------+ We say that in the second table ( derived ),  t1.col is a “lateral outer reference” to the first table t1 . The word “lateral” hints at the fact that the referenced table is placed “next to”, “on the side of” the derived table (i.e. both are part of the same FROM clause). The arrow goes “laterally”. While implementing this LATERAL feature, I added another related one at the same time: support for NON -latera...

Hello all. Last months have been busy for me with various tasks, and one of them is finally done and released in MySQL 8.0.14 : LATERAL derived tables. All details are in my post on mysqlserverteam . I think it is a useful addition to our SQL capabilities. Enjoy!

Happy New Year to everyone! I published an article about row numbering and ranking with MySQL, here.

Colin, from Percona, wrote a nice post demonstrating a funny use of common table expressions: solving a Sudoku Puzzle . Ok, that's not the type of problem which matters a lot to database users... but it still feels good when he mentions that MySQL's implementation (which I wrote), is the fastest of all three tested DBMSs 😊.