Mysql – Stored procedure variable storage takes long time to execute

MySQLstored-procedures

I have a stored procedure which take an endless time for execution:

BEGIN
set @my_user_id=null;

select  user_id into @my_user_id
    from  office_user
    where  username like my_username
      and  `password` like MD5(my_password);

select  user_id,`name`,username,mobile_no,email,designation,role_id,
       signImage,colorCode,category,role_new.permission,report
    from  office_user
    INNER JOIN  role_new on office_user.role_id=role_new.roleId
    where  user_id=@my_user_id;

select  id,`name`,parent_id
    from  businesslevels
    where  parent_id=1
      AND  is_active =1
      AND  `name`!='Main'
      and  FIND_IN_SET(id,
(          ( SELECT  GROUP_CONCAT(topLevelId)
                    from  businesslevels
                    where  FIND_IN_SET(id,
(                         ( SELECT  GROUP_CONCAT(distinct bLevelid)
                                    from  forms
                                    where  FIND_IN_SET(Fname,
(                                        ( SELECT  GROUP_CONCAT(formName)
                                                    from  userforms
                                                    where  userType='office'
                                                      and  emp_id IN (@my_user_id))))))
                          )
    ORDER BY  `name`;

END

and when I am using this i.e. No variable usage, direct as a repeat sub query

This procedure execute quickly:

BEGIN
select  user_id,`name`,username,mobile_no,email,designation,role_id,
       signImage,colorCode,category,role_new.permission,report
    from  office_user
    INNER JOIN  role_new on office_user.role_id=role_new.roleId
    where  where username like my_username
      and  `password` like MD5(my_password);

select  id,`name`,last_name,emp_code,reporting_to,shift_start,
       shift_end,model,version,track_interval,conveyance,reporting_to
    from  field_user
    where  FIND_IN_SET(reporting_to,concat(@my_user_id,GetUserFamilyTree
(          ( SELECT  user_id
                            from  office_user
                            where  username like my_username
                              and  `password` like MD5(my_password))));


END

I don't know why this happening. I don't want to write subquery again. As there are more queries in procedure that need user_id

Any suggestion will be appreciated

Best Answer

Don't use FIND_IN_SET ( SELECT GROUP_CONCAT ... ). Instead JOIN the two tables together.

Related Question