Solve the problem related to the Optimizer in the MySQL database _ MySQL

Solve the problem related to the Optimizer in the MySQL database. MySQL uses an overhead-based optimizer to determine the optimal solution for processing queries. In many cases, MySQL can calculate the best possible query plan, but in some cases, MySQL does not have enough information about the data and has to make a "educated" estimation of the data.

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

Use the EXPLAIN statement to obtain information about how MySQL processes 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;

Use analyze table tbl_name to allocate update keys for the scanned tables.

Use force index for scanned tables to notify MySQL that 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 are also helpful.

You can adjust global or thread-like system variables. For example, use the "-- max-seeks-for-key = 1000" option to start mysqld, or use "SET max_seeks_for_key = 1000" to notify the Optimizer: assuming that no table scan results in more than 1000 key searches.