Hello
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.id ,accounts_cstm.source_c, accounts.parent_id , accounts.assigned_user_id , accounts.name , jt0.name 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.id = accounts_cstm.id_c LEFT JOIN accounts jt0 ON accounts.parent_id=jt0.id AND jt0.deleted=0
AND jt0.deleted=0 LEFT JOIN users jt1 ON accounts.assigned_user_id=jt1.id AND jt1.deleted=0
AND jt1.deleted=0 where (((accounts.name like '%testname%' ) OR ( accounts.phone_office like '%testname%' ) OR ( accounts.id IN (select bean_id from (SELECT eabr.bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = 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 https://pgorod.github.io/Reindex-AOD/
right now my modules/AOD_Index/Index/Index is ~ 100MB
and MySQL table aod_indexevent has ~ 250k rows