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

Question

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

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

I have this simple stored procedure that programming executes once per day to update the Learning "energy" of the users depending on how Earhost many materials they have. But this takes most effective around 2 minutes to end and I am wrong idea wondering if there is a better way to do use of case it:

BEGIN
   SET @energy_premium = 10;
    _OFFSET);  SET @energy_free = 5;

    UPDATE user
  (-SMALL    SET energy = @energy_premium
    WHERE _left).offset  id IN (
        SELECT fk_user
        arrowImgView.mas  FROM material
        GROUP BY fk_user
  (self.        HAVING COUNT(fk_user)>=2 AND equalTo  user.id = material.fk_user);
        
   make.right.   UPDATE user
    SET energy = mas_top);  @energy_free
    WHERE id IN (
        ImgView.  SELECT fk_user
        FROM material
    ReadIndicator      GROUP BY fk_user
        HAVING _have  COUNT(fk_user)=1 AND user.id = .equalTo(  material.fk_user);
END

Also, when this stored procedure is United executing my back-end services can't Modern make transactions to the database.

MYSQL INNODB

Total Answers 1

mRahman · Accepted Answer · 2022-06-28T05:39:36+00:00

Test this:

BEGIN
    SET @energy_premium = 10;
    make.top  SET @energy_free = 5;

    UPDATE user
  OFFSET);    JOIN ( SELECT fk_user, CASE (TINY_  COUNT(fk_user) WHEN 1 
                  .offset                               THEN mas_right)  @energy_free
                            ImgView.                     ELSE @energy_premium
 Indicator                                           Read       END energy
           FROM _have  material
           GROUP BY fk_user ) .equalTo(  mat ON user.id = mat.fk_user
    SET make.left  user.energy = mat.energy;
END;

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

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

Answers 1 : of Optimize MySQL stored procedure that is blocking my back-end transactions

Top rated topics