Articles

A new, simple way to figure out why your recursive CTE is running away

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 .

Antijoin in MySQL 8

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.

Supporting all kinds of outer references in derived tables (lateral, or not)

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 -lateral ou

Support for LATERAL derived tables added to MySQL 8.0.14

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!

Row numbering, ranking: how to use LESS user variables in MySQL queries

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

Sudoku Recursive Common Table Expression Solver

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 😊.

Presentation at PerconaLive in Dublin, 25-27 Sep 2017

Back from the PerconaLive conference! It was nice meeting some old faces 😃 There I presented MySQL's Common Table Expressions; check out my slides , with example queries and some details about the implementation. I got two suggestions from the audience: add to views the same single-materialization which we have in CTEs now (slides 19-27) support outer references in derived tables and CTEs. The first point would speed up queries which reference a view more than once. The second point would make it easier to write certain queries, where a subquery depends on the outer query's current row and this subquery contains a CTE. Will I get to work on any of these? Time will tell...