…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

These tables are usually small, and don’t have frequent insert operations, so it is not a big performance issue, but the aforementioned accounts can grow big, and managing an unnecessary index with a length of 38 bytes is just a burden on the database. The redundant index is present in an older Sugar 4.5 and the latest 5.0.0f, too.

UPDATE: I posted it on Sugar forums, lets see the feedback:)

This is an archive post. It represents a point of view in the past. Facts might have changed, events might be interpreted differently as of today. Links might be broken.