0

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

1
  • Which of the queries takes long? Can you replicate the behavior by executing the queries one by one directly without the procedure? If yes, use EXPLAIN to see the difference in execution plans.
    – jkavalik
    Jul 8, 2016 at 11:43

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.