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
, andshipSlug
. Since there are further factors for example,csv.extra_cols
andcsv.offerDuration
which affects the price, I just wanted to getMIN(Price)
based on columns used inGROUP 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;