Help with slow query logging and optimization

[stevec]

Just wondering if any good folks here can help me out.

I'm going through slow query logging to find any bottlenecks. The major one seems to be from unified searching. It's the pro version and we modified it slightly to also search on joined full-names.

Anyway, I've included a sample query and the 'explain'ed mysql execution plan.

A lot of these searches are 2 secs plus. And I'm wondering if any extra indexing can help.

As it's the pro version, it includes the team table. (I've detailed the team table indexes to help).

Thanks:

Query:

SELECT
                                contacts.*,
                accounts.name as account_name,
                accounts.id as account_id,
                accounts.assigned_user_id account_id_owner,
                users.user_name as assigned_user_name ,teams.name AS team_name  , contacts_cstm.*
                FROM contacts INNER JOIN team_memberships team_memberships
                                ON contacts.team_id = team_memberships.team_id
                                AND team_memberships.user_id = '4a9cae1e-42bf-3caf-b822-43c7df2ca761'
                                AND team_memberships.deleted=0 LEFT JOIN users
                            ON contacts.assigned_user_id=users.id
                            LEFT JOIN accounts_contacts
                            ON contacts.id=accounts_contacts.contact_id
                            LEFT JOIN accounts
                            ON accounts_contacts.account_id=accounts.id  LEFT JOIN teams ON contacts.team_id=teams.id AND (teams.deleted=0)  LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c where (contacts.last_name like 'martins%' or contacts.first_name like 'martins%' or accounts.name like 'martins%' or contacts.assistant like 'martins%' or contacts.email1 like 'martins%' or contacts.email2 like 'martins%' or CONCAT(contacts.first_name,' ',contacts.last_name) like 'martins%') AND  contacts.deleted=0  AND (accounts.deleted is NULL or accounts.deleted=0)   AND (accounts_contacts.deleted is NULL or accounts_contacts.deleted=0)  ORDER BY last_name asc LIMIT 0,101

It just returns two rows but took 1.7 secs. the 'explain' looks like:

 id 	 select_type 	 table 	 		type 	possible_keys 	 													key 	 		key_len 	ref 	 					rows 	 Extra 
1	 SIMPLE 	 team_memberships 	ref 	idx_team_membership,idx_teammemb_team_user,idx_teamuser_del								idx_team_membership 	36	 	const 						3	 Using where; Using temporary; Using filesort 
1	 SIMPLE 	 contacts 	 	ref 	idx_cont_last_first,idx_contacts_del_last,idx_cont_del_reports,idx_contact_del_team,idx_cont_email1,idx_cont_email2	idx_contact_del_team 	38		const,sugarcrm.team_memberships.team_id		2468	 Using where 
1	 SIMPLE 	 users 	 		eq_ref 	PRIMARY 	 													PRIMARY 		36	 	sugarcrm.contacts.assigned_user_id 		1	  
1	 SIMPLE 	 accounts_contacts 	ref 	idx_acc_cont_cont 	 												idx_acc_cont_cont 	37	 	sugarcrm.contacts.id 				1	 Using where 
1	 SIMPLE 	 accounts 	 	eq_ref 	PRIMARY,idx_accnt_id_del												PRIMARY 		36	 	sugarcrm.accounts_contacts.account_id 		1	 Using where 
1	 SIMPLE 	 teams 	 		eq_ref 	PRIMARY,idx_team_del_name												PRIMARY 		36	 	sugarcrm.contacts.team_id 			1	  
1	 SIMPLE 	 contacts_cstm 	 	eq_ref 	PRIMARY 	 													PRIMARY 		36	 	sugarcrm.contacts.id 				1

The tem_membership indxes are:

idx_team_membership      `user_id`, `team_id`
idx_teammemb_team_user   `team_id`, `user_id`
idx_teamuser_del         `user_id`, `deleted`