title: mysql query running very slow. Please help me with indexes
body: <p>I'm trying to run a query which is taking 5 seconds to execute with 100000 rows. The query is given below. I've tried all possible indexes i could. Please suggest me what am i missing. </p>
  
  <pre><code>select distinct db_books.bookid as id
  , request_type.name as book_type
  , request_type.id as book_type_id
  , db_books.subject as subject
  , sender_user.uid as sender_user_id
  , sender_user.username as sender_user
  , sender_company.companyid as sender_company_id
  , sender_company.companyname as sender_company
  , sender_team_id.teamid as sender_team_id
  , sender_team_id.name as sender_team
  , GROUP_CONCAT(distinct receiver_user_details.uid separator '|') as receiver_user_id
  , GROUP_CONCAT(distinct receiver_user_details.username separator '|') as receiver_user
  , GROUP_CONCAT(distinct receiver_company.companyid separator '|') as receiver_company_id
  , GROUP_CONCAT(distinct receiver_company.companyname separator '|') as receiver_company
  , GROUP_CONCAT(distinct receiver_team_details.teamid separator '|') as receiver_team_id
  , GROUP_CONCAT(distinct receiver_team_details.name separator '|') as receiver_team
  , status.id as statusid
  , status.name as status
  , db_books.modifydate as modified_date
  , db_books.createddate as creation_date
  , state.id as stateid
  , state.name as state
  , assignee.uid as assignee_user_id
  , assignee.username as assignee_user
  , purpose.name as purpose
  , purpose.id as purposeid
  , g.name as entityname
  , g.entityid as entityid
  from db_books db_books 
  inner join db_users sender_user on (sender_user.deleted=0 and sender_user.uid=db_books.sndrUserid)
  inner join db_companies sender_company on (sender_company.deleted=0 and sender_company.companyid=db_books.sndrCompanyid)
  inner join db_companies receiver_company on (receiver_company.deleted=0 and receiver_company.companyid=db_books.target_company_id)
  inner join db_request_types request_type on (request_type.id=db_books.book_type_id)
  left outer join db_teams sender_team_id on (sender_team_id.deleted=0 and sender_team_id.teamid=db_books.sender_team_id)
  left outer join db_books_to_users receiver_user on (receiver_user.bookid=db_books.bookid)
  left outer join db_users receiver_user_details on (receiver_user_details.uid=receiver_user.userid)
  left outer join db_books_to_teams receiver_teams on (receiver_teams.bookid=db_books.bookid)
  left outer join db_teams receiver_team_details on (receiver_team_details.teamid=receiver_teams.teamid)
  left outer join db_request_status status on (status.id=db_books.statusid)
  left outer join db_request_state_types state on (state.id=db_books.request_state_id)
  left outer join db_request_purpose purpose on (purpose.id=db_books.request_purpose_id)
  left outer join db_users assignee on (assignee.uid=db_books.assignee)
  left outer join db_books_details mdtl on (mdtl.deleted=0 and mdtl.bookid=db_books.bookid)
  left outer join db_entities g on (g.deleted=0 and g.entityid=mdtl.entityid)
  where 1=1 
  and 
  (db_books.sndrUserid=25000000003265 
  or db_books.sender_team_id in (
              select a.teamid from db_team_users a 
              inner join db_teams b  on (b.teamid=a.teamid and b.deleted=0)
              where a.userid=25000000003265
          )
  or db_books.bookid in (
      select distinct bookid from db_books_to_users where userid=25000000003265
      union 
      select distinct bookid from db_books_to_teams where teamid in 
          (
              select a.teamid from db_team_users a 
              inner join db_teams b  on (b.teamid=a.teamid and b.deleted=0)
              where a.deleted=0 AND a.userid=25000000003265
          )
      )
  ) 
  group by db_books.bookid
  limit 20
  </code></pre>
  
  <p>The explain plan is as given below.</p>
  
  <pre><code>id  select_type table   type    possible_keys   key key_len ref rows    Extra
  1   PRIMARY sender_user ALL PRIMARY,u2              14573   Using where; Using temporary; Using filesort
  1   PRIMARY db_books    ref i_db_books_target_company_id,i_db_books_sndrUserid,i_db_books_sndrCompanyid,i_sndrUserid_sender_team_idbookid   i_db_books_sndrUserid   7   mde_staging.sender_user.uid 41  Using where
  1   PRIMARY sender_company  eq_ref  PRIMARY,db_companies_icd    PRIMARY 7   mde_staging.db_books.sndrCompanyid  1   Using where
  1   PRIMARY receiver_company    eq_ref  PRIMARY,db_companies_icd    PRIMARY 7   mde_staging.db_books.target_company_id  1   Using where
  1   PRIMARY sender_team_id  eq_ref  PRIMARY,db_teams_i  PRIMARY 7   mde_staging.db_books.sender_team_id 1   
  1   PRIMARY receiver_user   ref i_db_books_to_users_bookid  i_db_books_to_users_bookid  7   mde_staging.db_books.bookid 1   
  1   PRIMARY receiver_user_details   eq_ref  PRIMARY,u2  PRIMARY 7   mde_staging.receiver_user.userid    1   
  1   PRIMARY receiver_teams  ref i_db_books_to_teams_bookid  i_db_books_to_teams_bookid  7   mde_staging.db_books.bookid 1   
  1   PRIMARY receiver_team_details   eq_ref  PRIMARY,db_teams_i  PRIMARY 7   mde_staging.receiver_teams.teamid   1   
  1   PRIMARY status  eq_ref  PRIMARY PRIMARY 4   mde_staging.db_books.statusid   1   
  1   PRIMARY state   eq_ref  PRIMARY PRIMARY 4   mde_staging.db_books.request_state_id   1   
  1   PRIMARY purpose eq_ref  PRIMARY PRIMARY 4   mde_staging.db_books.request_purpose_id 1   
  1   PRIMARY assignee    eq_ref  PRIMARY,u2  PRIMARY 7   mde_staging.db_books.assignee   1   
  1   PRIMARY mdtl    ref db_books_details_bookid db_books_details_bookid 7   mde_staging.db_books.bookid 1   
  1   PRIMARY request_type    ALL PRIMARY             4   Using where; Using join buffer
  1   PRIMARY g   eq_ref  PRIMARY,db_entities7    PRIMARY 7   mde_staging.mdtl.entityid   1   
  3   DEPENDENT SUBQUERY  db_books_to_users   ref i_db_books_to_users_bookid  i_db_books_to_users_bookid  7   func    1   Using where; Using temporary
  4   DEPENDENT UNION db_books_to_teams   ref i_db_books_to_teams_bookid  i_db_books_to_teams_bookid  7   func    1   Using where; Using temporary
  5   DEPENDENT SUBQUERY  b   eq_ref  PRIMARY,db_teams_i  PRIMARY 7   func    1   Using where
  5   DEPENDENT SUBQUERY  a   ref db_team_users_i db_team_users_i 11  func,const  1   Using where
      UNION RESULT    &lt;union3,4&gt;  ALL                     
  2   DEPENDENT SUBQUERY  b   eq_ref  PRIMARY,db_teams_i  PRIMARY 7   func    1   Using where
  2   DEPENDENT SUBQUERY  a   ref db_team_users_i db_team_users_i 7   func    1   Using where
  </code></pre>
  
  <p>If you see the first row of the explain plan, it is not using the possible index and then using file sort etc. Not sure if that is the problem. Please suggest me how to fix this or me what indexes to use??</p>