(mysql.info) optimizer-issues

Info Catalog

(mysql.info) query-issues

(mysql.info) problems

(mysql.info) table-definition-issues

 
 A.6 Optimizer-Related Issues
 ============================
 
 MySQL uses a cost-based optimizer to determine the best way to resolve
 a query. In many cases, MySQL can calculate the best possible query
 plan, but sometimes MySQL doesn't have enough information about the
 data at hand and has to make `educated' guesses about the data.
 
 For the cases when MySQL does not do the "right" thing, tools that you
 have available to help MySQL are:
 
    * Use the `EXPLAIN' statement to get information about how MySQL
      processes a query. To use it, just add the keyword `EXPLAIN' to
      the front of your `SELECT' statement:
 
           mysql> EXPLAIN SELECT * FROM t1, t2 WHERE t1.i = t2.i;
 
      `EXPLAIN' is discussed in more detail in  explain.
 
    * Use `ANALYZE TABLE TBL_NAME' to update the key distributions for
      the scanned table. See  analyze-table.
 
    * Use `FORCE INDEX' for the scanned table to tell MySQL that table
      scans are very expensive compared to using the given index. See
       select.
 
           SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
           WHERE t1.col_name=t2.col_name;
 
      `USE INDEX' and `IGNORE INDEX' may also be useful.
 
    * Global and table-level `STRAIGHT_JOIN'. See  select.
 
    * You can tune global or thread-specific system variables. For
      example, Start `mysqld' with the -max-seeks-for-key=1000 option or
      use `SET max_seeks_for_key=1000' to tell the optimizer to assume
      that no key scan causes more than 1,000 key seeks. See 
      server-system-variables.
 

Info Catalog

(mysql.info) query-issues

(mysql.info) problems

(mysql.info) table-definition-issues