Home > Database > Mysql Tutorial > How Can We Optimize MySQL Joins and Reduce Disk I/O to Speed Up Slow Queries?

How Can We Optimize MySQL Joins and Reduce Disk I/O to Speed Up Slow Queries?

Mary-Kate Olsen
Release: 2024-12-28 18:37:11
Original
891 people have browsed it

How Can We Optimize MySQL Joins and Reduce Disk I/O to Speed Up Slow Queries?

Mitigating MySQL Slow Queries by Optimizing Joins and Reducing Disk Write

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;
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template