Global search working slow

Hi All,

I would glad if you will guide me to make my Global Search work effictively in my SuiteCRM.
It is ttaking 1-2 minutes time while I am searching any data using it.

Kindly also let me know in what sequence I can troubleshoot it.

Regards,
Mohit

Please run the query at the top of this post and share results here:

https://pgorod.github.io/Database-tables-size/

We need to get a sense of how large your database is.

That post also explains where the index files are kept, and how to provoke a full reindex if necessary.

Hi Admin,

Thanks for your reply…

Please find the output of mentioned query :-

	Rows	Data	idx	Total_size	idxfrac
suitecrm_db.aod_indexevent	0.846M	0.280G	0.167G	0.447G	0.595
suitecrm_db.sugarfeed	0.463M	0.324G	0.039G	0.363G	0.119
suitecrm_db.opportunities	0.283M	0.175G	0.098G	0.272G	0.559
suitecrm_db.opportunities_copy-18sept2018	0.290M	0.111G	0.079G	0.190G	0.711
suitecrm_db.contacts	0.129M	0.083G	0.098G	0.181G	1.187
suitecrm_db.accounts_opportunities	0.264M	0.063G	0.115G	0.178G	1.821
suitecrm_db.opportunities_contacts	0.256M	0.037G	0.120G	0.156G	3.255
suitecrm_db.accounts_opportunities_copy-18sept2018	0.284M	0.040G	0.105G	0.145G	2.656
suitecrm_db.accounts	0.085M	0.057G	0.040G	0.098G	0.702
suitecrm_db.email_addr_bean_rel	0.177M	0.044G	0.046G	0.090G	1.061

Thx

If you have a test clone, I would try a full AOD reindex to see if it creates a much smaller (and so, faster) index. But if that doesn’t make any magical progress, you’ll have to think about doing some database maintenance, and perhaps some deletes to make your tables smaller.

I am not sure how much of your large database is actually composed of data that is interesting to you. We’d have to look at that in depth, sometimes tables get overgrown, or there are orphaned records (that point to a deleted record), or simply too many records with “deleted=1” which you can eliminate immediately if you’re not worried about the possibility of undeleting them one day.

Do you have the Scheduler job “Prune at 1st of month” active? What does it say in the “Last ran successfully” field? This job cleans up some records with “deleted=1”, though not all.

Do you have old data that you could remove from the database?

How big is your database?

Is it fast sometimes, or always slow?

Do you see any special errors in your logs during those long queries?

How big is your AOD Index directory? (In Linux, try
du -h modules/AOD_Index/Index/
from your SuiteCRM root directory)

Hi Alex,

Below is the answers to your queries :-

How big is your database?

–> As of now DB size is 2.5 GB but in future it will increase.

Is it fast sometimes, or always slow?

–> Global search is always slow if data exists in number of tables.

Do you see any special errors in your logs during those long queries?
–> No specific error logs.

How big is your AOD Index directory? (In Linux, try
du -h modules/AOD_Index/Index/
from your SuiteCRM root directory)
–> 215M Index/

Regards,
Mohit Dixit