Answers.Body: <p>To reduce apparently &quot;unanswered&quot; questions...</p>
  <p>The answer here is:</p>
  <ol>
  <li><p>yes, you're right, good catch! The suggestions that relate to CiviCRM core have been acted upon, thanks for contributing! See the <a href="https://lab.civicrm.org/dev/core/-/issues/2575" rel="nofollow noreferrer">issue</a> and the <a href="https://github.com/civicrm/civicrm-core/pull/20245" rel="nofollow noreferrer">merge request</a>.</p>
  </li>
  <li><p>the way to report problems is to <a href="https://lab.civicrm.org/dev/core/-/issues/?sort=created_date&amp;state=opened" rel="nofollow noreferrer">submit an issue</a> checking that there's not one there already. This is the right thing to do if you can't fix the issue yourself.</p>
  </li>
  <li><p>the way to suggest improvements is through <a href="https://github.com/civicrm/civicrm-core/pulls" rel="nofollow noreferrer">submitting a &quot;pull&quot; or &quot;merge&quot; request (PR/MR)</a></p>
  </li>
  </ol>
  <p>There's more about (2) and (3) in the <a href="https://docs.civicrm.org/dev/en/latest/core/contributing/" rel="nofollow noreferrer">developer docs</a></p>
  <p>And finally of relevance: issues and pull/merge requests for <em>extensions</em> will be found in the repository for that extension; typically on <a href="https://lab.civicrm.org" rel="nofollow noreferrer">https://lab.civicrm.org</a> or somewhere on github (look at the extension's info.xml file for the repo address).</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-05-13T13:36:47.310Z
Answers.LastActivityDate: 2022-05-13T13:36:47.310Z
Answers.UserDisplayName: artfulrobot
Body: <p>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.</p>
  <p>civicrm_contact:</p>
  <pre><code>PRIMARY KEY (`id`)
  KEY `index_is_deleted_sort_name` (`is_deleted`,`sort_name`,`id`)
  </code></pre>
  <p>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.</p>
  <p>civicrm_discount:</p>
  <pre><code>KEY `index_entity` (`entity_table`,`entity_id`)
  KEY `index_entity_option_id` (`entity_table`,`entity_id`,`price_set_id`)
  </code></pre>
  <p>index_entity is a prefix subset of index_entity_option_id which makes it completely redundant.</p>
  <p>civicrm_entity_file:</p>
  <pre><code>KEY `index_entity` (`entity_table`,`entity_id`)
  UNIQUE KEY `UI_entity_table_entity_id_file_id` (`entity_table`,`entity_id`,`file_id`)
  </code></pre>
  <p>index_entity is a prefix subset of UI_entity_table_entity_id_file_id which makes it completely redundant.</p>
  <p>civicrm_financial_type</p>
  <pre><code>UNIQUE KEY `UI_id` (`id`)
  PRIMARY KEY (`id`)
  </code></pre>
  <p>PKs are implicitly unique, so the unique key on id is completely redundant.</p>
  <p>civicrm_iats_journal:</p>
  <pre><code>KEY `tnid_2` (`tnid`)
  UNIQUE KEY `tnid` (`tnid`)
  </code></pre>
  <p>tnid_2 is redundant.</p>
  <p>civicrm_line_item:</p>
  <pre><code>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`)
  </code></pre>
  <p>index_entity is redundant.</p>
  <p>civicrm_queue_item:</p>
  <pre><code>KEY `index_queueids` (`queue_name`,`weight`,`id`)
  PRIMARY KEY (`id`)
  </code></pre>
  <p>PK suffix is redundant in index_queueids.</p>
  <p>civirule_action:</p>
  <pre><code>UNIQUE KEY `id_UNIQUE` (`id`)
  PRIMARY KEY (`id`)
  </code></pre>
  <p>id_UNIQUE is redundant.</p>
  <p>civirule_condition:</p>
  <pre><code>UNIQUE KEY `id_UNIQUE` (`id`)
  PRIMARY KEY (`id`)
  </code></pre>
  <p>id_UNIQUE is redundant.</p>
  <p>civirule_rule:</p>
  <pre><code>UNIQUE KEY `id_UNIQUE` (`id`)
  PRIMARY KEY (`id`)
  </code></pre>
  <p>id_UNIQUE is redundant.</p>
  <p>civirule_rule_action:</p>
  <pre><code>UNIQUE KEY `id_UNIQUE` (`id`)
  PRIMARY KEY (`id`)
  </code></pre>
  <p>id_UNIQUE is redundant.</p>
  <p>civirule_rule_condition:</p>
  <pre><code>UNIQUE KEY `id_UNIQUE` (`id`)
  PRIMARY KEY (`id`)
  </code></pre>
  <p>id_UNIQUE is redundant.</p>
  <p>civirule_rule_log:</p>
  <pre><code>KEY `rule_idx` (`rule_id`)
  KEY `rule_contact_idx` (`rule_id`,`contact_id`)
  </code></pre>
  <p>rule_idx is redundant.</p>
  <p>civirule_rule_tag:</p>
  <pre><code>UNIQUE KEY `id_UNIQUE` (`id`)
  PRIMARY KEY (`id`)
  </code></pre>
  <p>id_UNIQUE is redundant.</p>
  <p>civirule_trigger:</p>
  <pre><code>UNIQUE KEY `id_UNIQUE` (`id`)
  PRIMARY KEY (`id`)
  </code></pre>
  <p>id_UNIQUE is redundant.</p>
  <p>I think these qualify as performance bugs as each redundancy will add overhead on writes and memory usage.</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-04-27T11:20:45.367Z
Comments.Text: Thanks for identifying these.  I'd suggest you create an issue at https://lab.civicrm.org/dev/core/-/issues to get more developer focus on it.
Comments.UserDisplayName: Aidan
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-04-28T13:31:45.120Z
Comments.Text: @erikh-civicoop has created https://lab.civicrm.org/dev/core/-/issues/2575
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-05-10T13:29:40.863Z
Comments.Text: @gordan feel free to report an issue at https://lab.civicrm.org/extension/civirules and feel free to contribute with a merge request fixing this.
Comments.UserDisplayName: Jaap Jansma - CiviCooP
Tags: <database><issues><performance><datatables>
Title: Redundant Indexes
ThreadText: # Redundant Indexes
  
  (Asked by: username_0 on 2021-04-27)
  
  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.
  
  **Tags:** database, issues, performance, datatables
  
  > **Comments:**
  > **username_1**: Thanks for identifying these.  I'd suggest you create an issue at https://lab.civicrm.org/dev/core/-/issues to get more developer focus on it.  
  > **username_2**: @erikh-civicoop has created https://lab.civicrm.org/dev/core/-/issues/2575  
  > **username_3**: @gordan feel free to report an issue at https://lab.civicrm.org/extension/civirules and feel free to contribute with a merge request fixing this.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_4 on 2022-05-13. Score: 2
  
  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](https://lab.civicrm.org/dev/core/-/issues/2575) and the [merge request](https://github.com/civicrm/civicrm-core/pull/20245).
  2. the way to report problems is to [submit an issue](https://lab.civicrm.org/dev/core/-/issues/?sort=created_date&state=opened) 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)](https://github.com/civicrm/civicrm-core/pulls)
  
  There's more about (2) and (3) in the [developer docs](https://docs.civicrm.org/dev/en/latest/core/contributing/)
  
  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).
  
  
  ---
  
  