MySQL Forums
Forum List  »  Spanish

Re: Tablas Grandes
Posted by: jorge alberto alonso cruz
Date: July 05, 2007 10:45AM

esta query se me demora mas de 30 minutos que puedo hacer para obtimizarla

SELECT SQL_BIG_RESULT
producto.codigoConFlag,
tipoproducto.DESPROD,
sum(ventasU) as ventasU,
sum(ventasC) as ventasC,
sum(ventasV) as ventasV,
sum(ventasU08) as ventasU08,
sum(ventasC08) as ventasC08,
sum(ventasV08) as ventasV08,
sum(comprasU) as comprasU,
sum(comprasC) as comprasC,
sum(comprasUT) as comprasUT,
sum(comprasCT) as comprasCT,
sum(existe) as SA_AC,
count(DISTINCT estadistic2002.Fecha) as DiasEnVenta
FROM
estadistic2002
INNER JOIN producto
ON
estadistic2002.IdProducto = producto.id
INNER JOIN tipoproducto
ON
producto.idTipoProducto = tipoproducto.id
GROUP BY
tipoproducto.Id


estructura de las tablas que se emplean

/* 30 000 000 de registros */
CREATE TABLE `estadistic2002` (
`IdProducto` bigint(20) NOT NULL,
`IdLugar` int(11) NOT NULL,
`Fecha` date NOT NULL,
`existe` decimal(12,4) DEFAULT NULL,
`existeC` decimal(12,4) DEFAULT NULL,
`ventasU` decimal(12,4) DEFAULT NULL,
`ventasC` decimal(12,4) DEFAULT NULL,
`ventasV` decimal(12,4) DEFAULT NULL,
`ventasU08` decimal(12,4) DEFAULT NULL,
`ventasC08` decimal(12,4) DEFAULT NULL,
`ventasV08` decimal(12,4) DEFAULT NULL,
`comprasU` decimal(12,4) DEFAULT NULL,
`comprasC` decimal(12,4) DEFAULT NULL,
`comprasUT` decimal(12,4) DEFAULT NULL,
`comprasCT` decimal(12,4) DEFAULT NULL,
PRIMARY KEY (`IdProducto`,`IdLugar`,`Fecha`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



/* 4 000 000 de registros */
CREATE TABLE `producto` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`idTipoProducto` bigint(20) unsigned DEFAULT NULL,
`codigoConFlag` char(13) DEFAULT NULL,
`TIP_PROD` char(2) DEFAULT NULL,
`fechaLote` date DEFAULT NULL,
`fechaVense` date DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `codigoConFlag` (`codigoConFlag`),
KEY `TIP_PROD` (`TIP_PROD`),
KEY `fechaLote` (`fechaLote`)
) ENGINE=MyISAM AUTO_INCREMENT=890528 DEFAULT CHARSET=latin1;



/* 1 000 000 de registros */
CREATE TABLE `tipoproducto` (
`Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`TIP_PROD` char(2) DEFAULT NULL,
`CODPROD` char(13) NOT NULL,
`CODEAN` char(13) DEFAULT NULL,
`SCTA` char(2) DEFAULT NULL,
`DESPROD` char(55) DEFAULT NULL,
`UM` char(3) DEFAULT NULL,
`MARCA` char(15) DEFAULT NULL,
`MODELO` char(25) DEFAULT NULL,
`CONT` float(8,3) unsigned zerofill DEFAULT NULL,
`UMG` char(3) DEFAULT NULL,
`COSTO` float(11,4) DEFAULT NULL,
`VENTA` float(10,2) DEFAULT NULL,
`CODARM` char(11) DEFAULT NULL,
`CODPESA` char(5) DEFAULT NULL,
`CODAGRE` char(8) DEFAULT NULL,
`CODSUM` char(11) DEFAULT NULL,
`COS_BASE` float(11,4) DEFAULT NULL,
`NACIONAL` char(1) DEFAULT NULL,
`FECHA_ALT` date DEFAULT NULL,
`FECHA_MOD` date DEFAULT NULL,
`GRUPO` char(2) DEFAULT NULL,
`INSUMO` bit(1) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `CODPROD` (`CODPROD`)
) ENGINE=MyISAM AUTO_INCREMENT=486528 DEFAULT CHARSET=latin1;

Options: ReplyQuote


Subject
Views
Written By
Posted
3191
July 05, 2007 10:20AM
Re: Tablas Grandes
3291
July 05, 2007 10:45AM
2924
July 17, 2007 05:29AM
2409
August 01, 2007 05:51AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.