Optimize MySQL stored procedure that is blocking my back-end transactions

December 13, 2021

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.

>Solution :

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;