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.medidaFROM 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;
Get all recipes from database with the cheapest ingredients
performance;sql;mysql
I found two issues after some investigationThe 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 PRODUCTOSINNER 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.