MySQL Subquery hangs / terrible perfomance

MySQLmysql-5.5

I am using MySQL 5.5.16 @ CentOS.

Please have a look at this query:

SELECT                      
    inventory_ID,
    (
    SELECT 
        price_brutto 
    FROM 
        _inventory_prices 
    WHERE 
        _inventory_prices.inventory_ID = _inventory.inventory_ID AND 
        price_type_ID = 4 
    ORDER BY 
        price_date DESC 
    LIMIT 1
    ) as price_brutto
FROM
    _inventory         
ORDER BY
    inventory_ID ASC
LIMIT 1

It takes forever to execute this query! But separate queries:

SELECT                      
    inventory_ID
FROM
    _inventory         
ORDER BY
    inventory_ID ASC
LIMIT 1

AND

    SELECT 
        price_brutto 
    FROM 
        _inventory_prices 
    WHERE 
        _inventory_prices.inventory_ID = 12345 /* this is first inventory_ID from _inventory table */ AND 
        price_type_ID = 4

Runs in < 0.01s ! (First query returns 1 row, second 0 rows)

  • _inventory table is filled with ~60k rows
  • _inventory_prices table is filled with ~40m rows

Why main query (with subquery) is so terribly slow?

Structure:

CREATE  TABLE IF NOT EXISTS `ca_ERP`.`_inventory` (
  `inventory_ID` INT(11) NOT NULL AUTO_INCREMENT ,
  `SKU` VARCHAR(200) NULL DEFAULT NULL ,
  `item_ID` INT(11) NOT NULL ,
  ... data columns ...,
  PRIMARY KEY (`inventory_ID`) ,
  UNIQUE INDEX `SKU_UNIQUE` (`SKU` ASC) ,
  INDEX `fk__inventory__items1_idx` (`item_ID` ASC) ,
  CONSTRAINT `fk__inventory__items1`
    FOREIGN KEY (`item_ID` )
    REFERENCES `ca_ERP`.`_items` (`item_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 64930
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

CREATE  TABLE IF NOT EXISTS `ca_ERP`.`_inventory_prices` (
  `inventory_prices_ID` INT(11) NOT NULL AUTO_INCREMENT ,
  `inventory_ID` INT(11) NOT NULL ,
  `price_type_ID` INT(11) NOT NULL ,
  `price_brutto` DECIMAL(9,2) NULL DEFAULT '0.00' ,
  `currency_ID` INT(11) NULL DEFAULT '1' ,
  `is_custom` TINYINT(4) NULL DEFAULT '0' ,
  `price_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`inventory_prices_ID`) ,
  INDEX `fk__inventory_prices__inventory1_idx` (`inventory_ID` ASC) ,
  INDEX `fk__inventory_prices__currencies1_idx` (`currency_ID` ASC) ,
  INDEX `fk__inventory_prices__marketplaces1_idx` (`price_type_ID` ASC) ,
  INDEX `dateindex` (`price_date` ASC) ,
  INDEX `priceindex` (`price_brutto` ASC) ,
  CONSTRAINT `fk__inventory_prices__currencies1`
    FOREIGN KEY (`currency_ID` )
    REFERENCES `ca_ERP`.`_currencies` (`currency_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk__inventory_prices__inventory1`
    FOREIGN KEY (`inventory_ID` )
    REFERENCES `ca_ERP`.`_inventory` (`inventory_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk__inventory_prices__marketplaces1`
    FOREIGN KEY (`price_type_ID` )
    REFERENCES `ca_ERP`.`_price_type` (`price_type_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 3408033
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

Explain result:

1   PRIMARY _inventory  index       PRIMARY 4       1   6220900.00  Using index
2   DEPENDENT SUBQUERY  _inventory_prices   ref fk__inventory_prices__inventory1,fk__inventory_prices__marketplaces1    fk__inventory_prices__inventory1    4   ca_ERP._inventory.inventory_ID  177 100.00  Using where; Using filesort

Best Answer

The query is slow because the dateindex is used and that is not the best index for this query (or no index at all, please fix the EXPLAIN output). When the subquery is executed, the inventory_ID is already known and the price_type_ID is fixed but there is no composite index that can make this information useful.

Possible explanations for the single query running faster is that you either get cached results from previous execution of the query or it uses different index (and by luck this is a bit more efficient than the dateindex). You have several indexes, on (price_type_ID), on (inventory_ID) but no composite one. So, depending on statistics, the actual parameters and the index chosen each time, the performance will vary.

Try adding an index that will always do an index seek, no index or table scan (and get the price from the index, too, so no additional read form the table will be needed) and thus make both the query and the single query much faster:

ALTER TABLE _inventory_prices 
  ADD INDEX price_type_ID__inventory_ID__date__brutto__IX        -- choose a name
    (price_type_ID, inventory_ID, price_date, price_brutto) ;
Related Question