Very slow big mysql query

[jkubat]

Hello professionals,

this is a query, which lasted 46 seconds:

SELECT  
accounts.*  ,accounts_cstm.kod_partnera_c,accounts_cstm.id_cg_is2_c,accounts_cstm.lng_cg_is2_c,accounts_cstm.posledni_paragon_c,accounts_cstm.kod_tridy_c , 
CONCAT(IFNULL(jt0.first_name,''),' ',IFNULL(jt0.last_name,'')) modified_by_name , jt0.created_by modified_by_name_owner  , 
'Users' modified_by_name_mod , CONCAT(IFNULL(jt1.first_name,''),' ',IFNULL(jt1.last_name,'')) created_by_name , jt1.created_by created_by_name_owner  , 
'Users' created_by_name_mod , CONCAT(
IFNULL(jt2.first_name,''),' ',IFNULL(jt2.last_name,'')) assigned_user_name , jt2.created_by assigned_user_name_owner  , 
'Users' assigned_user_name_mod , ts1.team_count team_count , ts1.created_by team_count_owner  , 'Teams' team_count_mod
 , CONCAT(IFNULL(tj.name,''),' ',IFNULL(tj.name_2,'')) team_name , tj.created_by team_name_owner  , 
 'Teams' team_name_mod , jt5.name parent_name , jt5.assigned_user_id parent_name_owner  , 
 'Accounts' parent_name_mod , jt6.name campaign_name , jt6.assigned_user_id campaign_name_owner  , 
 'Campaigns' campaign_name_mod , CONCAT(IFNULL(jt7.first_name,''),' ',IFNULL(jt7.last_name,'')) modified_user_name ,
  jt7.created_by modified_user_name_owner  , 'Users' modified_user_name_mod
 
FROM accounts 

INNER JOIN 

(select tst.team_set_id 
from team_sets_teams tst 
INNER JOIN team_memberships team_memberships ON tst.team_id = team_memberships.team_id  
AND team_memberships.user_id = '54b8a29f-a681-0764-3a33-4d9f0a75b1c0' 
AND team_memberships.deleted=0 
group by tst.team_set_id) accounts_tf 

on accounts_tf.team_set_id  = accounts.team_set_id   

LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c

LEFT JOIN  users jt0 ON
jt0.id= accounts.modified_user_id AND jt0.deleted=0
 AND jt0.deleted=0  LEFT JOIN  users jt1 ON
jt1.id= accounts.created_by AND jt1.deleted=0
 AND jt1.deleted=0  LEFT JOIN  users jt2 ON
jt2.id= accounts.assigned_user_id AND jt2.deleted=0
 AND jt2.deleted=0  LEFT JOIN  team_sets ts1 ON
ts1.id= accounts.team_set_id AND ts1.deleted=0
 AND ts1.deleted=0  LEFT JOIN  teams tj ON
tj.id= accounts.team_id AND tj.deleted=0
 AND tj.deleted=0  LEFT JOIN  accounts jt5 ON
jt5.id= accounts.parent_id AND jt5.deleted=0
 AND jt5.deleted=0  LEFT JOIN  campaigns jt6 ON
jt6.id= accounts.campaign_id AND jt6.deleted=0
 AND jt6.deleted=0  LEFT JOIN  users jt7 ON
jt7.id= accounts.modified_user_id AND jt7.deleted=0
 AND jt7.deleted=0 where (accounts.name like 'c%') AND accounts.deleted=0 ORDER BY accounts.name LIMIT 0,30;

Output from explain is in attachment.


Is it possible to better optimize the query in SugarCRM?

Thank you very much.