big table support

[pmfreire]

Hello,

I am using Sugar 5 CE and imported a large number of accounts as admin, for later processing and user assignment.
The Accounts table grew to abou 39.000 records.

I don't know if this is a bug or if a table this large is just not supported, but now the login as admin takes hours (literally). If I login as another user everything is ok.

May this be an Apache problem or something with Sugar ? Has anyone seen this before ?

Thanks

[kuske]

On login and on clicking home the dashlet "my accounts" selects all accounts owned by the current user.
So, if there are a lot of account owned by admin this can take some time.
Try to remove the "my accounts" dashlet-

An other performance improvement would be to change the database table engine from myISAM to innoDB.
As the Name myISAM says it is a sequential file format, not very effective for great tables.

[pmfreire]

I changed the table type to InnoDB but did no difference. I don't think the problem is at MySQL, 'cause when Sugar is "thinking" slow, I access the database (for example with phpmyadmin) and make all type of queries on the accounts table and it is fast.

Also, removing the dashlet may solve one problem but I need to access Accounts, and when I try to do rhis, well ... I can go out for lunch at this time

Maybe the process can be modified ? It makes no sense that the function(s) select ALL records ... any list should count all records (only count) to make the number of pages calculation and then select only the visible ones (first 20, 41 to 60, etc). If it doesn't problems arise at large tables.

But am I the only one that have noticed this ? No one else has tables this large ?

[kuske]

I tested it in my environment with 50.000 Accounts and did not have any problem.
Perhaps your admin user has a special selection of account options which leads to long sql execution time.
I would propose to look on the selected fields and to define database indexes in mySQL for the selected fields, perhaps this would help.

[pmfreire]

Thanks for your feedback. That info showed me that what is happening with me is not what normally happens (I was thinking that no one ever tested Sugar with large tables )

So, the problem must be in something particular that I'm doing. What came first to my mind was the fact that I added some custom fields to the Accounts table (two geo fields and the fiscal number). I emptied the accounts_cstm and logged in to Sugar and selected Accounts tab and so on.

EVERYTHING WENT FINE!

I populated the accounts_cstm table with the data and the problems arose. So, the problems I complain have something to do with these custom fields.

Can you confirm/replicate this ?

[jjwdesign]

Just a couple of thoughts...

Does your _cstm table have a PRIMARY index for the id_c field? If not, add one.

I run a large MySQL database with INODB tables. The 'emails' table gets rather big; currently at 388MB and 63,000+ records. I have had it reach over 600MB in size and SugarCRM handled it.

You might also want to periodically "check", "defragment" and/or "optimize" the table(s).

Also, using the MySQL cache features has helped my systems performance.

Good luck,
Jeff Walters

[kuske]

You should add indexes to these custom fields

e.g.
ALTER TABLE accounts_cstm ADD INDEX Index_fiscal(`fiscalno_c`);
ALTER TABLE accounts_cstm ADD INDEX Index_geo1(`geo1_c`);
ALTER TABLE accounts_cstm ADD INDEX Index_geo2(`geo2_c`);

I think your fiscal number is a relatively long varchar field, this should have an index.
If the geo fields are chars too, they shoud have too.

hk