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;