About MySQL development (Optimizer mainly)

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

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