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:
Query:
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) <= 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) <= 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) <= 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) <= DATE(NOW()))
) * productos.costo) / 100) ) AS "'.$row['clave'].'" # store name
FROM productos WHERE 1
(I'm only keeping fields relevant to the query)
Table 1:
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;
Table 2:
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;
Table 3:
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;
Table 4:
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;
Table 5:
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;
Table 6:
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;
Table 7:
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;
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.
What could I do to optimize this scheme?
