Troubleshoot problems with the optimizer in the MySQL database

Source: Internet
Author: User
Tags thread class 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.