(mysql.info) controlling-optimizer

Info Catalog

(mysql.info) server-parameters

(mysql.info) optimizing-the-server

(mysql.info) 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 a 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's performance.
 
 MySQL 5.0.1 introduces a 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 spends in compiling a query.  On the other hand, because the
 optimizer skips 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, and 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 a better query plan, this option can be
      switched off (`optimizer_prune_level=0') with the risk that query
      compilation may take much longer. Note that, even with the use of
      this heuristic, the optimizer still explores a roughly exponential
      number of plans.
 
    * The `optimizer_search_depth' variable tells how far into the
      `future' of each incomplete plan the optimizer should look 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) server-parameters

(mysql.info) optimizing-the-server

(mysql.info) compile-and-link-options