I have this simple stored procedure that executes once per day to update the "energy" of the users depending on how many materials they have. But this takes around 2 minutes to end and I am wondering if there is a better way to do it:
BEGIN
SET @energy_premium = 10;
SET @energy_free = 5;
UPDATE user
SET energy = @energy_premium
WHERE id IN (
SELECT fk_user
FROM material
GROUP BY fk_user
HAVING COUNT(fk_user)>=2 AND user.id = material.fk_user);
UPDATE user
SET energy = @energy_free
WHERE id IN (
SELECT fk_user
FROM material
GROUP BY fk_user
HAVING COUNT(fk_user)=1 AND user.id = material.fk_user);
END
Also, when this stored procedure is executing my back-end services can't make transactions to the database.
CodePudding user response:
Test this:
BEGIN
SET @energy_premium = 10;
SET @energy_free = 5;
UPDATE user
JOIN ( SELECT fk_user, CASE COUNT(fk_user) WHEN 1
THEN @energy_free
ELSE @energy_premium
END energy
FROM material
GROUP BY fk_user ) mat ON user.id = mat.fk_user
SET user.energy = mat.energy;
END;