In a situation where a MySQL query utilizing multiple joins and table lookups was excessively consuming time, particularly affecting web page load speeds, an investigation and resolution was undertaken.
The query in question involved three tables: poster_data, poster_categories, and poster_prodcat. The query sought to display specific information from these tables based on a condition that a particular apcatnum value was equal to '623'.
An analysis of the database schema and query plan (provided as an EXPLAIN statement) revealed that the poster_prodcat table, which contained over 17 million rows, was a major contributor to the slowdown. Specifically, the query was writing out to disk when accessing a subset of approximately 400,000 rows from this table due to its unoptimized join conditions.
To address this issue, a restructuring of the database structure and indexes was proposed. The original complex join structure was replaced with a simplified and more efficient join strategy. Additionally, clustered composite indexes were introduced on the relevant columns to improve query performance.
The following code snippet showcases the optimized database structure and query:
-- Create the tables DROP TABLE IF EXISTS poster; CREATE TABLE poster ( poster_id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL UNIQUE ) ENGINE = INNODB; DROP TABLE IF EXISTS category; CREATE TABLE category ( cat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, 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) -- Clustered composite index ) ENGINE = INNODB; -- Populate the tables with data -- Fetch data from the existing database to populate the new structure 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 integration of these optimizations significantly reduced the query execution time. The restructuring and indexing techniques combined to minimize disk write operations and enhance query efficiency, resulting in a substantial improvement in the web page's loading speed.
The above is the detailed content of How Can We Optimize MySQL Joins and Reduce Disk I/O to Speed Up Slow Queries?. For more information, please follow other related articles on the PHP Chinese website!