by eggsurplus

Control what your users can access and save time, money, and frustrations. Lock down sensitive data in SuiteCRM to specific groups or teams. Supports unlimited assigned users, unlimited group assignments to records, custom layouts for each group, login/sudo capabilities and much more.

Cancel at any time!
Free Trial

Optimizing SecuritySuite

Been on SecuritySuite for awhile now and need it to run faster? Here are a few things try:

Remove all of the deleted records in securitygroups_records.

delete from securitygroups_records where deleted = 1;

Then delete duplicates

delete r from securitygroups_records r
inner join securitygroups_records r2 on r.module = r2.module
    and r.record_id = r2.record_id and r2.deleted = 0
    and r.id <> r2.id and r.date_modified > r2.date_modified
where r.deleted = 0;

Alternative Query Method

Depending on the database engine, it may be beneficial to enable an option to use a different query method. To try this add the following to your config_override.php:

$sugar_config['securitysuite_use_exists_query'] = true;

Other options

You can find records with many groups associated to it with (change table/count as desired):

select m.name, count(1)
from meetings m
inner join securitygroups_records r on m.id = r.record_id and r.module = 'Meetings' and r.deleted = 0
group by r.id
having count(r.id) > 2;

Some other things to try:
1) Upgrade to at least MySQL 5.5 if you haven't already (200-360% performance boost).
2) Reassess how groups are being used
3) Make sure InnoDB is being used as the db engine
4) Disable list count queries in SuiteCRM
5) Reassess and apply tuner recommendations (https://github.com/rackerhacker/MySQLTuner-perl)
6) Optimize MySQL tables (mysqlcheck)
7) See if it's a specific module: select module, count(*) from securitygroups_records group by module;
8) Defrag the securitygroups_records table (after doing deletes):

http://dev.mysql.com/doc/refman/5.0/en/innodb-file-defragmenting.html

ALTER TABLE securitygroups_records ENGINE=INNODB;
OPTIMIZE TABLE securitygroups_records;
  1. pgr member avatar

    pgr

    2 years ago

    Hi there, thanks for this article. I'm trying to help someone clean up they're database and this is useful.
    I have a question - is the securitygroups_records table prone to accumulating orphaned records? I mean, if the record it references is deleted (say, a contact), will the corresponding row in securitygroups_records be deleted? If not, what would be a nice, safe query to delete these orphaned records? thanks

    • eggsurplus member avatar

      eggsurplus Provider Affiliate

      2 years ago

      As you know, there are so many ways that records get updated in SuiteCRM, especially in regards to relationships. So it's not certain that securitygroups_records will always be marked as deleted=1. To be safe I suggest doing a left join from the securitygroups_records table to the related table to see if the related table is actually deleted and if so then deleting. Then do a similar join but check if the related table is deleted = 1 and then delete if so. It's best to be safe. I hope this helps!

  2. pgr member avatar

    pgr

    2 years ago

    Yes I agree it's understandable that there are left-overs, it's hard to cover everything without risking deleting too much.

    My difficulty with this query is that the key structure (which seems to be record_id+module) doesn't really allow me to get all orphaned records in a single query, does it? I would have to do it module by module, to join with the correct table, right?

    • eggsurplus member avatar

      eggsurplus Provider Affiliate

      2 years ago

      I would suggest doing it module by module. Not worth building a super query. That would likely require doing a loop on all securitygroups_records rows and dynamically building subqueries.

    • pgr member avatar

      pgr

      2 years ago

      Ok. Thank you for your help!

Saving Comment Saving Comment...