Скачать или смотреть Optimizing MySQL Performance: Efficiently Joining 3 Tables

Discover effective strategies to improve MySQL query performance when joining multiple tables. Learn how to rewrite queries for optimal results with practical examples.
---
This video is based on the question https://stackoverflow.com/q/62601065/ asked by the user 'user1031742' ( https://stackoverflow.com/u/1031742/ ) and on the answer https://stackoverflow.com/a/62601161/ provided by the user 'Tomalak' ( https://stackoverflow.com/u/18771/ ) 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: myqsl join performance - joining 3 tables

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 Performance: Efficiently Joining 3 Tables

When working with databases, performance can often become a bottleneck—especially when it comes to executing queries that involve multiple tables. If you’ve ever faced slow query performance while joining tables in MySQL, you’re not alone. One common scenario involves needing to retrieve records from three different tables based on specific conditions. This is where writing efficient queries becomes essential.

The Problem

Imagine your database has three tables:

content_type_category (c) - You need data from the c.field_pos_value column.

node (n) - This table should only return rows where n.nid matches c.nid.

url_alias (ua) - Here, you want to select rows where ua.src contains c.nid formatted as part of a larger string (e.g., abc/nid).

Your initial query might look something like this:

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

While this might return the results you need, the use of the LIKE clause with the CONCAT function can significantly hinder performance, particularly on large datasets.

Understanding the Performance Issue

Why is the LIKE CONCAT('%/', c.nid) Slow?

When part of a string is searched for in a database, especially at the end (or middle) of a string, it often leads to poor performance due to the following reasons:

Lack of Index Utilization: Most databases, including MySQL, can only use indexes effectively when searching from the beginning of a string.

Full Table Scans: When the search condition cannot utilize the indexing efficiently, MySQL must scan the entire table, resulting in slower query execution.

Proposed Solution

To enhance the performance of your query, consider the following workaround:

Modify the url_alias Table

Add a New Column: Introduce a new column, nid, to the url_alias table.

Extract the NID: This new column should contain the numeric identifier extracted from the src field. For example:

If the src is abc/1, the nid should be 1.

Utilize Triggers or Code: You can use a database trigger to automatically populate this new nid column whenever a new row is added to the url_alias table or handle it within the application logic itself.

Index the New Column: Create an index on the nid column to improve the performance of join operations.

Revised Query

Once you have added the nid column, your query can be rewritten as follows:

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

This revised query leverages direct matching of indexed columns, significantly improving performance.

Conclusion

Improving MySQL query performance does not have to be a daunting task. By recognizing the inefficiencies in your original queries and restructuring your database design with efficient indexing, you can optimize your query execution time dramatically. Remember, avoiding partial string searches when possible—and especially avoiding end-of-string searches—will often yield the best results.

By implementing these strategies, you can ensure that your MySQL database runs efficiently, even with complex queries. Start optimizing your database operations today to enjoy faster performance and improved user experience.