title: Retail inventory system Mysql query optimization
body: <p>I'm looking to optimize the following Mysql query that is used to calculate the cost of inventory for a number of stores, I'm running the following query through a PHP loop for each distinct store and outputting the result:</p>
  
  <p>Query:</p>
  
  <pre><code>SELECT
  
      SUM((((
  
      (SELECT COALESCE(SUM(facturas_fabrica.cantidad), 0)
  
      FROM facturas_fabrica
  
      INNER JOIN entradas_pedidos_productos ON 
      entradas_pedidos_productos.clave = facturas_fabrica.entradas_pedidos_productos_clave
  
      INNER JOIN entradas_pedidos ON entradas_pedidos.clave = entradas_pedidos_productos.entradas_pedidos_clave
  
      WHERE entradas_pedidos_productos.producto_id = productos.id 
      AND facturas_fabrica.procesado_local = 1 AND entradas_pedidos.sucursal_id = '.$row['id'].' # store id   
      AND DATE(facturas_fabrica.fecha_procesada) &lt;= DATE(NOW()))
  
  
      -
  
      (SELECT COALESCE(SUM(cantidad), 0) 
  
      FROM facturas_contenido
  
      WHERE producto_id = productos.id AND facturas_contenido.sucursal_id = '.$row['id'].'
      AND DATE(facturas_contenido.fecha_creacion) &lt;= DATE(NOW()))
  
      +
  
      (SELECT COALESCE(SUM(cantidad), 0) 
  
      FROM notas_de_credito_contenido
  
      WHERE producto_id = productos.id AND notas_de_credito_contenido.sucursal_id = '.$row['id'].'
      AND DATE(notas_de_credito_contenido.fecha_creacion) &lt;= DATE(NOW()))
  
      -
  
      (SELECT COALESCE(SUM(salidas_devoluciones.cantidad), 0) 
  
      FROM salidas_devoluciones
  
      WHERE producto_id = productos.id AND (estado = 2 OR estado = 3) AND modulo != 2 AND salidas_devoluciones.sucursal_id = '.$row['id'].' # store id
      AND DATE(salidas_devoluciones.fecha_envio) &lt;= DATE(NOW()))   
  
      ) * productos.costo) / 100) ) AS "'.$row['clave'].'" # store name 
  
  FROM productos WHERE 1
  </code></pre>
  
  <p>(I'm only keeping fields relevant to the query)</p>
  
  <p>Table 1:</p>
  
  <pre><code>CREATE TABLE `productos` (
    `id` int(10) unsigned NOT NULL,
    `costo` int(10) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `costo` (`costo`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>Table 2:</p>
  
  <pre><code>CREATE TABLE `facturas_fabrica` (
    `id` int(10) NOT NULL AUTO_INCREMENT,
    `clave` bigint(20) unsigned NOT NULL,
    `entradas_pedidos_productos_clave` bigint(20) unsigned NOT NULL,
    `cantidad` tinyint(3) unsigned NOT NULL,
    `procesado_local` tinyint(1) NOT NULL DEFAULT '0',
    `fecha_procesada` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `clave_UNIQUE` (`clave`),
    KEY `fk_entradas_pedidos_productos_clave_idx` (`entradas_pedidos_productos_clave`),
    KEY `facturas_fabrica_procesado_local` (`procesado_local`),
    KEY `facturas_fabrica_cantidad` (`cantidad`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
  
  --
  -- Constraints for table `facturas_fabrica`
  --
  ALTER TABLE `facturas_fabrica`
    ADD CONSTRAINT `fk_entradas_pedidos_productos_clave` FOREIGN KEY (`entradas_pedidos_productos_clave`) REFERENCES `entradas_pedidos_productos` (`clave`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  </code></pre>
  
  <p>Table 3:</p>
  
  <pre><code>CREATE TABLE `entradas_pedidos_productos` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `clave` bigint(20) unsigned NOT NULL,
    `entradas_pedidos_clave` bigint(20) unsigned NOT NULL,
    `producto_id` int(10) unsigned NOT NULL,
    `cantidad` tinyint(3) unsigned NOT NULL,
  
    PRIMARY KEY (`id`),
    UNIQUE KEY `clave_UNIQUE` (`clave`),
    KEY `fk_pedidos_producto_id_idx` (`producto_id`),
    KEY `fk_pedidos_productos_pedido_clave_idx` (`entradas_pedidos_clave`),
    KEY `entradas_productos_cantidad` (`cantidad`),
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
  
  --
  -- Constraints for table `entradas_pedidos_productos`
  --
  ALTER TABLE `entradas_pedidos_productos`
    ADD CONSTRAINT `fk_pedidos_productos_pedido_clave` FOREIGN KEY (`entradas_pedidos_clave`) REFERENCES `entradas_pedidos` (`clave`) ON DELETE NO ACTION ON UPDATE NO ACTION,
    ADD CONSTRAINT `fk_pedidos_productos_producto_id` FOREIGN KEY (`producto_id`) REFERENCES `productos` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  </code></pre>
  
  <p>Table 4:</p>
  
  <pre><code>CREATE TABLE `entradas_pedidos` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `clave` bigint(20) unsigned NOT NULL,
    `sucursal_id` tinyint(3) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `clave_UNIQUE` (`clave`),
    KEY `clave` (`clave`),
    KEY `entradas_sucursal` (`sucursal_id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
  
  --
  -- Constraints for table `entradas_pedidos`
  --
  ALTER TABLE `entradas_pedidos`
    ADD CONSTRAINT `fk_entradas_pedidos_sucursal_is` FOREIGN KEY (`sucursal_id`) REFERENCES `sucursales` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  </code></pre>
  
  <p>Table 5:</p>
  
  <pre><code>CREATE TABLE `facturas_contenido` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `clave` bigint(20) unsigned NOT NULL,
    `sucursal_id` tinyint(3) NOT NULL,
    `producto_id` int(10) unsigned NOT NULL,
    `cantidad` tinyint(3) unsigned NOT NULL,
    `fecha_creacion` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `clave_factura_contenido` (`clave`),
    KEY `fk_orden_contenido_producto_id_idx` (`producto_id`),
    KEY `facturas_contenido_cantidad` (`cantidad`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
  
  --
  -- Constraints for table `facturas_contenido`
  --
  ALTER TABLE `facturas_contenido`
    ADD CONSTRAINT `fk_facturas_clave` FOREIGN KEY (`factura_clave`) REFERENCES `facturas` (`clave`) ON DELETE NO ACTION ON UPDATE NO ACTION,
    ADD CONSTRAINT `fk_facturas_contenido_producto_id` FOREIGN KEY (`producto_id`) REFERENCES `productos` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  </code></pre>
  
  <p>Table 6:</p>
  
  <pre><code>CREATE TABLE `notas_de_credito_contenido` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `clave` bigint(20) unsigned NOT NULL,
    `sucursal_id` tinyint(3) NOT NULL,
    `producto_id` int(10) unsigned NOT NULL,
    `cantidad` tinyint(3) unsigned NOT NULL,
    `fecha_creacion` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `clave_nota_de_credito_contenido` (`clave`),
    KEY `fk_nc_clave_idx` (`nc_clave`),
    KEY `fk_ordenes_contenido_clave_idx` (`nc_facturas_contenido_clave`),
    KEY `notas_de_credito_cantidad` (`cantidad`),
    KEY `notas_de_credito_producto_id` (`producto_id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
  
  --
  -- Constraints for dumped tables
  --
  
  --
  -- Constraints for table `notas_de_credito_contenido`
  --
  ALTER TABLE `notas_de_credito_contenido`
    ADD CONSTRAINT `fk_facturas_contenido_clave` FOREIGN KEY (`nc_facturas_contenido_clave`) REFERENCES `facturas_contenido` (`clave`) ON DELETE NO ACTION ON UPDATE NO ACTION,
    ADD CONSTRAINT `fk_nc_clave` FOREIGN KEY (`nc_clave`) REFERENCES `notas_de_credito` (`clave`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  </code></pre>
  
  <p>Table 7:</p>
  
  <pre><code>CREATE TABLE `salidas_devoluciones` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `clave` bigint(20) unsigned NOT NULL,
    `producto_id` int(10) unsigned NOT NULL,
    `cantidad` tinyint(3) unsigned NOT NULL,
    `sucursal_id` tinyint(3) NOT NULL,
    `fecha_envio` timestamp NULL DEFAULT NULL,
    `estado` tinyint(3) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    UNIQUE KEY `clave_UNIQUE` (`clave`),
    KEY `salidas_devoluciones_cantidad` (`cantidad`),
    KEY `fk_salidas_producto_id_idx` (`producto_id`),
    KEY `devoluciones_estado` (`estado`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
  
  --
  -- Constraints for table `salidas_devoluciones`
  --
  ALTER TABLE `salidas_devoluciones`
    ADD CONSTRAINT `fk_salidas_producto_id` FOREIGN KEY (`producto_id`) REFERENCES `productos` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  </code></pre>
  
  <p>The problem is because of the large amount of stores and movements in each store the query is becoming increasingly heavy, and because inventory is calculated from the beginning of each store's history it only gets slower with time.</p>
  
  <p>What could I do to optimize this scheme?</p>