How Can Database Restructuring Optimize a Slow MySQL Query and Eliminate Disk I/O?

Optimizing MySQL Query to Reduce Execution Time and Eliminate Disk I/O

In a typical web application scenario, the performance of a database query is crucial to maintain a responsive user experience. This article addresses a specific MySQL query that exhibited slow execution times, resulting in poor website performance.

The query in question joins three tables: "poster_data," "poster_categories," and "poster_prodcat." It aims to retrieve data from the tables based on a specific condition on the "poster_prodcat" table. The query execution plan revealed the involvement of the "poster_prodcat" table and its writing to disk, indicating inefficient data retrieval.

To address the performance issue, we explored an alternative approach that involves restructuring the database and queries. Specifically, we created new tables named "poster," "category," and "poster_category." The "poster_category" table was designed with a clustered composite index on the (cat_id, poster_id) columns.

With this new schema, we rewrote the original query as follows:

SELECT
    p.*,
    c.*
FROM
    poster_category pc
INNER JOIN
    category c ON pc.cat_id = c.cat_id
INNER JOIN
    poster p ON pc.poster_id = p.poster_id
WHERE
    pc.cat_id = 623
ORDER BY
    p.name
LIMIT
    32;

The EXPLAIN plan for the rewritten query reveals a significant improvement in performance. The query now uses indexes effectively, reducing the need for disk I/O and minimizing the execution time.

The above is the detailed content of How Can Database Restructuring Optimize a Slow MySQL Query and Eliminate Disk I/O?. For more information, please follow other related articles on the PHP Chinese website!