title: How to minimize process time of the procedure?
body: <p>I created such a quite complex stored procedure that checks the password from <code>c_sent_messages</code> table if exists in <code>c_passwords</code> table. Then it's entering some messages and updating records from the database. Finally it is entering the record from <code>c_sent_messages</code> to <code>archive</code> table. Everything was good before i add the two rows which add record to the <code>archive</code> and delete the message from <code>c_sent_messages</code> (to not inflate storage) </p>
  
  <pre><code>INSERT INTO archive SELECT *, NOW() FROM c_sent_messages WHERE id=idi;          
  DELETE FROM c_sent_messages WHERE  id=idi;
  </code></pre>
  
  <p>My processor is also a good one(Intel 7700 HQ) and i am not running any other software/process.
  After i added these two rows, the procedure took too many time to add records to <code>archive</code>  table. I want to minimize process time of this procedure. Is there any way to make it faster? (Adding 1000 records to the <code>archive</code> took 120 seconds)</p>
  
  <pre class="lang-sql prettyprint-override"><code>
  DELIMITER $$
  CREATE PROCEDURE flood () 
  BEGIN
  DECLARE i INT DEFAULT 0;
      declare ifExist int DEFAULT 0;
      DECLARE numara VARCHAR(10);
      declare adamin_gonderdigi varchar(150);
    declare idi int default 0;
  
  
      myloop: LOOP
  
  
  
  
        SET idi               = (SELECT id  from c_sent_messages where stats = 'N' limit 1);
          SET numara            = (SELECT sender  from c_sent_messages where id = idi);
          SET adamin_gonderdigi = (SELECT msg_text from c_sent_messages WHERE id = idi);
  
        select count(1) into ifExist from c_passwords b WHERE adamin_gonderdigi = b.pass;
  
          IF ifExist &gt; 0 THEN
              set ifExist = 0;
                  select count(1) into ifExist from c_sent_messages a WHERE a.stats ="N";
                  IF ifExist &gt; 0 THEN
                      INSERT INTO c_received_messages ( receiver, sender, msg_text, stats, x_date )
                          VALUES
                              (numara, "5757", "Code is received successfully.", "S",NOW());
                      UPDATE c_passwords a SET a.stats = "S", a.x_date = NOW()
                      WHERE pass = adamin_gonderdigi;         
  
                          update c_sent_messages set stats = 'S' where id = idi;
  
                  ELSE
  
                      INSERT INTO c_received_messages ( receiver, sender, msg_text, stats, x_date )
                      VALUES(numara,"5757","Code is used before!","S",NOW());
  
                      update c_sent_messages set stats = 'U' where id = idi;
  
                  END IF; 
  
          ELSE 
              INSERT INTO c_received_messages  ( receiver, sender, msg_text, stats, x_date )
              VALUES(numara,"5757","Please send a valid code!","S",NOW());
  
              update c_sent_messages set stats = 'F' where id = idi;
          END IF;     
  
          INSERT INTO archive SELECT *, NOW() FROM c_sent_messages WHERE id = idi;            
          DELETE FROM c_sent_messages WHERE  id=idi;
  
      SET i = i + 1;
          IF
              i = 900000 THEN
                  LEAVE myloop;
  
          END IF;     
      END LOOP myloop;
  
  END $$
  DELIMITER;
  
  </code></pre>