mysql - Same SQL runs fast in QUERY but very slowly in SP?

Question

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

QUERY

 start transaction;
   set @recordClient = (select ClientId  from by_test_db1.recordcd where SN = 'abc' );
   set @logClient    = (select ClientId  from by_test_db1.log      where SN = 'abc' );
   select concat(@recordClient,@logClient);
 commit;

SP

delimiter $$
create procedure TEST(newSN varchar(50))
begin 
     start transaction;
        set @recordClient = (select ClientId  from by_test_db1.recordcd where SN = newSN );
        set @logClient    = (select ClientId  from by_test_db1.log      where SN = newSN );
        select concat(@recordClient,@logClient);
     commit;
end $$
delimiter ;

call TEST('abc');

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 don't even know how to search the answer to this situation.

question from:https://stackoverflow.com/questions/65947563/same-sql-runs-fast-in-query-but-very-slowly-in-sp

Answer 1

Add indexes

INDEX(SN)  -- on both tables

Simplify query

SELECT  CONCAT(
           ( select ClientId  from by_test_db1.recordcd where SN = 'abc' ),
           ( select ClientId  from by_test_db1.log      where SN = 'abc' ) );