Indexing for find_in_set usage

Asked Jan 15 '15 at 17:27

Active Jan 15 '15 at 18:57

Viewed 740 times

I am using inner join on 3 tables and one of my columns in one table is comma separated values(varchar) like m03412,3456,skeys and I am using FIND_IN_SET in one of the inner joins.

Query takes around 3 mins .

Is there any way to reduce the time by indexing?

I have 2 tables

user

userid ,
managerid ,
training_title, status and approverid ....

details

userid and name

Select a.userid as userid, a.training_title as training_title,
      a.status as status,c.userid as approverid,b.name as 'Manager_Name',
      c.name as 'ApproverName'

from
      user  a 
       join details  b on a.managerid = b.userid
       join details  c on find_in_set(c.userid,a.approverid) > 0
       where a.managerid=@mid and a.approverid <> '' 
       group by approverid order by ApproverName

edited Jan 15 '15 at 18:57

asked Jan 15 '15 at 17:27

Learner2011

Please do a `EXPLAIN SELECT ...` and add the Output to your question – Benvorth Jan 15 '15 at 22:55
1

You should add an index on `details.userid`. An Index on `user.approverid` will not be used since you wrapped the col in the `find_in_set` method – Benvorth Jan 15 '15 at 22:56
1

An index on `user.managerid` might help as well – Benvorth Jan 15 '15 at 22:57

Indexing for find_in_set usage

user

details

0 Answers0