Help optimize this select statement

MySQL & MariaDB Other MySQL® Questions
1

Hi forks,

I am new to mysql and am looking for a better way to do this.

I have a procedure in mysql which uses the following select statements to get the values in a temporary table. I would like to optimize these queries, anyone with idea how this could be done better.

SET xtrandate = (SELECT value FROM tempvalues WHERE valueid = ‘1’);
SET xserialno = (SELECT value FROM tempvalues WHERE valueid = ‘2’);
SET xfac = (SELECT value FROM tempvalues WHERE valueid = ‘3’);
SET xshift = (SELECT value FROM tempvalues WHERE valueid = ‘4’);
SET xrouteid = (SELECT value FROM tempvalues WHERE valueid = ‘5’);
SET xcpid = (SELECT value FROM tempvalues WHERE valueid = ‘6’);
SET xmemberid = (SELECT value FROM tempvalues WHERE valueid = ‘7’);
SET xoptr = (SELECT value FROM tempvalues WHERE valueid = ‘8’);
SET xwm = (SELECT value FROM tempvalues WHERE valueid = ‘9’);
SET xnocans = (SELECT value FROM tempvalues WHERE valueid = ‘10’);
SET xqty = (SELECT value FROM tempvalues WHERE valueid = ‘11’);
SET xtime = (SELECT value FROM tempvalues WHERE valueid = ‘12’);
SET xlac = (SELECT value FROM tempvalues WHERE valueid = ‘13’);
SET xalc = (SELECT value FROM tempvalues WHERE valueid = ‘14’);
SET xtemp = (SELECT value FROM tempvalues WHERE valueid = ‘15’);
SET xcanid = (SELECT value FROM tempvalues WHERE valueid = ‘16’);
SET xacceptreject = (SELECT value FROM tempvalues WHERE valueid = ‘17’);

Thanks for your time.

2

Can you please show structure of table and output of EXPLAIN for one of queries?

3

Thanks man, I have already figured out what to do.

For those for maybe having a similar problem, instead of storing the values in a temporary table, I decided to store the values in an array first then passed the values of the array to the procedure.

Thanks once again.