4

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.

3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.