How Can We Optimize a Slow MySQL SELECT Query to Improve Web Page Performance?

Slow MySQL Select Query Optimized to Enhance Performance

This MySQL query has faced delays due to its complexity and data volume. To mitigate this, it's essential to understand the relationships between the tables involved:

• poster_data: Stores data on individual posters.
• poster_categories: Lists all available categories (e.g., movies, art).
• poster_prodcat: Links posters to their respective categories, with multiple entries possible for a single poster.

The original query, as follows, exhibited a lengthy execution time, particularly when used in web page creation:

SELECT * 
FROM poster_prodcat, 
     poster_data, 
     poster_categories 
WHERE poster_data.apnumber = poster_prodcat.apnumber 
AND poster_categories.apcatnum = poster_prodcat.apcatnum 
AND poster_prodcat.apcatnum='623'  
ORDER BY aptitle ASC 
LIMIT 0, 32

Analysis of the Execution Plan:

The query's performance bottleneck was identified in the 'explain' output:

[Explain Image Link]

As observed, the query had to write data to disk, significantly impacting speed.

Solution:

To address this issue, a revised schema was designed using composite indexing. This new structure demonstrated significant improvements in performance:

DROP TABLE IF EXISTS poster;
CREATE TABLE poster
(
poster_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL UNIQUE
)
ENGINE = INNODB; 

DROP TABLE IF EXISTS category;
CREATE TABLE category
(
cat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL UNIQUE
)
ENGINE = INNODB; 

DROP TABLE IF EXISTS poster_category;
CREATE TABLE poster_category
(
cat_id MEDIUMINT UNSIGNED NOT NULL,
poster_id INT UNSIGNED NOT NULL,
PRIMARY KEY (cat_id, poster_id) -- Note the clustered composite index !!
)
ENGINE = INNODB;

With the composite index in place, the following revised query produced lightning-fast results:

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;

This optimized approach effectively resolved the performance issues, making the application web page responsive and user-friendly.

The above is the detailed content of How Can We Optimize a Slow MySQL SELECT Query to Improve Web Page Performance?. For more information, please follow other related articles on the PHP Chinese website!