Troubleshoot problems with the optimizer in the MySQL database

MySQL employs a cost-based optimizer to determine the most solution to the processing of queries. In many cases, MySQL is able to compute the best possible query plan, but in some cases MySQL does not have enough information about the data to have a "educated" estimate of the data.

When MySQL fails to do the "right" thing, you can use the following tools to help MySQL:

Use the explain statement to get information about how MySQL handles queries. To use it, you can add the keyword explain before the SELECT statement:

Mysql> EXPLAIN SELECT * from t1, t2 WHERE t1.i = t2.i;

Update key assignments for the scanned table using the Analyze table Tbl_name.

Use force INDEX for the scanned table to notify MySQL: Table scanning is expensive compared to using a given index.

SELECT * from t1, T2 FORCE INDEX (index_for_column)

WHERE T1.col_name=t2.col_name;

Use index and ignore index also have some help.

You can adjust the global or thread class system variables. For example, start mysqld with the "--max-seeks-for-key=1000" option or use SET max_seeks_for_key=1000 to notify the optimizer that no table scan will cause more than 1000 key searches.