Скачать или смотреть How to Optimize Slow MySQL Queries with Efficient Indexing

Discover how to speed up slow MySQL queries by using compound indexing on your tables. Learn the steps to enhance database performance effectively.
---
This video is based on the question https://stackoverflow.com/q/67472523/ asked by the user 'John' ( https://stackoverflow.com/u/1469670/ ) and on the answer https://stackoverflow.com/a/67472988/ provided by the user 'Bill Karwin' ( https://stackoverflow.com/u/20860/ ) 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: Even with indices this query is relatively slow. How to speed it up?

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.
---
How to Optimize Slow MySQL Queries with Efficient Indexing

In the realm of database management, slow queries can be a significant bottleneck, hindering not only performance but also user experience. One common scenario that many developers encounter is the challenge of optimizing complex queries that involve sorting and filtering simultaneously. In this guide, we’ll examine a specific case involving a videos table in MySQL and explore a solution that leverages the power of indexing to improve query performance substantially.

Understanding the Problem

Imagine a table named videos containing various columns, including ID, Title, Date, Quality, and Length. Users often want to sort and filter videos based on multiple criteria, which can lead to slow query execution times when not properly optimized.

The Query Challenges

Consider the following scenarios with sample queries:

Filtering by Quality and Length:

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

Execution Time: 0.003 seconds

Sorting by Date:

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

Execution Time: 0.003 seconds

Combining Filter and Sort:

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

Execution Time: 1.3 seconds

The last query showcases a significant bottleneck. Although it uses indices for filtering, the sorting based on Date imposes additional workload, especially if the result set is large.

Solution: Implementing an Optimal Index

To tackle the slow querying issue when filtering and sorting together, a more comprehensive indexing strategy is necessary. Here’s how to redefine the index on the videos table.

Step 1: Create a Compound Index

You will need to create a compound index that includes all the relevant columns used in your query. In this case, we want to include Quality, Length, and Date in the index.

SQL Command:

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

Key Points:

The order of the first two columns (Quality and Length) in the index can be swapped; however, they must appear before the Date column.

By including Date in the index, MySQL can read the rows directly in the desired order, which eliminates the need for additional sorting operations during query execution.

Step 2: Understanding MySQL Sorting Optimization

MySQL optimizes querying based on the sorting order of the index. Here’s how it works:

InnoDB Table Optimization: MySQL's InnoDB storage engine reads rows in the order specified by the index. If the index is structured as (quality, length, date), MySQL can fetch rows in that exact order without needing extra sorting.

Primary Key Consideration: If your index consists only of (quality, length), MySQL may resort to the primary key order for the third column, potentially disrupting the desired sort order.

Step 3: Testing the Query Performance

After implementing the compound index, test the previously slow query again:

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

With the new index, you should see a significant reduction in execution time, likely returning to a sub-second response.

Conclusion

Optimizing slow queries in MySQL often requires strategic indexing, especially when it comes to sorting and filtering. By implementing a compound index that includes columns involved in both operations, you can greatly enhance performance and user experience. So next time you encounter slow query performance, consider revisiting your indexing strategy, and check to see if a compound index could solve your problems as it did for our videos table.

Feel free to share your experiences or ask questions about query optimization in the comments below!