MySQL 5 Administration-Part 1 Tutorials
Architecture / Query Optimizer
Subtitles of the Movie (MySQL 5 Administration-Part 1 / Architecture / Query Optimizer)
The next attraction in our journey of discovery is the query optimizer. So the query optimizer is the thing that decides what's the best way of executing this query. What it does is it takes the parse tree that's passed to it and tries to determine the best execution plan. And it does this by accessing certain resources that are provided for it. For example it has access to index and table statistics. So it has an understanding of not only the fact that certain indexes actually exist, certain columns or combinations of columns are indexed. But also it has statistics of those indexes. It has an idea of how efficient the index is. You know how many unique elements there are in that versus how many total rows there are in the table. So that makes it a more selective index if a higher percentage of the elements there are unique. It also understands the skew of the index. So is it heavily weighted toward one end or the other of the index? Or toward the middle? Table statistics also come into bear here so it has a lot of information from which to determine how best to proceed. It also has a number of cost based algorithms and other rules for optimizing the query in the first place that it brings to bear for determining what the execution plan is going to be. Ultimately it tries to come up with an execution plan that's going to minimize the total number of reads that have to performed in carrying out the execution of the statement. It passes the execution plan that it comes up with then to the execution engine which works kind of like a dispatcher at a trucking company. It has certain work orders and it knows that certain tables have to be accessed and it knows which engines are the engines that manage those tables. So it brings the appropriate engines into the meeting room, passes out the work orders, tells them in what order they're supposed to be doing these things. And then the engines go off, get the information from the database tables, bring it back to the execution engine. The execution engine then does any other things that it has to do, aggregation, calculations order bys, group bys. And then it packages everything up really nicely and sends it back to the client. Now the optimizer itself though has to determine what indexes there are to work with. It's get this from again the parse tree and the information that's in there. It then balances the cost of using one of the indexes, against just doing a table scan in the first place. It also balances the cost of using one index versus another. Part of the execution plan is going to be the order in which tables are addressed when we are doing a join. So it has to determine the join order from all this as well deciding whether one table is best to start with because it has fewer rows. That's usually the direction that it takes it. It eliminates unnecessary tables that might be named inside of the query but really don't have anything to do with retrieving the information. And also unnecessary parts of the Where Clause information. Sometimes those things are a little bit redundant or can be superseded by some other part of the Where Clause. So it looks for those kinds of things to act as shortcuts. It also tries to find indexes that will help it deal with group by and order by operations. And ultimately it still has to make an educated guess because it doesn't have the luxury of being able to go out and actually try all the different possibilities. It has to mathematically determine what is likely to be the best way of going about performing the operation. And sometimes it doesn't get it right. So we have ways of bypassing all of that and adding little hints here and there that might help it along. But we have to be careful in doing those things too because we might set it on the right path today but a couple of weeks down the road, those same hints might put it on the wrong path. So there's a little art and a little science to this as well. Alright. That's enough about the query execution but there are some other things that we also need to deal with that are inside of the MySQL server. In the next lesson, I'd like to take a brief look at the plug-in interface which was new in version 5.1 and has been expanded in version 5.5.
Tutorial Information
| Course: | MySQL 5 Administration-Part 1 |
| Author: | David Swain |
| SKU: | 34307 |
| ISBN: | 978-1-61866-086-2 |
| Release Date: | 2012-12-31 |
| Duration: | 16 hrs / 171 lessons |
| Work Files: |
Yes |
| Captions: | No |
| Compatibility: |
Vista/XP/2000, OS X, Linux QuickTime 7, Flash 8 |
VTC Sign up & Benefits
- Unlimited Access
- 81,350 Video Tutorials (14,200 free)
- Video Available as Flash or QuickTime
- Over 715 Courses
- $30 for One Month Access
- Multi-User Discounts Available
