MySQL SQL optimization: Limitations of correlated subqueries

Source: Internet
Author: User

This is the third article on MySQL SQL optimization.

A company's business system frequently throws a problematic SQL statement. We have made fundamental statistics on such SQL statements:

These SQL statements have been executed for 12 times recently. The maximum time is 480 seconds and the minimum time is 286 seconds.

Table t1 has more than 0.9 million rows, which always scans so much unnecessary data.

This is because the MySQL query optimizer has limitations in processing related subqueries.

MySQL always presses the related outer table into the subquery, which is considered to be more efficient in searching data rows.
This means that MySQL first selects the external layer table for full table scanning, and then executes subqueries one by one based on the relevant fields,

If it is a small table, the situation may not attract our attention, but if the outer layer represents a very large table, the query performance will be very poor,

Unfortunately, our scenario just fits the latter.


Our optimized execution results:




Good Luck!

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.