0

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;
  • Is this a groupwise-max case? – Rick James Oct 28 '19 at 2:54
  • @RickJames not sure if I get your question, I am trying to get MIN values on price precisely and the rest of other fields are aggregated (Just made an edit) using MIN just to avoid group by on non-aggregated columns. – ro ko Oct 28 '19 at 3:53
  • How many rows in the resultset if you remove the GROUP BY? – Rick James Oct 28 '19 at 14:05
  • roughly 150,000 – ro ko Oct 29 '19 at 11:36
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.