How Can I Optimize a Slow MySQL Query to Improve Performance and Reduce Disk I/O?

Optimizing MySQL Query to Reduce Execution Time and Disk Writes

Your MySQL query is encountering performance issues, taking several minutes to complete and writing to disk during execution. This can significantly impact the responsiveness of your web page. To address this, we will explore a solution involving data modeling and index optimization.

The slow query you mentioned is:

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

According to the EXPLAIN output, the query is performing a full table scan on the poster_prodcat table, which contains over 17 million rows. This is contributing to the excessive execution time.

To optimize the query performance, we can implement the following changes:

Normalization and Indexing:

1. Normalize the Data: Convert the poster_prodcat table into a normalized structure with two tables: poster and category. Create a third table, poster_category, to establish the relationship between posters and categories.
2. Create Indexes: Create indexes on the appropriate columns in the poster, category, and poster_category tables. This will enable the database to quickly locate data without having to perform full table scans.

Here's an example of the updated schema:

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

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

CREATE TABLE poster_category (
  cat_id MEDIUMINT UNSIGNED NOT NULL,
  poster_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (cat_id, poster_id),
  INDEX (poster_id) -- Clustered composite index
) ENGINE=InnoDB;

Once the data is normalized and indexed, the query should perform much faster, reducing the execution time significantly.

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