(mysql.info.gz) Controlling optimizer

Info Catalog

(mysql.info.gz) Server parameters

(mysql.info.gz) Optimizing the Server

(mysql.info.gz) Compile and link options

 
 7.5.3 Controlling Query Optimizer Performance
 ---------------------------------------------
 
 The task of the query optimizer is to find an optimal plan for executing
 an SQL query. Because the difference in performance between "good" and
 "bad" plans can be orders of magnitude (that is, seconds versus hours
 or even days), most query optimizers, including that of MySQL, perform
 more or less exhaustive search for an optimal plan among all possible
 query evaluation plans. For join queries, the number of possible plans
 investigated by the MySQL optimizer grows exponentially with the number
 of tables referenced in a query. For small numbers of tables (typically
 less than 7-10) this is not a problem. However, when bigger queries are
 submitted, the time spent in query optimization may easily become the
 major bottleneck in the server performance.
 
 MySQL 5.0.1 introduces a new more flexible method for query optimization
 that allows the user to control how exhaustive the optimizer is in its
 search for an optimal query evaluation plan. The general idea is that
 the fewer plans that are investigated by the optimizer, the less time
 it will spend in compiling a query. On the other hand, because the
 optimizer will skip some plans, it may miss finding an optimal plan.
 
 The behavior of the optimizer with respect to the number of plans it
 evaluates can be controlled via two system variables:
 
    * The `optimizer_prune_level' variable tells the optimizer to skip
      certain plans based on estimates of the number of rows accessed
      for each table. Our experience shows that this kind of "educated
      guess" rarely misses optimal plans, while it may dramatically
      reduce query compilation times. That is why this option is on
      (`optimizer_prune_level'=1) by default. However, if you believe
      that the optimizer missed better query plans, then this option can
      be switched off (`optimizer_prune_level'=0) with the risk that
      query compilation may take much longer. Notice that even with the
      use of this heuristic, the optimizer will still explore a roughly
      exponential number of plans.
 
    * The `optimizer_search_depth' variable tells how far in the
      "future" of each incomplete plan the optimizer should look in order
      to evaluate whether it should be expanded further.  Smaller values
      of `optimizer_search_depth' may result in orders of magnitude
      smaller query compilation times. For example, queries with 12-13
      or more tables may easily require hours and even days to compile
      if `optimizer_search_depth' is close to the number of tables in
      the query. At the same time, if compiled with
      `optimizer_search_depth' equal to 3 or 4, the compiler may compile
      in less than a minute for the same query. If you are unsure of
      what a reasonable value is for `optimizer_search_depth', this
      variable can be set to 0 to tell the optimizer to determine the
      value automatically.
 
 

Info Catalog

(mysql.info.gz) Server parameters

(mysql.info.gz) Optimizing the Server

(mysql.info.gz) Compile and link options