Performance

…ok, so i was doing some database tuning recently to speed up one of our deployed instance of SugarCRM, when i became alert of an interesting phenomenon.

The table accounts not only had a primary key(id), which is normal, but also had a composite key(id, deleted) named idx_accnt_id_del. Now wait a minute! Even if you perform a query with id AND deleted criteria, id already points to a single record in the table, since it is a unique primary key. Thus the composite key makes absolutely no sense!
Mysqlperformanceblog, which i have read a lot lately, confirms my findings.
I didn’t try any of the tools they mention in the comments section, but by quickly searching for “id_del” in Sugar vardef files reveals that the following tables have the same problem:
- roles
- acl_actions
- acl_roles
- fields_meta_data