 Hello everyone. Yeah, so you have heard a lot of discussions, presentations about other components in MySQL. Now let's talk about queries. So I'll cover the new features that we have implemented in the MySQL later to optimize it. Obviously it's not, I mean we don't have time, 20 minutes is not a time to cover all the features. So I have selected a few of them. So let's start with that. This is a safe August statement which says that whatever I say you cannot sue or acquiesce on that. So this basically says that. So let's just take some time to get a glimpse of it and move on to the next slide. So apart from my colleagues, my colleagues and whoever has worked with Optimizer. So rest of the folks, have you heard about the Optimizer in MySQL? Have you worked on any of the Optimizer features in MySQL as such? Okay, that's different. Okay, so I was expecting this based on my previous experiences in Porsche CSO. I thought I'll just do a small recap of the basic step you can cover in the Optimizer. So I took this example from one of the blogs of Morgan Docker. So he explains Optimizer as a map navigation system. So what is it? It's similar to something like this. So you have a source, you have a destination and you want to reach your destination in the best possible way. So this is similar to what Optimizer does in MySQL. So it finds out the best possible way to execute it very well. And so it's similar to this map navigation system that we use in ordinary activities wherever you go, wherever you have to find something else. So it's exactly similar to that. So here is a small example, like I have a query which joins two tables and it finds out the city, count of the number of cities in India. So how does it optimize it? So here is what the query Optimizer does. So for each of the queries or the tables that are involved in the queries, the Optimizer tries to collect the statistics and the beta data of those tables. So without that it cannot find out the exact way to the best possible way to execute the query. Now what it does after getting those statistics is it generates a query execution. I mean it might generate a lot of query execution plans based on the statistics that it gets, but the one that it chooses is the best possible plan to execute the query. So that's how the Optimizer works. So we call the Optimizer as the heart of a database. So it's basically what helps us. So at the end of the day, all the DBAs, the application developers, all the developers are very fast, very performing. What are the features that I can use inside the query and stuff like that. So this is what the Optimizer helps us in doing that. And at the end it gives you the results. So it shows the count of all the cities in India. So this is basically the job of the Optimizer. Looks easy, but it is not. It's a very, very complicated module in MySphere which takes care of all the things that we see. And we never believe that this can be done. So when you work with MySphere Optimizer, you get to know about a few of the terms that we use. So the first is the cost. So every query has a cost involved in it. And what the Optimizer does is it calculates the costs of executing that query and it brings out the plan with the minimum cost. So what is the cost? It's a logical unit that represents the resource usage for executing that query. Next comes the explain plan. So the explain plan shows you the different ways that the Optimizer is going to execute the claims. What is it going to do? Which tables it is going to join? What is the join order? What are the indexes that it's going to use? So all those data can be found out from the explain plan. Now to give those data, you need to have some information about the tables, the columns that are part of the query. So that information is achieved from the metadata and statistics of the table. So the metadata says you the information about the table like what are the number of indexes that are there, what is the nullability of the column, stuff like that. And the statistics says you about the size of the table, the cardinality of columns, etc. So these two are the very, very important things that you need to generate the best possible data. Now sometimes that may not happen. The reason, the basic reason is because you may not have the exact or the updated statistics of the table. So you might have come across many of the blogs or many of the scenarios where the DBS says that okay, my query is not performing well, my query is getting slowed down. And this basically most of the time the reason is because you may not have, the first thing that we say is maybe you may not have the updated statistics about the table information in your data. So these four things are the ones that are going to help you generate the best possible query solution. So I have covered the optimizer basics. So I will cover the three things that I have decided to speak about is the index improvements, the optimizer hint improvements, and something called as the internal table storage. So let's go about looking at the index improvements. So I have picked up this schema. This is a vertex database schema which has the JSON columns. In this, this is freely available. You can use it. You can download it and you can use it in your projects or whatever you want. So I have picked up this schema because this will give you a good idea of what the problem that I am going to discuss and the solution that we have as to how we have implemented the solution and the optimizer. So let's talk about functional index. So what is the functional index? Has anyone worked in Oracle or any other RTBS? So you must know about functional index. So, and who knows about indexes in such? Not you guys, no, no, no, no. Indexes, yeah, so you know about indexes, right? So create an index on a column. But here what we are doing is we are creating an index where at least one of the index part is a function instead of a column. So you can have a function. Define on, I mean, the index is created on a function. And the way to do it is just like any other syntaxes that you have created in this or the table and then delete in this. So I'll take an example where I'm going to find out the population of the city table based on certain range condition. So this is my problem statement. I have this schema which is this and I'm going to find out the population of cities in the city table based on certain ranges. So what I do is I create an index on the city table where I'm using a JSON function. But there's JSON extract to extract the population data out of the column in that table. Now the details of the JSON functions and the JSON schema data type is going to be covered by Chaitra in the next session. So I'll just not go into that. But this is a function which can extract the information out of a JSON column. And what I do next is I create this index on this table. So you see this hide one index is created and I do this. So my query is select info from city and you see that I am using the same function. And then what I'm doing is I'm giving a range of 300 to 500. So this is just a small example to show you as to how this works. This is how you're going to use this function in your query. So similarly, you can have a lot of complicated stuff defined inside a function. And then you can excuse that as a query and it will show you the population of info about the population of within that certain range of 300 to 500. So this is what is a function of index in an action. Now, yeah, so if you look at the explain plan, I was talking about the explain. So if you look at the explain plan, it really says that it is using that index. So possible keys are I want to execute that way. So this is a way of finding out if your index is being used by the optimizer or not. So you can explain query and it will show you the output. If you look at, once you've created the index, now you have to, you can see what are all the indexes created in your table. So show indexes shows you all the indexes that are created in the table. I've just removed the other index part and I've just shown you the index part from the. So what type of index is created for the function index? It's created between index and this is the expression that you use. So whenever you're creating a functional index, if you do a show index from city and you go to that particular index definition, it will show you the exact expression that is being used for creating that index. So this is how you can figure out what are all the indexes that are created. So that was all about functional index that I wanted to cover. You can find more information about this in the highest-level docs. So let me move to the next index improvements that has been implemented in a.to. I picked up this because this was contributed by Facebook and we have picked up their implementation and we have used it. So this is the way of skipping index scan. So whenever you have a multi-column index, what if you have a column index defined on multiple columns and you have something like the index is defined on ABC, but your query is like select start from T1 where B is less than 3. So what previously used to happen was that it used to scan the whole index while executing the query. Now this improvement is what it will do is that it will get to not scan the whole index, but it will scan the whole index. And this is useful only when the condition is not on the first part of the index. So if you have select start from T1 where C is less than 3, it's going to use this feature. So this was used by, this was a patch by Facebook and we have implemented this patch from there. Okay, now the next topic that I wanted to discuss about is regarding the optimizer hints. So there were a lot of hints where basically they're an optimizer from long-term back, but in a.to we have started implementing a lot of other hints in the optimizer. So these are the set of hints that we have implemented in a.to. Like there are join order hints and there's hints for setting the system variables, hints for views and derived tables for merge and no merge, and then we have hints for index merges also. So I'll just go through each of them briefly. Okay, so the join order hints is basically, so if you, let's say that you have a query which was performing well and after certain, let's say after certain something has happened because of which your query is not performing well and you see that the join order has changed. So the way you can force to use the same join order is by using this hints. You can just mention that join order, which table you want to join first and then you can mention this and you can see that the explain of the statement shows that it's joining on that order that was previously used. Similarly, we can use this index merge hints for merging indexes on tables and you can use it using this kind of like indexes you can specify that you want to merge and so you can merge those from multiple rates cans onto a single table. Okay, so session variables hints. So there are a lot of session variables or variables in my skill that you can basically use for improving the performance of a query. So what used to happen was like if you set the variables for a particular session, it used to persist for that session. Now, it may happen that the next query that comes does not require that variable to be set. So what you can do is you can basically set the variables for that exact query and this is how you do it. So you set the foreign key checks for inserts because insert might take a lot of time. You can specify a sort buffer size for a particular query. So this helps you to have that session set for that particular query, not for the whole session, not for the whole global session. So this is the set bar by optimizing that we have provided in .do. Okay, so now moving on to the internal temp table. I thought Colin was talking about the internal temp table storage engine in the body. So previously for certain optimizer queries or my skill queries, we need to use this internal temporary tables for sorting is one of the examples. Now we used to use the memory engine previously and so now what we have come up with this is the internal temp table storage engine. Now this was introduced in .do.to and this is not something that the user can create. You don't need any user interaction for using this feature. This is something that is used by optimizer and it's created on the fly. Now it's based on the memory that you have. So if the internal temp table storage engine memory exceeds, it can overflow into the disk and that's how it works. So it's used basically for complex queries like CDs, commentable expressions. Then if we were listing plus order by there are a bunch of other query types that would use this feature. It's there in my skill documentation but this is the internal temp table which was introduced. Now the improvements that we have in the latest versions of .do.to is that it now supports JSON texts, block and geometry data types. So these are the improvements that it has and it supports overflow to disk and there's basically no overhead when it comes to converting from .do.to to disk. So this is the improvements for internal temp tables and so there are certain limitations. So basically there are certain variables by which you can control the size of the temp table. And as Mike mentioned in his performance schema improvement, so you can monitor the usage of memory and disk usage with the performance schema tables that we have for internal temp tables. So we did some benchmarking on the JSON variant of suspension tests when it uses internal temp tables. And we see a lot of more through. So suspension is one of the internal performance tests that we have and whenever we have to benchmark anything that's basically the thing that we do it from our side. So what we have done is we have done this suspension test and then we have found out that the internal temp table is faster than the internal temp tables that we had previously. So and this is there in the MySQL server team blog so you can read about this suspension benchmarking that we have done. Okay so yeah so I am done with the last slide. So if you have any questions I can take it up now or you can catch me. Temporary table space no. So basically these are internal temp tables which kind of like it's like a staging table. That holds the temporary data when we are doing some complex queries like sorting. Exactly. But table spaces is, you are talking about table spaces. Okay then maybe you can say table spaces. Okay thank you so much and the next session.