Скачать или смотреть How to Optimize Your ORDER BY MySQL Queries for Better Performance

Discover effective strategies to enhance the performance of your MySQL queries with `ORDER BY`, reducing execution time significantly.
---
This video is based on the question https://stackoverflow.com/q/75386772/ asked by the user 'wbzy00' ( https://stackoverflow.com/u/12013462/ ) and on the answer https://stackoverflow.com/a/75393466/ provided by the user 'Rick James' ( https://stackoverflow.com/u/1766831/ ) 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: Simple MySQL query with ORDER BY too slow

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.
---
Is Your MySQL Query With ORDER BY Taking Too Long? Here’s How to Fix It!

If you've ever experienced sluggish performance when running MySQL queries, especially those involving the ORDER BY clause, you're not alone. A common scenario that many developers encounter involves trying to achieve optimal performance for queries that deal with large datasets. One such example might be the challenge of matching phrases in hundreds of thousands of English sentences—a task that can strain even the best-equipped machines. In this post, we'll explore the problem, analyze the current tools at your disposal, and implement strategies to enhance your query's efficiency.

Understanding the Problem

Let's consider a specific case that sheds light on the performance issues at hand. Suppose you have the following SQL query that aims to match whole words and order the results based on the length of the source text:

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

Despite having a reasonably powerful system (an Intel Core i5-10400F, 16GB RAM, and an SSD), the execution time for this query can take over 4.5 seconds—far from ideal! The EXPLAIN command shows that MySQL is scanning over 1 million rows, which greatly increases the duration of the operation.

Issues with Current Indexing

You may have implemented indexing strategies to improve efficiency, such as:

Creating a Prefix Index: INDEX src_cus_lang (source(755), customer, language)

Adding Additional Filters: Other attempts included indexing combinations of customer and language.

However, these efforts often yield minimal gains since REGEXP requires processing a large volume of data.

Solutions to Improve Query Performance

Fortunately, there are several methods you can implement to enhance the performance of your MySQL queries. Let's break down these strategies:

1. Rethink Your Indexing Strategy

The current prefix index approach is suboptimal because it doesn't filter data effectively. Instead, you can create a more refined index:

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

By filtering on customer and language before applying REGEXP, you can significantly reduce the number of rows that need to be processed.

2. Utilize Full-Text Indexing

Consider leveraging a FULLTEXT index on the source column. This powerful feature enables faster searches and can be especially effective when combined with Boolean mode:

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

Using full-text search can drastically reduce the execution time as it allows MySQL to utilize index structures more efficiently.

3. Experiment with Natural Language Processing

You can also experiment with IN NATURAL LANGUAGE MODE for potentially improved performance in text searching. This approach optimizes how MySQL interprets the query, offering better results in the context of natural languages.

4. Adjust the Data Structure

Although it’s not the ideal approach to restructure your existing database, if possible, consider revising the structure for better performance. Breaking the tm table into segments based on customer and language could lead to faster lookups although this might require substantial changes in how your application handles data.

5. Monitor and Tune Performance Regularly

Finally, always monitor the performance of queries through the EXPLAIN analysis and adjust your indices as necessary. Performance optimization is an ongoing process, not a one-time adjustment.

Conclusion

Improving the performance of your MySQL queries involving ORDER BY can feel daunting, especially with large datasets. However, by optimizing your index strategy, leveraging full-text searches, and consistently monitoring your database performance, you can achieve significant improvements. With these strategi