 When I think it's the first developer talk today, it's about SQL and all the talks before me were about roughly administration stuff and more for administration. And this is real for development. Yeah, improve your SQL. Right, let's see if it's work. No, it won't work. It doesn't matter. I'm going to take this. It's on. I have some rules, like every speaker. I can't do an individual consulting within this talk. Of course, it's very important. My slides don't have any standalone meaning. So when you find the slides later on in the internet, I'm pretty sure I will upload them. You can't say, whoa, that's faster than that. Look at the slide. No, you have to hear what I say to that slide because they have no standalone meaning. And of course, a copyright, my slides. Don't copy them and so on. Oh, that's OK. So who am I? My name is Susanna Holtzgriffe. I married in 2014. It was very funny because nobody knows me anymore. That I was the first female developer at the PostgreSQL project. And I worked for MySQL AB in Sweden, worked for Sun, worked for Oracle. And then I left. And today I'm working as freelance consultant for MariahDB, MySQL and PostgreSQL. So I don't mind what you use. I just will help you. I don't mind what system you use. That's me. I mostly do it in Germany or close to Germany, Belgium, Netherlands, London, or what's good to reach from Germany because I made it worldwide and had so much long-distance flights that I said, OK, I need to retire. I just need to do it in my own area. So CTE, common table expressions. I think it was in 2010 or so. PostgreSQL implemented common table expressions. And I said, wow, that's great. Especially because you had, you see it, you had a real performance gain of it measured to correlated subselects. And I said, wow, that's great. So I implemented it in my standard default trainings since the day. And last year I listened to a talk from Peter Saitsev. He introduced CTEs in MySQL 8.0. I said, OK. And I know MariahDB, I think the version of MariahDB was half a year earlier released than MySQL. So now both also have CTEs and I really love CTEs. And so I said, I will speak about it. And as I said, in PostgreSQL, the big performance gain you have in correlated subselects. Who is using correlated subselects? Or let me show you what it is correlated subselects. Just remember, it was when you have an outer select which you are using in the inner select here. So where inner bar is outer bar. That's a correlated subselect. Who has such use cases or knows somebody who has these use cases? OK. I have lots of customers having these use cases. Very often I see queries with correlated subselects because they just have this use case. And usually they are really slow. It's often a complaint. The query is too slow. And this is a common table expression. Common table expressions start with with. And a common table expression is like a view. You have a view before the original select. So here you first have a view. You first select the average what you need and then make a join from the table, joining the table with your view and forgetting the result. As I said in PostgreSQL, it was a big performance gain. And I had customers, they said my query needs 20 minutes. I helped them to rewrite it to two common table expressions and the query finished in two seconds. It was a real gain. I won. The customer booked me again. Today it's not so fast. The common table expression still is same fast. But the optimizer just got better on the correlated subselects. So that common difference isn't so big anymore but there's still a performance gain. But there's not just the performance gain. It's also, I think it's better readable. You see it in my examples. When you have this before, this is just better readable. I have one example here. It's an easy one. It's a ticket system. You know there's penalties for wrong parking and so on. It's a test DB. I made this two or three tables and 100 million rows today. Randomly, I randomly filled. So, okay, that's a complex query. Honestly, I have a second complex, more complex query. And I sometimes think about my, what the hell is this thing doing? It's same here. And when I have it in CTE, it's much pretty clear what it's doing because you don't have the subselect and subselect and so on. So you will have a better understanding. It's easier to read. So it's an easy one. I just wanted to know which towns got more money than the duration. So top towns in ticket for parking ticket penalties. So, yeah. When I prepared the slides, I set up two VMs, two virtual machines on the same machine, on the same host. One core or two cores. I can't remember exactly a similar. Also, I installed, by using the packages, the Debian packages from the download pages of MySQL.org and MariahDB.org. And, yeah, I set up InnoDB, Buffer Pool a little bit. Those identically to my run. And I had, by accident, on my computer I had a PostgreSQL running. It isn't the actual version, so it doesn't matter. Because I know what happens on PostgreSQL. So I run this query. I set up this test database on this two systems. Yeah. And the performance gain on this query, as I said, one million rows. In MySQL 8.0.14, the CTE query needs half of the time as the sub-select. Just to compare PostgreSQL, my PostgreSQL needed a third of the time. It doesn't matter. MariahDB has no performance difference at the moment in CTEs or sub-curries. Somebody of the developers told me yesterday, he knows why. I didn't figure, I couldn't find out a performance gain in MariahDB, but in MySQL I also have a huge performance gain. As I said today, the optimizers are much better in sub-curry, sub-force sub-curries than a few years ago. So, that's a more complex query. You see it here, and that's something I always ask me, what the hell is it doing? I know it, but, yeah. So, that's the typical query I get from customers. Sometimes more often, more complex. And when I ask the customer what is it doing, and I get lots of complaining. So, with the risk query, I see, okay, here we have, give me the top, towns again, who made the violation, most money of violations, and then split it again, select something from them. So, that's the CTE rewrite. And that's the performance gain. MySQL, MySQL is a 10% faster, even PostgreSQL just is 2-3rd fast, just needs 2-3rd of the time. I had even no performance gain. I know from PostgreSQL when the queries are too complex, then you also have no performance gain. So, you always have to test if it's still there. And how much you need to cash in the view. And I guess it's similar to MySQL. My machine just was smaller here. Yeah, that's the CTE. That's the one, as I said, CTE is a good future feature. I once had a training. It was MySQL training, or this training company said it's MySQL training. But you know big companies, the employee isn't allowed to book the training by its own, by its own. So, there is somebody in the company booking this training. And I think this guy who booked the training was, he thought the Y in MySQL is a type rule. So, all the guys, all the students in the training wanted MS SQL, instead of MySQL. It was a developer training. And I figured out, oh, they were fit on writing CTEs. It was when it just was implemented in PostgreSQL. So, I was so surprised. So, then I know CTEs are very well known in MS SQL. I didn't know before. But, yeah. Yeah, CTEs. But there's another fancy fancy stuff you can do with CTEs. That's recursion. Just one example, recursion. You can write recursive queries with using CTEs. This just counts the numbers from 1 to 100, 50, the 5050 will be the result. You can do such stuff. I know that's always when CTEs are introduced, or say, hey, we can do recursion. I've never seen this in also at PostgreSQL, 10 years now. I've never seen a customer using CTEs for recursion. Usually, they use it for rewriting complex queries or really correlated subselects. But, you can do it. It's nice. Yeah. That's something neither MySQL nor MyIDB today have implemented, but maybe that's the future that you can do using CTEs also in DML, so in delete, or insert, or update. Yeah. Delete, insert, or update. Just a feature. Let me look. I have no time there. So, any questions up to here? No? Because I have a second topic here. It's window functions. Window functions are, I don't know when they are, they were implemented in the MySQL and MyIDBs, I think, roughly 10 years ago, somebody here from the developer knows it. Since when? Window functions? I don't know. You don't know? Yeah. It's some years in both in MyIDB and MySQL. But they are still very unknown. That's why I thought, okay, I take this as second topic. Window functions make window from a result, or make a petition. So, it's easier to explain it by examples. I made the average example. You can do window functions on almost all aggregate functions, aggregates. I just took here the average function, and I took, I made a town, I made a new table with violations, number of violations in town, just because I have, I not want millions of results, just 10. So, what is it doing here? It's doing the average over partition by town. So, the average of number of violations over partition by town. Partition by town, not good by town, partition by town from violation. And this is the result. This is the result on MyIDB. You will see the MySQL result is similar. You see at the end, the, that you have the average of, for example, of Aachen. And the average of Bielefeld and the average of Aachen at the end. And it's the average of this three-wire relations and not just for the single one. When you do it by group by, you will get an average for each row and not, not partitioned by this town. You also can do it with some and other aggregate functions. I filled the tables randomly. That's why you have different values in the average and in the number of violations in MySQL, but the result is similar. Also performance, I didn't feel any performance here because just ten rows on the table. So, not so much. But it's a really great feature when you need some average. It's really a use case I have really often. Not with average, but with some. Another example or one of great feature which I see my customer using very, very often is WENC. WENC, here we have, I wanted to WENC the charge and partitioned by town, order by charge, of course the surprise, it's money, DESC as WENC. And I figured out when I made the test that WENC is a reserved keyword in MySQL. Almost all this window functions are reserved keywords in MySQL. So in MySQL I took underscore WENC instead of WENC for naming it. So, and the result for the ranking is, you see, you have here Aachen, this is one, this is WENC, top, this is a second, you have here top on Bielefeld is driving without license and second for a third and so on. And top of Aachen is driving without, oh funny, driving without license and the other is driving under alcohol at the end. So you can have WENC. I see this very often used in online games. I have some gaming customers and of course they like this function for WENC. But WENC very often is used. We have another feature or two other features. I just, oh, sorry. I don't want, of course, MySQL similar results. Same here. And yeah, lack. Lack and lead. You can do this window functions, this window. Also, you just don't just have the keyword partition here. You also can do something like over-order by something. And be careful when I made this slide for lack. I made a DSC for lead. I made it without. We will see it later. So lack what's doing it. As I said, lack also is a reserved keyword in MySQL. You see it at MyIDB. Lack always shows you the row before the other row. So, Moscow, London, Berlin, Madrid, Roma, and so on. Sorry to know which row is which result is before that. Same here. And lead is vice-versa, of course. As I said, in my example, I just forgot the DSC at the end. So lead is the after, so it's just, as you see, it's sort of vice-versa. It's always what it's, yeah, what comes next. So, Brussels means Vienna, Paris, Rome, Madrid. What comes next? Yeah, I know. I'm also done. I just have to slide. MySQL is similar, as you see. Yeah. And more about window functions. There are lots of more. You can find lists and also more descriptions and mariahdb.com, knowledge base, English libraries, window functions, and on dev.mySQL.com, docsrefman8.org, English, window functions, descriptions.html. So, they both have a good documentation for it. Yes. And that, thanks for listening. I think both cities and window functions are very, very fancy stuff. And let's go to use them.