Features I'd like to see in MySQL: CTEs

The pace of MySQL engineering has been pretty brisk for the last few years. I think that most of the credit is due to Oracle, but one should not ignore Percona, Monty Program, Facebook, Google, Twitter, and others. Not only are these organizations (and the individuals I haven’t mentioned) innovating a lot, they’re providing pressure on Oracle to keep up the improvements, too.

But if you look back over the last few years, MySQL is still functionally a lot like it used to be. OK, we’ve got row-based binary logging – but we had binary logging and replication before, this is just a variation on a theme. Partitioning – that’s a variation on a theme (partitioned tables are a variation on non-partitioned tables). Performance – same thing, only faster. And so on.

I’m painting things with too broad a brush. There’s actually a lot of stuff that’s NOT just a variation.

But if you look around at what’s out there in other open-source DBs, there’s a lot of innovation, particularly in PostgreSQL, which has had CTEs (common table expressions) for a while. CTEs are not a variation on a theme. They are major new feature, analogous to going from no-subquery-support to supports-subqueries. They enable a lot of things like recursive queries, making a SQL database useful in many more types of situations – think graph-processing, for example, which is downright annoying without them.

Will we see CTEs in MySQL soon?

I'm Baron Schwartz, the founder and CEO of VividCortex. I am the author of High Performance MySQL and lots of open-source software for performance analysis, monitoring, and system administration. I contribute to various database communities such as Oracle, PostgreSQL, Redis and MongoDB. More about me.