I have a terrible query from a design that I inherited. Here it is..
SELECT
i.id, i.description AS ingredient, effective_date, uom.symbol AS purchase_uom,
(sum(ri.regular_qty * t.regular) + sum(ri.large_qty * t.large)) * uc.factor AS qty
FROM
(SELECT m.id AS mealID, effective_date,
SUM(CASE
WHEN c.portion = -1 THEN 1
WHEN c.portion = 0 THEN 1
WHEN c.portion = 2 THEN 2
END) AS regular,
SUM(CASE
WHEN c.portion = 1 THEN 1
END) AS large
FROM `client` c
JOIN client_menu cm ON c.idUser=cm.idClient
JOIN meal m ON cm.id_meal=m.id
WHERE effective_date BETWEEN '2016-01-20' AND '2016-01-20'
AND c.node_id = '3'
AND id_meal >0
GROUP BY effective_date, m.id
ORDER BY meal_type,m.long_name,cm.notes, c.portion
) AS t
JOIN recipes r ON t.mealID = r.meal_id
JOIN recipe_ingredients ri ON r.id = ri.recipe_id
JOIN ingredients i ON ri.ingredient_id = i.id
JOIN uom_conversions uc ON uc.uom_from = i.recipe_uom AND uc.uom_to = i.purchase_uom
JOIN uom ON uom.id = i.purchase_uom
GROUP BY effective_date, i.id ORDER BY i.description ASC
when I run the query in phpmyadmin it takes 20 millisec. But within my laravel app the query takes 40-50 seconds.
I did some basic profiling to confirm that the delay is in the query and not somewhere else.
To run teh query in laravel I have tried:
$pdo = DB::getPdo();
$query = $pdo->query($sql);
$data = $query->fetchAll();
and also
$data = DB::select(DB::raw($sql));
I have spent days on this, I have no clue how to fix it. Please help....
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community