1
\$\begingroup\$

I have the following query which tries to get all the cheapest products linked to my recipes and it takes 10 seconds to return from the database:

SELECT PRODUCTOS.id, PRODUCTOS.titulo, CONCAT (INGREDIENTES.titulo," ", CONCAT(RECETA_INGREDIENTE.cantidad * $pers, "", PRODUCTOS.medida)) as ingrediente , precio, precio_medio , RECETA_INGREDIENTE.cantidad as cantidad_receta, PRODUCTOS.Volumen as cantidad_producto, PRODUCTOS.imagen, RECETA_INGREDIENTE.id_ingrediente as idingrediente, PRODUCTOS.precio_medio as precio_medio, PRODUCTOS.medida
FROM PRODUCTOS    
INNER JOIN INGREDIENTES ON INGREDIENTES.id = PRODUCTOS.ingrediente 
INNER JOIN RECETA_INGREDIENTE ON INGREDIENTES.id = RECETA_INGREDIENTE.id_ingrediente    
WHERE precio = (SELECT MIN(precio) FROM PRODUCTOS AS f WHERE f.ingrediente = PRODUCTOS.ingrediente) AND RECETA_INGREDIENTE.id_receta = $id;

I found the SELECT MIN(precio)... within the WHERE case is making the query take too long. I found out the price is a float column which I believe is not the most suitable data type for a price. This is taking products with the cheapest price in the PRODUCTS table and I was thinking about replace the FROM PRODUCTS part with something more like this:

select *,MIN(precio) from PRODUCTOS where ingrediente is not null group by titulo;

But I still couldn't get it working. Any advice?

The output is all the ingredients for a particular recipe with the cheapest products.

Table definition:

/*Table structure for table `INGREDIENTES` */

DROP TABLE IF EXISTS `INGREDIENTES`;

CREATE TABLE `INGREDIENTES` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `titulo` varchar(255) DEFAULT NULL,
  `slug` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8;

/*Table structure for table `PRODUCTOS` */

DROP TABLE IF EXISTS `PRODUCTOS`;

CREATE TABLE `PRODUCTOS` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `titulo` varchar(255) DEFAULT NULL,
  `categoria` varchar(255) DEFAULT NULL,
  `precio` float DEFAULT NULL,
  `precio_medio` float DEFAULT NULL,
  `volumen` int(5) DEFAULT '0',
  `promo` varchar(255) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `ingrediente` int(11) DEFAULT NULL,
  `ref` int(11) DEFAULT NULL,
  `imagen` varchar(255) DEFAULT NULL,
  `medida` varchar(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=321 DEFAULT CHARSET=utf8;

/*Table structure for table `RECETAS` */

DROP TABLE IF EXISTS `RECETAS`;

CREATE TABLE `RECETAS` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `titulo` varchar(255) DEFAULT NULL,
  `cal` varchar(50) DEFAULT NULL,
  `descripcion` text,
  `imagen` varchar(255) DEFAULT NULL,
  `orden` int(11) DEFAULT NULL,
  `visible` tinyint(1) DEFAULT '0',
  `TYPE` varchar(255) DEFAULT NULL,
  `id_tipo` int(1) DEFAULT NULL,
  `id_dificultad` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

/*Table structure for table `RECETA_INGREDIENTE` */

DROP TABLE IF EXISTS `RECETA_INGREDIENTE`;

CREATE TABLE `RECETA_INGREDIENTE` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_ingrediente` int(11) DEFAULT NULL,
  `cantidad` int(11) DEFAULT NULL,
  `orden` int(11) DEFAULT NULL,
  `id_receta` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8;
\$\endgroup\$
  • \$\begingroup\$ This sounds like more of a stack Overflow problem to be honest. there is no way that this query works as intended as the subselect in the where clause would not work as shown. That subselect returns a result set, not a scalar value. \$\endgroup\$ – Mike Brant Oct 5 '16 at 17:20
  • 3
    \$\begingroup\$ You should also get in the habit of using a consistent style when writing your queries. Your query, as written, is very hard to read (and therefore understand). Keep lines under 80 characters, use indentation, be consistent on capitalization for query syntax vs. database objects vs. values, be consistent on use of full table.field syntax vs. alias.field syntax vs. field only syntax.. \$\endgroup\$ – Mike Brant Oct 5 '16 at 17:23
0
\$\begingroup\$

I found two issues after some investigation

The first is that my "precio" column is float and that a bad idea to store currencies. I turn this into a double data type by using the following command:

ALTER TABLE PRODUCTOS MODIFY COLUMN precio double;

The second issue is the way I use the subquery to get the cheapest product price in every single product. Instead, I've changed my PRODUCTS table inside of my FROM case to a minified version. Basically, what I've done is get all the products with the cheapest price by ingredient and removes the need of use subqueries. Please, see the fixed version:

SELECT ...
FROM (
    SELECT * FROM PRODUCTOS AS P WHERE ingrediente is not null and P.precio = (
    select MIN(precio) from PRODUCTOS where P.ingrediente=PRODUCTOS.ingrediente group by PRODUCTOS.ingrediente LIMIT 1)
) AS PRODUCTOS
INNER JOIN INGREDIENTES ...

The result returned is exactly the same and I've moved from 1.8 seconds to 0.15 which I think is a great improvement.

\$\endgroup\$
  • 2
    \$\begingroup\$ double is not much better than float, for money should use an exact numeric datatype, e.g. DECIMAL(10,2). \$\endgroup\$ – dnoeth Oct 12 '16 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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