(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
automatically generated byinfo2html