QUERY OPTIMIZATION in Font

Generator QR Code 2d barcode in Font QUERY OPTIMIZATION

| ------------------| | | PROJECT | | | ------------------| | | Access Method: | | | | iterator | | | ------------------| | | expert_mysql.directorate | | | | | | | | | | | | | | | V | | | ------------------| | | | PROJECT | | | | ------------------| | | | Access Method: | | | | | iterator | | | | ------------------| | | | | | | ---------------------------| | | | | | V V | | ------------------| | | JOIN | | | ------------------| | | Access Method: | | | | iterator | | | ------------------| | | | | | | | V | | Result Set | +----------------------------------------------------+ 36 rows in set, 2 warnings (0.06 sec)

mysql> SELECT DBXP * FROM staff JOIN tasking ON staff.id = tasking.id mysql> WHERE staff.id = '123456789'; +------------------------------------------------+ | Execution Path | +------------------------------------------------+ | expert_mysql.staff | | | | | | | | | | | V |

| ------------------| | | RESTRICT | | | ------------------| | | Access Method: | | | | iterator | | | ------------------| | | expert_mysql.tasking | | | | | | | | | | | | | | | V | | | ------------------- | | | | PROJECT | | | | ------------------- | | | | Access Method: | | | | | iterator | | | | ------------------- | | | | | | | ---------------------------| | | | | | V V | | ------------------| | | JOIN | | | ------------------| | | Access Method: | | | | iterator | | | ------------------| | | | | | | | V | | Result Set | +------------------------------------------------+ 36 rows in set (0.06 sec) mysql> Notice how the query plans differ for each of the statements entered. Take some time to explore other query statements to see how the optimizer optimizes other forms of queries.

Summary

I presented in this chapter the most complex database internal technology an optimizer. You learned how to expand the concept of the query tree to incorporate a query optimizer that uses the tree structure in the optimization process. More importantly, you discovered how to construct a heuristic query optimizer. The knowledge of heuristic optimizers should provide you with a greater understanding of the DBXP engine and how it can be used to study database technologies in more depth. It doesn t get any deeper than an optimizer!

In the next chapter, I will show you more about query execution through an example implementation of a query tree optimization strategy. The next chapter will complete the DBXP engine by linking the heuristic query optimizer using the query tree class to an execution process that surprise also uses the query tree structure.

The following lists several areas for further exploration. They represent the types of activities you might want to conduct as experiments (or as a class assignment) to explore relational database technologies. 1. Complete the code for the balance_joins() method. Hint: You will need to create an algorithm that can move conjunctive joins around so that the join that is most restrictive is executed first (is lowest in the tree). 2. Complete the code for the cost_optimization() method. Hint: You will need to walk the tree and indicate nodes that can use indexes. 3. Examine the code for the heuristic optimizer. Does it cover all possible queries If not, are there any other rules that can be used to complete the coverage 4. Examine the code for the query tree and heuristic optimizer. How can you implement the distinct node type as listed in the query tree class Hint: See the code that follows the prune_tree() method in the heuristic_optimization() method. 5. How can you change the code to recognize invalid queries What are the conditions that determine a query is invalid and how would you test for them 6. (advanced) MySQL does not currently support the intersect operation (as defined by Date). Change the MySQL parser to recognize the new keyword and process queries like SELECT * FROM A INTERSECT B. Are there any limitations of this operation and are they reflected in the optimizer 7. (advanced) How would you implement the GROUP BY, ORDER BY, and HAVING clauses Make the changes to the optimizer to enable these clauses.