by eggsurplus

Control what your users can access and save time, money, and frustrations. Lock down sensitive data in SugarCRM or 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.

Free 30 day trial
Try it Now

#176 - MYSQL Table Query (Speed) question

Closed Feature created by rgaitken Verified Purchase 6 years ago

Good Afternoon,

This is more of a question than a feature request. We are using the Security Groups module on system that has lots of records in the 'securitygroups_records' table, when a Contact / Lead / Target is being deleted it is taking a long time to update the table and is causing it to Lock between updates. We done some tracing on this a we can locate the issue in the M2MRelationship module and the issue relates to the missing module name on a delete which means MYSQL cant use the indexes that are on this table.

This is becoming a particular problem on mass deletes, therefore I was wondering if we should look at adding a new index on this table, one that does not include the module field or if we should try a looking elsewhere? Any help would be greatly appreciated as the module itself is proving very useful.

Thank you

Ron

  1. eggsurplus member avatar

    eggsurplus Provider Affiliate

    6 years ago

    I'd advise testing it with a new index to see how it performs. Also, try purging records in the securitygroups_records table that no longer exists. Curious, are you using MyISAM or InnoDB? There may be an opportunity to tune your my.cnf to tweak out better performance as well.

  2. rgaitken member avatar

    rgaitken Verified Purchase

    6 years ago

    We (actually our client) is using MyISAM but I think they will have to move to InnoDB for performance reasons. One of the developers has made a change to the add the module name in for Standard modules on the delete (update) and this seems to have made a difference, I can send you code if you are interested. Thanks

    • eggsurplus member avatar

      eggsurplus Provider Affiliate

      6 years ago

      Sure, send it over. My name @ My name . com. I'll keep this open to see if there is a possibility to add/change the index on the table.

  3. eggsurplus member avatar

    eggsurplus Provider Affiliate

    5 years ago

    Doing some cleanup. Feel free to follow up as needed.

This case is public. Please leave out any sensitive information such as URLs, passwords, etc.
Saving Comment Saving Comment...
Rating
Rating
  • "This impressive add on is easy to use, extremely flexible, and huge time saver. This add on exceeded our expectations and is worth every penny!"

    Read More Reviews

Keep up to date on the latest additions

We'll send you an email every month with handpicked add-ons, reviews, tricks and tips. Don't worry, we hate spam as much as you do.