How to create mysql index


I have 600k records in Account module and this is the reason for slowing down the global search.

When I search for something I get results with 5-10 seconds delay
I turned slow log queries on

Mon Nov 9 12:55:49 2020 [418846][1][FATAL] Slow Query (time:5.4005880355835)

SELECT ,accounts_cstm.source_c, accounts.parent_id , accounts.assigned_user_id ,  , parent_name , jt0.assigned_user_id parent_name_owner  , 'Accounts' parent_name_mod, accounts.date_modified , accounts.date_entered  , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner  , 'Users' assigned_user_name_mod, accounts.created_by  FROM accounts  LEFT JOIN accounts_cstm ON = accounts_cstm.id_c   LEFT JOIN  accounts jt0 ON AND jt0.deleted=0
 AND jt0.deleted=0  LEFT JOIN  users jt1 ON AND jt1.deleted=0
 AND jt1.deleted=0 where ((( like '%testname%' ) OR ( accounts.phone_office like '%testname%' ) OR ( IN (select bean_id  from (SELECT eabr.bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON ( = eabr.email_address_id)  WHERE eabr.deleted=0 AND ea.email_address LIKE 'testname%') email_derived)))) AND accounts.deleted=0 ORDER BY accounts.date_entered DESC LIMIT 0,11

I try to use SQL tool EXPLAIN to see what I can do

For first row “Account” I can only made index between delete and date_entered but the search speed has not increased enough

Here’s my MySQL index for Accounts

What index should I create for this query?
does anyone know what indexes are needed for such large queries?

I have SuiteCRM 7.11.15
AOD enabled
I did reindexing by
right now my modules/AOD_Index/Index/Index is ~ 100MB
and MySQL table aod_indexevent has ~ 250k rows



Sorry for disturb you
but maybe you know anything about my questions?
Or you have hear how we can use SuiteCRM for large data? (let’s start from 5-10 modules with 50-100k record each)
sutecrm uses too many tables in one SQL query so how can it be optimized?

For example, every modules have two tables ‘main’ and ‘_cstm’ and SQL query must use JOIN’s…