Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Add the CREATE TABLE statements and the EXPLAIN plan of the (slow) query. –  ypercube May 29 '13 at 10:06
    
@ypercube: done –  guest May 29 '13 at 10:15
    
what i am doing now is creating subqueries in PHP - queries are instant, whole thing is really ridiculous. mysqlwtf? –  guest May 29 '13 at 10:36
    
Can you correct the EXPLAIN output? It's not clear which index is finally used. –  ypercube May 29 '13 at 10:58
    
@ypercube: done. –  guest May 29 '13 at 11:22

1 Answer 1

up vote 1 down vote accepted

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) ;
share|improve this answer
    
OK, but why single query executes in <0.01s? –  guest May 29 '13 at 11:04
    
It either uses different index (and by luck this is a bit more efficient than the dateindex) or you get a cached result from previous execution of the query. Is the single query always fast (with different parameters)? –  ypercube May 29 '13 at 11:06
    
Sometimes is slower (when there is more rows in inv_prices), but never like 100 seconds... –  guest May 29 '13 at 11:10
    
Anyways thanks for answer, I am going to add the index and I will inform about results –  guest May 29 '13 at 11:18
    
with inner join and unique index everything works great –  guest May 29 '13 at 12:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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