(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