Optimizing a MySQL query to avoid “Using temporary” and “Using filesort”

Question

I know there's a thousand similar questions out there, but I have been two weeks trying to find a real solution for this query.

This query comes from a Point of Sale program. This query is related to a form where the user chooses the report he needs (periods, totals, etc.) and whether or not want grouped results.

This is inconvenient. The query is generated in VB.NET, I mean, by code, and it will vary according to user's selections (different totals, periods, groups, etc.), so with the solution to this question I should be able to continue "creating" by code all other queries of the form.

In this case, this query is a totals query grouped by family.

Most of the time (>99%) is wasted in SENDING DATA (show profile for query #)

The tables are as follows:

CREATE TABLE `product` (
  `idProduct` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `idFamily` tinyint(3) unsigned DEFAULT NULL,
  `Codigo` char(10) NOT NULL,
  `Nombre` char(70) DEFAULT NULL COMMENT 'Nombre corto',
-- five more integer columns
  PRIMARY KEY (`idProduct`),
  KEY `fk_p_idFamily` (`idFamily`),
  CONSTRAINT `fk_p_idFamily` FOREIGN KEY (`idFamily`) REFERENCES `family`  (`idFamily`),
) ENGINE=InnoDB AUTO_INCREMENT=19420 DEFAULT CHARSET=latin1 PACK_KEYS=0;

CREATE TABLE `family` (
  `idFamily` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `Nombre` char(30) NOT NULL,
  `Descripcion` char(255) DEFAULT NULL,
  `Borrado` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`idFamily`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1 PACK_KEYS=0

CREATE TABLE `document` (
`idDocument` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`Nombre` char(25) NOT NULL,
`Descripcion` char(100) DEFAULT NULL,
`Borrado` tinyint(1) NOT NULL DEFAULT '0',
`NoComputa` tinyint(1) NOT NULL DEFAULT '0',
  `Rectifica` tinyint(1) NOT NULL DEFAULT '0',
  `CalculoSumatorioPVP` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`idDocument`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 PACK_KEYS=0

CREATE TABLE `soldproduct` (
  `idProduct` smallint(5) unsigned NOT NULL,
  `idSale` int(10) unsigned NOT NULL,
  `PrecioCompra` decimal(7,2) NOT NULL ,
  `PrecioVenta` decimal(7,2) NOT NULL ,
  `DtoProd` decimal(7,4) DEFAULT NULL ,
  `BrutoUd` decimal(7,2) NOT NULL ,
  `PVPUd` decimal(7,2) NOT NULL ,
  `Cantidad` decimal(9,3) DEFAULT NULL ,
  PRIMARY KEY (`idProduct`,`idSale`),
  KEY `fk_pv_idSale` (`idSale`),
  CONSTRAINT `fk_pv_idProduct` FOREIGN KEY (`idProduct`) REFERENCES `product` (`idProduct`),
  CONSTRAINT `fk_pv_idSale` FOREIGN KEY (`idSale`) REFERENCES `sales` (`idSale`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=0

CREATE TABLE `sales` (
  `idSale` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `idDocument` tinyint(3) unsigned NOT NULL,
  `idEstadoVenta` tinyint(3) unsigned NOT NULL,
  `idCliente` smallint(5) unsigned NOT NULL,
  `idFormaPago` tinyint(3) unsigned NOT NULL,
  `idEmpleado` tinyint(3) unsigned NOT NULL ,
  `idTienda` tinyint(3) unsigned DEFAULT NULL,
  `idTipoVenta` tinyint(3) unsigned NOT NULL,
  `FechaVenta` datetime DEFAULT NULL COMMENT 'Fecha de Venta',
  `PrecioCompraTotal` decimal(10,2) DEFAULT NULL,
  `IVA` decimal(7,4) DEFAULT NULL,
  -- ten more decimal columns
  -- five more datetime columns
  -- ten more char columns
  `Borrado` tinyint(1) NOT NULL DEFAULT '0' ,
  `Historia` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`idVenta`),
  KEY `fk_v_idTienda` (`idTienda`),
  KEY `fk_v_idCliente` (`idCliente`),
  KEY `fk_v_idEmpleado` (`idEmpleado`),
  KEY `fk_v_idTipoVenta` (`idTipoVenta`),
  KEY `fk_v_idFormaPago` (`idFormaPago`),
  KEY `fk_v_idDocument` (`idDocument`),
  KEY `fk_v_idEstadoVenta` (`idEstadoVenta`),
  KEY `idx_v_FechaVenta` (`FechaVenta`),
  CONSTRAINT `fk_v_idCliente` FOREIGN KEY (`idCliente`) REFERENCES `cliente` (`idCliente`),
  CONSTRAINT `fk_v_idDocument` FOREIGN KEY (`idDocument`) REFERENCES `document` (`idDocument`),
  CONSTRAINT `fk_v_idEmpleado` FOREIGN KEY (`idEmpleado`) REFERENCES `empleado` (`idEmpleado`),
  CONSTRAINT `fk_v_idEstadoVenta` FOREIGN KEY (`idEstadoVenta`) REFERENCES `estadoventa` (`idEstadoVenta`),
  CONSTRAINT `fk_v_idFormaPago` FOREIGN KEY (`idFormaPago`) REFERENCES `formapago` (`idFormaPago`),
  CONSTRAINT `fk_v_idTienda` FOREIGN KEY (`idTienda`) REFERENCES `tienda` (`idTienda`),
  CONSTRAINT `fk_v_idTipoVenta` FOREIGN KEY (`idTipoVenta`) REFERENCES `tipoventa` (`idTipoVenta`)
) ENGINE=InnoDB AUTO_INCREMENT=101770 DEFAULT CHARSET=latin1 PACK_KEYS=0

And the query is this:

SELECT  f.Nombre  ,SUM(sp.PrecioVenta*sp.Cantidad)  
FROM soldproduct sp, sales s, document doc, family f, product p 
WHERE s.idDocument = doc.idDocument AND doc.NoComputa = FALSE
AND p.idProduct = sp.idProduct AND sp.idSale = s.idSale 
AND p.idFamily = f.idFamily AND p.Borrado = FALSE 
AND s.Borrado = FALSE AND s.Historia = FALSE AND s.idTienda = 1 
AND s.FechaVenta BETWEEN '2013-01-01' AND '2014-01-01' GROUP BY f.idFamily;

I've also try this one (I've also remove document table in case it is was responsible)

SELECT ProductFamily.Nombre, SUM(sp.PrecioVenta*sp.Cantidad) 
FROM 
(SELECT idSale FROM sales WHERE Borrado = FALSE AND Historia = FALSE AND idTienda = 1 
AND FechaVenta BETWEEN '2013-01-01' AND '2014-01-01') SalesidSale
JOIN
soldproduct sp
ON sp.idSale = SalesidSale.idSale
JOIN
(SELECT p.idProduct, p.idFamily, f.Nombre FROM product p, family f WHERE 
p.idFamily = f.idFamily AND p.Borrado = FALSE) ProductFamily
ON ProductFamily.idProduct = sp.idProduct
GROUP BY ProductFamily.idFamily;

The time it takes is very large, and its output to EXPLAIN command is (first query):

id  select_type table   type    possible_keys                                               key                 key_len ref                     rows    Extra
1   SIMPLE      v       range   PRIMARY,fk_v_idTienda,fk_v_idDocument,idx_v_FechaVenta      idx_v_FechaVenta    6       NULL                    7387    "Using index condition; Using where; Using MRR; Using temporary; Using filesort"
1   SIMPLE      doc     ALL     PRIMARY                                                     NULL                NULL    NULL                    4       "Using where; Using join buffer (Block Nested Loop)"
1   SIMPLE      pv      ref     PRIMARY,fk_pv_idSale                                        fk_pv_idSale        4       gemalia.s.idSale        4       NULL
1   SIMPLE      p       eq_ref  PRIMARY,fk_p_idFamily                                       PRIMARY             2       gemalia.sp.idProduct    1       "Using where"
1   SIMPLE      f       eq_ref  PRIMARY                                                     PRIMARY             1       gemalia.p.idFamily      1       NULL

I hope someone can help me, I tried to create indices, subqueries, etc.. but I can't get anything lower than 40 seconds, which is too much, and I'm sure that I'm doing something wrong..

The number of rows per table, is approximately: Sales: 100,000 Products: 20,000 Families: 35 SoldProducts: 1,100,000 Documents: 4

Thanks a lot.

Answer 1

Try this query in which we are trying to take advantage of short circuit.

EDIT

 SELECT  
   f.Nombre, 
   SUM(sp.PrecioVenta*sp.Cantidad)  
 FROM 
   soldproduct sp
 INNER JOIN 
   sales s
 ON 
   (s.idTienda = 1 AND AND 
   s.Borrado = FALSE AND 
   s.Historia = FALSE AND 
   sp.idSale = s.idSale)
 INNER JOIN
   document doc
 ON
   (doc.NoComputa = FALSE AND
   s.idDocument = doc.idDocument) 
 INNER JOIN
   family f
 ON
   (p.idFamily = f.idFamily) 
 INNER JOIN
   product p 
 ON
   (p.Borrado = FALSE AND         
   p.idProduct = sp.idProduct)
 WHERE 
   s.FechaVenta BETWEEN '2013-01-01' AND '2014-01-01' 
 GROUP BY 
   f.idFamily;