Скачать или смотреть Optimizing MySQL UPDATE Queries: Speeding Up Operations with Multiple AND Conditions

Discover effective techniques to optimize slow `UPDATE` queries in MySQL using multiple `AND` conditions, and how indexing can drastically improve performance.
---
This video is based on the question https://stackoverflow.com/q/64348604/ asked by the user 'asdj1234' ( https://stackoverflow.com/u/11332416/ ) and on the answer https://stackoverflow.com/a/64458948/ provided by the user 'asdj1234' ( https://stackoverflow.com/u/11332416/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: UPDATE query using multiple AND conditions slow in MYSQL

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Optimizing MySQL UPDATE Queries: Speeding Up Operations with Multiple AND Conditions

When working with large datasets in MySQL, performance bottlenecks can significantly impede the efficiency of your database operations. One common issue many developers face is slow UPDATE queries, especially when utilizing multiple AND conditions. This post will address a specific scenario where an update on a table containing approximately 2 million rows is taking an unacceptably long time to execute.

The Challenge

Suppose you find yourself in a situation where you are trying to update a large table, such as global_mobility_report, based on conditions linked to another smaller table, geography. However, as you attempt to run an UPDATE query, it seems to drag on, with the operation taking 30 minutes or more without any output.

Here's a simplified version of the query you're attempting to run:

[[See Video to Reveal this Text or Code Snippet]]

Why is it Slow?

There could be several reasons for the slowness of your query:

Lack of Indexing: When tables contain millions of rows, performing lookups without indexes on the columns involved can lead to full table scans, which are time-consuming.

Complexity: The use of nested queries can further complicate your operations, especially when multiple AND conditions are used, potentially leading to higher computational overhead.

The Solution

To remedy the performance issue with your UPDATE query, indexing is a game changer. Here’s how you can streamline your operations:

Step 1: Index the Relevant Columns

By using indexes on the columns involved in your WHERE clause (country_region, sub_region_1, sub_region_2, metro_area, iso_3166_2_code, and census_fips_code), the database can perform lookups much faster.

You can create indexes as follows:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Run Your Update Query Again

After creating the necessary indexes, re-execute your UPDATE query. The database should now be able to locate the records in the geography table much more efficiently, drastically reducing the time taken to run the operation.

Potential Results

Most users report a significant increase in speed—often from lengthy 30-minute waits down to just a few seconds! By indexing, you allow MySQL to find the rows it needs without scanning through the entire dataset.

Conclusion

In database management, performance optimization is crucial, especially when dealing with large volumes of data. By understanding the impact of indexing your columns, you can transform your slow UPDATE queries into fast, efficient updates.

If you find yourself in similar situations, remember to consider indexing as your first line of defense against slow queries.

Ready to speed up your MySQL queries? Start by indexing your tables today!