DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(mysql.info.gz) Optimizer Issues

Info Catalog (mysql.info.gz) Query Issues (mysql.info.gz) Problems (mysql.info.gz) 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 will
      process 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' EXPLAIN.
 
    * Use `ANALYZE TABLE TBL_NAME' to update the key distributions for
      the scanned table.   `ANALYZE TABLE' ANALYZE TABLE.
 
    * Use `FORCE INDEX' for the scanned table to tell MySQL that table
      scans are very expensive compared to using the given index.  
      `SELECT' 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'.  `SELECT' 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 will cause more than 1,000 key seeks.  See
       Server system variables.
 
 
Info Catalog (mysql.info.gz) Query Issues (mysql.info.gz) Problems (mysql.info.gz) Table Definition Issues
automatically generated byinfo2html