Mysql - query optimization

Question

I have a DB schema as such,

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema stack_vista
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema stack_vista
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `stack_vista` DEFAULT CHARACTER SET utf8 ;
USE `stack_vista` ;

-- -----------------------------------------------------
-- Table `stack_vista`.`csv`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `stack_vista`.`csv` (
  `idcsv` INT NOT NULL AUTO_INCREMENT,
  `serviceCode` VARCHAR(45) NULL,
  `offerDate` DATETIME NULL,
  `price` VARCHAR(45) NULL,
  `offerDuration` INT NULL,
  `occupancy` INT NULL,
  `extra_cols` VARCHAR(45) NULL,
  PRIMARY KEY (`idcsv`),
  INDEX `index_service_code` (`serviceCode` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `stack_vista`.`offers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `stack_vista`.`offers` (
  `idoffers` INT NOT NULL AUTO_INCREMENT,
  `shipSlug` VARCHAR(45) NULL,
  `offerLink` VARCHAR(45) NULL,
  `name` VARCHAR(45) NULL,
  `serviceCode` VARCHAR(45) NULL,
  `extra_cols` VARCHAR(45) NULL,
  PRIMARY KEY (`idoffers`),
  INDEX `index_offer_code` (`serviceCode` ASC),
  FULLTEXT INDEX `index_ship_slug` (`shipSlug` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `stack_vista`.`ships`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `stack_vista`.`ships` (
  `idships` INT NOT NULL AUTO_INCREMENT,
  `slug` VARCHAR(45) NULL,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`idships`),
  FULLTEXT INDEX `index_slug` (`slug` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `stack_vista`.`props`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `stack_vista`.`props` (
  `idprops` INT NOT NULL AUTO_INCREMENT,
  `idoffers` INT NOT NULL,
  `propName` VARCHAR(45) NULL,
  `propCode` VARCHAR(45) NULL,
  `type` VARCHAR(45) NULL,
  PRIMARY KEY (`idprops`),
  INDEX `fk_props_offers_idx` (`idoffers` ASC),
  INDEX `index_type` (`type` ASC),
  CONSTRAINT `fk_props_offers`
    FOREIGN KEY (`idoffers`)
    REFERENCES `stack_vista`.`offers` (`idoffers`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `stack_vista`.`location`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `stack_vista`.`location` (
  `idlocation` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `code` VARCHAR(45) NULL,
  `type` VARCHAR(45) NULL,
  PRIMARY KEY (`idlocation`),
  INDEX `index_type` (`type` ASC),
  INDEX `index_name` (`name` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `stack_vista`.`offers_has_location`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `stack_vista`.`offers_has_location` (
  `offers_idoffers` INT NOT NULL,
  `location_idlocation` INT NOT NULL,
  PRIMARY KEY (`offers_idoffers`, `location_idlocation`),
  INDEX `fk_offers_has_location_location1_idx` (`location_idlocation` ASC),
  INDEX `fk_offers_has_location_offers1_idx` (`offers_idoffers` ASC),
  CONSTRAINT `fk_offers_has_location_offers1`
    FOREIGN KEY (`offers_idoffers`)
    REFERENCES `stack_vista`.`offers` (`idoffers`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_offers_has_location_location1`
    FOREIGN KEY (`location_idlocation`)
    REFERENCES `stack_vista`.`location` (`idlocation`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

My table csv is a table where I add data imported from csv, and has offers for different services/travels, for different dates, location and types. It can have 10000-100000 records, with around 900-1000 services and it's combination as seen in group by.

Different services maps to servicCode from table offers, which is unique in offers table.

Some of the offers have shipSlugs, which maps to slugs in ships table.

I am trying to list all possible serviceCodes, offerDate, propTypeId, and shipSlug. Since there are further factors for example, csv.extra_cols and csv.offerDuration which affects the price, I just wanted to get MIN(Price) based on columns used in GROUP BY hence the aggregation.

I have added index in every columns which is involved either in where clause or join on conditions.

My query below currently takes between 6-9 seconds to execute. What can be further done to optimize this query:

SELECT 
    i.serviceCode AS serviceCode,
    MIN(i.offerDate) AS offerDate,
    MIN(i.price) AS price,
    MIN(i.offerDuration) AS duration,
    (i.occupancy) AS occupancy,
    p.idprops AS propTypeId,
    MIN(p.propName) AS propTypeName,
    MIN(p.propCode) AS propTypeCode,
    MIN(p.type) AS type,
    g.idlocation AS destinationId,
    MIN(g.name) AS destinationName,
    MIN(g.code) AS destinationCode,
    MIN(g.type) AS destinationType,
    o.idoffers AS offerId,
    MIN(o.offerlink) AS offerlink,
    MIN(o.name) AS name,
    o.shipSlug AS shipSlug,
    MIN(s.name) AS shipName,
    CONCAT_WS('-',
            YEAR(i.offerDate),
            MONTH(i.offerDate)) AS offer_year_month_date
FROM
    csv i
        JOIN
    offers o ON o.serviceCode = i.serviceCode
        LEFT JOIN
    ships s ON o.shipSlug = s.slug
        JOIN
    props p ON o.idoffers = p.idoffers AND p.type = 'travelType'
        JOIN
    offers_has_location og ON o.idoffers = og.offers_idoffers
        JOIN
    location g ON g.idlocation = og.location_idlocation 
                   AND g.code IN ('Earth' , 'River', 'HighLands')    
GROUP BY serviceCode, offer_year_month_date, propTypeId, destinationId, shipSlug, occupancy;

Answer 1

It could take 6-9 seconds to generate the 150K rows in the intermediate table. And that table would be bulky because of holding about 15 columns, awaiting MIN(), etc.

A common fix is to turn the query inside out. But I am not sure how to do it in your case.

SELECT  serviceCode, occupancy, offer_year_month_date,
        ( SELECT ... ) AS ..,
        ( SELECT ... ) AS ..,
        ( SELECT ... ) AS ..
    FROM ( SELECT       serviceCode, offer_year_month_date
               FROM ...
               GROUP BY serviceCode, offer_year_month_date
         ) AS x;

The hope is that you can push the GROUP BY into that 'derived' table and it will have much less than 150K rows. Then the other columns are pulled by the subqueries in the SELECT part.