Get all recipes from database with the cheapest ingredients

Question

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;

Answer 1

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.