Optimiser Related Issues
========================

MySQL uses a cost based optimiser to find out the best way to resolve a
query.  In many cases MySQL can calculate the best possible query plan
but in some cases MySQL doesn't have enough information about the data
at hand and have to do some 'educated' guesses about the data.

This manual section is intended for the cases when MySQL doesn't get it
right.

The tools one has available to help MySQL do the 'right' things are:

   * `EXPLAIN'. *Note `EXPLAIN': EXPLAIN.

   * `ANALYZE TABLE'. *Note `ANALYZE TABLE': ANALYZE TABLE.

   * `USE INDEX', `FORCE INDEX' and `IGNORE INDEX'. *Note SELECT::.

   * Global and table level `STRAIGHT JOIN'. *Note SELECT::.

   * Setting thread specific variables. *Note SHOW VARIABLES::.

How to avoid table scan,,,
--------------------------

`EXPLAIN' will show `ALL' in the `type' column when MySQL uses a table
scan to resolve a query. This happens usually when:

   * The table is so small that it's faster to do a table scan than a
     key lookup.  This is a common case for tables with < 10 rows and a
     short row length.

   * There is no usable restrictions in the `ON' or `WHERE' clause for
     indexed columns.

    *Note
     Where optimisations::.

    in this
     case the table scan would be faster.

What you can do to avoid a 'wrong' table scan for big tables are:

   * Use `ANALYZE TABLE' for the scanned table to update key
     distributions.  *Note ANALYZE TABLE::.

   * Use `FORCE INDEX' for the scanned table to tell MySQL that table
     scans are very expensive compared to use one of the given index.
     *Note SELECT::.

          SELECT * FROM t1,t2 force index(index_for_column) WHERE t1.column=t2.column;

   * Start `mysqld' with `--max-seeks-for-key=1000' or do `SET
     MAX_SEEKS_FOR_KEY=1000'  to tell the optimiser that for no key
     scan will cause more than 1000 key seeks.

[Назад] [Содержание] [Вперед]