The Impact of Query Rewrite on MySQL Query Optimization
When processing a query, every relational database vendor takes a number of similar steps to parse, validate, and optimize a query. MySQL does the following:
- Validates the query syntax — does it use valid operators, are the clauses in the proper order, etc.
- Validates the query semantics — are the the objects (tables, views, columns) valid database objects in the database, etc.
- Transforms the query — rewrites the query so the optimizer can more easily choose the best path.
- Optimizes the query — determines the best execution path based on a large number of factors including statistics about the table and index data.
When analyzing a database performance problem, it helps to have an understanding of what is happening during step 3 and 4 above.
Occasionally I find a blog article that does a great job of explaining one these processes for a particular database vendor and I would like to share one that I found today. This excellent post was written by Guilhem Bichot and does a great job of illustrating the transformation (query rewrite) step and the impact of this step on the execution path selection.
This post describes the process in MySQL but the principles are similar for any database vendor.
Note that the example that Guilhem uses shows how the database itself may introduce a query hint while rewriting the query. I have mentioned in the past that I avoid hints because over time the usefulness and necessity of the hint may change and it may indeed hurt you. Of course, since the query rewrite process is executed in real time it is not subject to this problem.