MySQL - Same SQL query fast ,but very slow in Stored Procedures?

Question

I had been stucked in this problem for more than 10 hours!

I had tried to add or remove the '@' before variables or params but nothing happened.

I really dont know whats going wrong , help please !

QUERY

select ClientId into @v1 from by_test_db1.recordcd where SN = 'abc';
select ClientId into @v2 from by_test_db1.log      where SN = 'abc';
select concat(@v1,@v2);

SP

delimiter $$
create procedure TEST(newSN varchar(50))
begin 
    declare v1 varchar(50);
    declare v2 varchar(50);
     start transaction;
        select ClientId into v1 from by_test_db1.recordcd where SN = newSN;
        select ClientId into v2 from by_test_db1.log      where SN = newSN;
        select concat(v1,v2);
     commit;
end $$
delimiter ;

MySQL version 5.7

Through there are 100 million rows in the recordcd table ,the QUERY just ran fast and well, but the SP was running so slowly that it timed out and reported an error

Error Code: 2013. Lost connection to MySQL server during query

I tried many ways but none of them worked, I don't know why there is such a ridiculous situation , I dont even know how to search the answer to this situation, so hope you big guys can help me , thank u.

question from:https://stackoverflow.com/questions/65936707/mysql-same-sql-query-fast-but-very-slow-in-stored-procedures

Answer 1

Waitting for answers