« MySQL Development Model | Main | Writing a Storage Engine for MySQL »

April 21, 2005

MySQL 5.0 Query Optimizer

At MySQL 2005 Listening to Timour Katchaounov talk about the query optimizer in MySQL 5.0.

(I missed the introduction while enroute to the room).

The query processing steps:

  1. Parser (builds tree)
  2. Preprocessor (checks syntax, columns)
  3. Optimizer (generates query execution plan)
    • query transformation
    • search for optimal execution plan
    • plan is refined
  4. Query sent to execution engine
A query has only a few pre-defined operations, which eases the task of processing a query:

MySQL uses a left-deep linear plan for executing a query. All of the tables fall into a single line. Many other systems use the bushy plan, which is more tree-like.

Timour shows a large query with 5 or 6 WHERE conditions and steps through the process of how the query is parsed.

In optimizing a SQL statement there is quite a bit of analysis of the cost of a query. The cost is calculated by looking at things like how many times the disk will need accessed, the number of pages per table, the length of the rows and keys and the data schema (key uniqueness etc). Determining costs involves mathematical operations to determine the cost using different methods. The type of storage engine isn't considered in the cost.

MySQL 5.0 has greedy searching. It doesn't consider everything, just gets enough information to find a good path and then moves on.

Posted by mike at April 21, 2005 2:26 PM