Redundant Indexes

Question

It looks like there are several redundant or partly redundant indexes that civicrm creates on the database. Some of this may be storage engine specific (e.g. InnoDB), but some isn't.

civicrm_contact:

PRIMARY KEY (`id`)
KEY `index_is_deleted_sort_name` (`is_deleted`,`sort_name`,`id`)

In this case, the id suffix on the index_is_deleted_sort_name index is redundant for InnoDB which contains the PK as the payload in each index entry.

civicrm_discount:

KEY `index_entity` (`entity_table`,`entity_id`)
KEY `index_entity_option_id` (`entity_table`,`entity_id`,`price_set_id`)

index_entity is a prefix subset of index_entity_option_id which makes it completely redundant.

civicrm_entity_file:

KEY `index_entity` (`entity_table`,`entity_id`)
UNIQUE KEY `UI_entity_table_entity_id_file_id` (`entity_table`,`entity_id`,`file_id`)

index_entity is a prefix subset of UI_entity_table_entity_id_file_id which makes it completely redundant.

civicrm_financial_type

UNIQUE KEY `UI_id` (`id`)
PRIMARY KEY (`id`)

PKs are implicitly unique, so the unique key on id is completely redundant.

civicrm_iats_journal:

KEY `tnid_2` (`tnid`)
UNIQUE KEY `tnid` (`tnid`)

tnid_2 is redundant.

civicrm_line_item:

KEY `index_entity` (`entity_table`,`entity_id`)
UNIQUE KEY `UI_line_item_value` (`entity_table`,`entity_id`,`contribution_id`,`price_field_value_id`,`price_field_id`)

index_entity is redundant.

civicrm_queue_item:

KEY `index_queueids` (`queue_name`,`weight`,`id`)
PRIMARY KEY (`id`)

PK suffix is redundant in index_queueids.

civirule_action:

UNIQUE KEY `id_UNIQUE` (`id`)
PRIMARY KEY (`id`)

id_UNIQUE is redundant.

civirule_condition:

UNIQUE KEY `id_UNIQUE` (`id`)
PRIMARY KEY (`id`)

id_UNIQUE is redundant.

civirule_rule:

UNIQUE KEY `id_UNIQUE` (`id`)
PRIMARY KEY (`id`)

id_UNIQUE is redundant.

civirule_rule_action:

UNIQUE KEY `id_UNIQUE` (`id`)
PRIMARY KEY (`id`)

id_UNIQUE is redundant.

civirule_rule_condition:

UNIQUE KEY `id_UNIQUE` (`id`)
PRIMARY KEY (`id`)

id_UNIQUE is redundant.

civirule_rule_log:

KEY `rule_idx` (`rule_id`)
KEY `rule_contact_idx` (`rule_id`,`contact_id`)

rule_idx is redundant.

civirule_rule_tag:

UNIQUE KEY `id_UNIQUE` (`id`)
PRIMARY KEY (`id`)

id_UNIQUE is redundant.

civirule_trigger:

UNIQUE KEY `id_UNIQUE` (`id`)
PRIMARY KEY (`id`)

id_UNIQUE is redundant.

I think these qualify as performance bugs as each redundancy will add overhead on writes and memory usage.

Answer 1

To reduce apparently "unanswered" questions...

The answer here is:

1. yes, you're right, good catch! The suggestions that relate to CiviCRM core have been acted upon, thanks for contributing! See the issue and the merge request.

2. the way to report problems is to submit an issue checking that there's not one there already. This is the right thing to do if you can't fix the issue yourself.

3. the way to suggest improvements is through submitting a "pull" or "merge" request (PR/MR)

There's more about (2) and (3) in the developer docs

And finally of relevance: issues and pull/merge requests for extensions will be found in the repository for that extension; typically on https://lab.civicrm.org or somewhere on github (look at the extension's info.xml file for the repo address).