This section discusses key optimizer concepts, terminology, and how these are reflected in the MySQL server source code.
This description uses a narrow definition: The optimizer is the set of routines which decide what execution path the DBMS should take for queries. MySQL changes these routines frequently, so you should compare what is said here with what's in the current source code. To make that easy, this description includes notes referring to the relevant file and routine, such as “See: /sql/select_cc, optimize_cond()”. A transformation occurs when one query is changed into another query which delivers the same result.For example, a query could be changed from
Most transformations are less obvious. Some transformations result in faster execution.
The Optimizer Code
This diagram shows the structure of the function handle_select() in /sql/sql_select.cc(the server code that handles a query):
The indentation in the diagram shows what calls what.Thus you can see that handle_select()calls mysql_select() which calls JOIN::prepare() which calls setup_fields(), and so on. The first part of mysql_select() is JOIN::prepare() which is for context analysis, metadata setup, and some subquery transformations. The optimizer is JOIN::optimize() and all its subordinate routines. When the optimizer finishes, JOIN::exec() takes over and does the job that JOIN::optimize() decides upon. Although the word “JOIN” appears, these optimizer routines are applicable to all query types.The optimize_cond() and opt_sum_query() routines perform transformations. The make_join_statistics() routine puts together all the information it can find about indexes that might be useful for accessing the query's tables.
MySQL Related Interview Questions
|PHP Interview Questions||MySQL Interview Questions|
|PHP+MySQL Interview Questions||Drupal Interview Questions|
|MYSQL DBA Interview Questions||PHP5 Interview Questions|
|WordPress Interview Questions||Joomla Interview Questions|
|CakePHP Interview Questions||CodeIgniter Interview Questions|
|PHP7 Interview Questions|
A Guided Tour Of The Mysql Source Code
Important Algorithms And Structures
How Mysql Performs Different Selects
How Mysql Transforms Subqueries
Mysql Client/server Protocol
Prepared Statements And Stored Routines
Myisam Storage Engine
Innodb Storage Engine
Writing A Custom Storage Engine
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.