Answers.Body: <p><strong>Disclaimer, as with other answers, this is aimed at a fairly technical person who kinda understands what they're doing and has made lots of backups :-)</strong></p>
  
  <p>As <a href="https://civicrm.stackexchange.com/a/18985/35">Sophie points out</a> you can find the exact problem looking at the latest log in the <code>ConfigAndLog</code> directory.</p>
  
  <p>For several of us the problem has been that there's a key <strong>defined incorrectly</strong> and the script's current response is to try a <code>CREATE INDEX...</code> statement, but it already exists, so it fails. Furthermore dropping this index with a simple <code>DROP KEY &lt;keyname&gt; ON &lt;table&gt; ....</code> cannot be done where a foreign key (FK) depends on having an index on those columns, as pointed out by <a href="https://issues.civicrm.org/jira/browse/CRM-20533?focusedCommentId=103855&amp;page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-103855" rel="noreferrer">Aidan</a></p>
  
  <p>Temporarily disabling FKs can be problematic. The way I got it up and running was as follows:</p>
  
  <ol>
  <li><p>Run the fixer, either from the status page or (for drupal users) with  </p>
  
  <pre><code>drush cvapi System.updateindexes
  </code></pre>
  
  <p>If it didn't work, read on.</p></li>
  <li><p>Inspect the latest file in <code>ConfigAndLog</code> to find the table where it died. In my case this looked like  </p>
  
  <pre><code>CREATE UNIQUE INDEX UI_case_contact_id ON civicrm_case_contact
  (case_id, contact_id) [nativecode=1061 ** Duplicate key name
  'UI_case_contact_id'
  </code></pre>
  
  <p>So it's the <code>UI_case_contact_id</code> key that's the problem here.</p></li>
  <li><p>Attempt to delete that key, if this works you can go back to step 1.</p>
  
  <pre><code>drush civicrm-sql-cli
  MariaDB [your_db]&gt; DROP INDEX UI_case_contact_id ON civicrm_case_contact;
  </code></pre></li>
  <li><p>If deleting the key fails due to a FK constraint, e.g. like this:  </p>
  
  <pre><code>ERROR 1553 (HY000): Cannot drop index 'UI_case_contact_id': needed
  in a foreign key constraint.
  </code></pre>
  
  <p>Then create the same key with a different name - copy the definition from the error message in (2), e.g.</p>
  
  <pre><code>CREATE UNIQUE INDEX my_temp_key ON civicrm_case_contact
  (case_id, contact_id);
  </code></pre>
  
  <p>You should now be able to go back to step 3 and successfully delete the original key since now the FK can depend on your new key called <code>my_temp_key</code>.</p></li>
  <li><p>Re-run the updater, hopefully it's ok  (on that table/key at least) now. If so you can then delete your <code>my_temp_key</code> with</p>
  
  <pre><code>DROP INDEX my_temp_key ON civicrm_case_contact;
  </code></pre>
  
  <p>Nb. Several of us have had this part fail. This means the update script has not created the index for some reason. If this happens I'd advise it would probably be best practise to reissue the original <code>CREATE INDEX</code> command from the log (step 2 above), then you should be able to drop your temporary key ok. This way you leave the schema exactly as CiviCRM expects it to be, which is definitely going to be helpful in the long term.</p></li>
  </ol>
  
  <p>I put this solution together standing on the observations shared by others credited above, just thought it might be useful for me to spell out a step by step process. :-)</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-12T07:04:54.107Z
Answers.Text: I think the command in Step 3 should be "DROP INDEX" instead of "DROP KEY"?  I needed the former with mysql, anyway. 
  
  Also, I was able to follow these instructions to rebuild the indexes with the updater, but get the error "Cannot drop index 'my_temp': needed in a foreign key constraint" when I try to remove my temporary index.  Probably doesn't hurt to leave it there, so I'm not too worried about it...
Answers.UserDisplayName: Norris
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-12T07:19:39.570Z
Answers.Text: Hi Norris, having done this a few times, I've experienced that same problem. I've updated my answer. Also thanks for spotting the key/index thing 
Answers.UserDisplayName: artfulrobot
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-25T18:07:27.943Z
Answers.Text: Many thanks. Steps 3-5 worked perfectly for me. Note that I did encounter the "common" errors, but the suggested recoveries worked just fine for me.
Answers.UserDisplayName: Karl Rudnick
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-26T06:42:40.440Z
Answers.Text: Interesting that I successfully ran this fix on my local xampp instance on my laptop. When I went live, my System Status page does not offer the Update Indices button, so I can't go about the same steps. I am running Wordpress 4.8 and updated Civi 4.7.16 to 4.6.22. The System Status function displays nothing on the upper part (where the Update Indices button should be). Any ideas how to restore that?
Answers.UserDisplayName: Karl Rudnick
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-26T07:24:18.627Z
Answers.Text: The System Status page not fully displaying is actually a separate issue requiring CiviCRM cache to be fully cleared and default paths reset after the upgrade. I usually only do that step when moving the site from one location/machine to another. See https://civicrm.stackexchange.com/questions/19365/missing-indices-error-followed-by-missing-system-status-page
Answers.UserDisplayName: Karl Rudnick
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-26T07:46:47.180Z
Answers.Text: You probably just need to clear the menu cache rather than reset paths (these two buttons are next to eachother), but if you're using the default paths there's no difference.
Answers.UserDisplayName: artfulrobot
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-28T17:48:34.507Z
Answers.Text: Check out [CiviCRM Chat](https://chat.civicrm.org/civicrm/channels/wordpress) for Fri, 28 July 2017; 10:00.  KCristiano provided a fix for the System Status Page issue that worked for me and others.
Answers.UserDisplayName: dvhirst
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-09-11T16:25:59.673Z
Answers.Text: The first part of Step 5 failed for me, because there are duplicates in my civicrm_case_contact table:
  DB Error: already exists
  Debug information:
  CREATE UNIQUE INDEX UI_case_contact_id ON civicrm_case_contact (case_id, contact_id) [nativecode=1062 ** Duplicate entry '183-8015' for key 'UI_case_contact_id']
  I have 28 duplicates, guess I'll need to sort those out manually.
Answers.UserDisplayName: davejenx
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-09-11T17:06:00.313Z
Answers.Text: Following on from my previous comment: once I manually removed the duplicates, it worked.
Answers.UserDisplayName: davejenx
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-10T12:17:45.937Z
Answers.LastActivityDate: 2017-08-17T07:08:38.687Z
Answers.LastEditDate: 2017-08-17T07:08:38.687Z
Answers.UserDisplayName: artfulrobot
Answers.Body: <p>The pop-up message should have a link "View details and manage alerts" that takes you to the System Status page.  That includes an <code>Update Indices</code> button (yes there is a typo in 4.7.19 but it doesn't affect the function).</p>
  
  <p><code>Update Indices</code> <em>should</em> create the missing indices which may take a while depending on which indices are missing and how much data you have in the affected tables, and then return you to the System Status page without that warning.</p>
  
  <p>However, there is a bug (<a href="https://issues.civicrm.org/jira/browse/CRM-20533" rel="noreferrer">https://issues.civicrm.org/jira/browse/CRM-20533</a>) that causes the index creation to fail when an index of the same name already exists.</p>
  
  <p>If that affects your system, the warning will persist until a future release when this is fixed.  If that bothers you, you can use the 'Hide' option.  </p>
  
  <p>Note that this is warning of a problem that has likely been in your system for a while: the problem is not new, but the check and the attempt to fix it is new in 4.7.19.</p>
  
  <p>Kainuk's answer shows how to list the missing indices on a Drupal system, or for any CMS you can use:</p>
  
  <p><code>cv ev 'print_r(CRM_Core_BAO_SchemaHandler::getMissingIndices())'</code></p>
  
  <p>and run the update with</p>
  
  <p><code>cv api System.updateindexes</code></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-05-08T11:29:39.830Z
Answers.LastActivityDate: 2017-05-08T12:36:44.253Z
Answers.LastEditDate: 2017-05-08T12:36:44.253Z
Answers.UserDisplayName: Aidan
Answers.Body: <p>After upgrading to 4.7.19 I found that indices cannot be created automatically; you can look at your CiviCRM error log to see which database statement is causing a fatal error - you might see problems such as a duplicate index.</p>
  
  <p>In my case I had to run these database statements before/after repairing indices, but it could be different on your site:</p>
  
  <pre><code>ALTER TABLE civicrm_prevnext_cache DROP INDEX index_all;
  ALTER TABLE civicrm_option_value DROP FOREIGN KEY `FK_civicrm_option_value_option_group_id`;
  ALTER TABLE civicrm_option_value DROP INDEX index_option_group_id_name;
  ALTER TABLE civicrm_entity_tag DROP INDEX UI_entity_id_entity_table_tag_id;
  </code></pre>
  
  <p>Then, click the index repair button, and finally, restore the constraint:</p>
  
  <pre><code>ALTER TABLE civicrm_option_value ADD CONSTRAINT `FK_civicrm_option_value_option_group_id` FOREIGN KEY (`option_group_id`) REFERENCES `civicrm_option_group` (`id`) ON DELETE CASCADE;
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-09-05T20:36:13.253Z
Answers.Text: Agreed, removed. For reference here's what you should not do:     
  UPDATE civicrm_uf_group SET name = CONCAT(name, '_', id) WHERE name NOT LIKE CONCAT('%', id);
Answers.UserDisplayName: Alan Dixon
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-11-19T15:49:21.770Z
Answers.Text: Thanks, this worked for me.  In my case, I only had to drop FK_civicrm_option_value_option_group_id, click the repair button and then add FK_civicrm_option_value_option_group_id back.  I was able to use phpMyAdmin to do this.  I'm using CiviCRM 4.7.27 and this still isn't fixed.
Answers.UserDisplayName: George Lozier
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-27T16:12:35.743Z
Answers.Text: Warning: Changing the machine name as you do above (UPDATE civicrm_uf_group SET name...) can have wide repercussions, and in fact rendered my CiviCRM inoperable after I enabled CiviVolunter (https://issues.civicrm.org/jira/browse/VOL-309).
Answers.UserDisplayName: BobS
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-05-09T23:02:54.737Z
Answers.LastActivityDate: 2017-09-05T20:35:44.353Z
Answers.LastEditDate: 2017-09-05T20:35:44.353Z
Answers.UserDisplayName: mfb
Answers.Body: <p>Had the same problem when I went from 4.7.14 to 4.7.20, same resolution as @mfb but with a different index.  Default location for the error logs is</p>
  
  <blockquote>
    <p>/sites/default/files/civicrm/ConfigAndLog  </p>
  </blockquote>
  
  <p>There I found an error adding the index UI_case_contact_id .  Coincidentally, this was the first index listed when running the drush ev command per @Kainuk above. Still had to fix via db.  For me it was:</p>
  
  <pre><code>ALTER TABLE civicrm_case_contact DROP FOREIGN KEY `FK_civicrm_case_contact_contact_id`;
  ALTER TABLE civicrm_case_contact DROP FOREIGN KEY `FK_civicrm_case_contact_case_id`;
  ALTER TABLE civicrm_case_contact DROP INDEX UI_case_contact_id;
  </code></pre>
  
  <p>Clicked on the button on the civicrm/a/#/status page, then ran these queries to restore the constraints</p>
  
  <pre><code>ALTER TABLE civicrm_case_contact ADD CONSTRAINT `FK_civicrm_case_contact_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE;
  ALTER TABLE civicrm_case_contact ADD CONSTRAINT `FK_civicrm_case_contact_case_id` FOREIGN KEY (`case_id`) REFERENCES `civicrm_case` (`id`) ON DELETE CASCADE;
  </code></pre>
  
  <p>BTW, even though drush wasn't needed to solve this problem, these sites were helpful getting it installed:
  <a href="https://www.drupal.org/node/2809987#comment-11684107" rel="nofollow noreferrer">https://www.drupal.org/node/2809987#comment-11684107</a>
  <a href="http://endlessgeek.com/2014/03/install-drush-cpanel-shared-hosting/" rel="nofollow noreferrer">http://endlessgeek.com/2014/03/install-drush-cpanel-shared-hosting/</a></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-28T18:02:19.910Z
Answers.Text: After checking for and finding the same issue in [civicrm.files]/ConfigAndLog, this worked for me after CiviCRM 4.7.20 --> 4.7.22.  I used phpMyAdmin to run the SQL queries, no issues. My config is LAMP running on AWS t2 micro with WordPress 4.8 as CMS.  Thanks, I now have a clean 4.7.22 install.
Answers.UserDisplayName: dvhirst
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-12T02:27:23.260Z
Answers.LastActivityDate: 2017-11-27T10:42:46.937Z
Answers.LastEditDate: 2017-11-27T10:42:46.937Z
Answers.UserDisplayName: Sophie Miron
Answers.Body: <p>Are you using Drupal and do you have access to <code>drush</code>? Then you can use the shell. To show the missing indexes do:</p>
  
  <pre><code> drush ev 'civicrm_initialize();print_r(CRM_Core_BAO_SchemaHandler::getMissingIndices())'
  </code></pre>
  
  <p>To create them use</p>
  
  <pre><code> drush cvapi System.updateindexes
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-05-07T10:01:32.357Z
Answers.LastActivityDate: 2017-05-07T10:01:32.357Z
Answers.UserDisplayName: Kainuk - CiviCooP
Answers.Body: <p>From the system check screen you should be able to click on a button to add them. Most likely this is because you are missing newly added indices </p>
  
  <p>e.g from 
  <a href="http://yoursite/civicrm/a/#/status" rel="nofollow noreferrer">http://yoursite/civicrm/a/#/status</a></p>
  
  <p>You should have an option to add the indices. Note that on a large site they may require an outage to add them as they may take a while</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-05-06T10:54:57.020Z
Answers.Text: Thanks for this answer. Should the updating of the indices happen in the background? When I click on the button, then a confirmation dialog, then a couple of seconds of whirling CiviCRM logo, then back to the System Status page where it still says that there are missing indices. Refreshing the page does not help.
Answers.UserDisplayName: Jern
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-05-15T01:12:52.973Z
Answers.Text: Had the same. Ran kainuk's offering from terminal. Spat out an array for first, then drush cvapi System.updateindexes
  Array
  (
      [error_code] => already exists
      [tip] => add debug=1 to your API call to have more info about the error
      [is_error] => 1
      [error_message] => DB Error: already exists
  )
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-05-06T05:50:41.723Z
Answers.LastActivityDate: 2017-05-06T05:50:41.723Z
Answers.UserDisplayName: user4619
Answers.Body: <p>I upgraded from 4.7.8 to 4.7.20. After seeing 'missing indices' status msg I saw that table civicrm_case_contact did indeed have foreign keys for both contact_id and case_id- that's about all it has, being a link table. The problem is in the function that builds the existing_sigs, required_sigs arrays for SchemaHandler:findMissingIndices comparing civicrm_case_contact::1::case_id::contact_id with civicrm_case_contact::0::contact_id
  Other tables listed as 'missing indices' included civicrm_uf_group:name but column <code>name</code> is not constrained to being unique; and civicrm_activity:status_id which is allowed to be null.
    This is a very useful function in theory, but maybe needs some work?  Thank you all</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-18T23:56:59.100Z
Answers.LastActivityDate: 2017-06-18T23:56:59.100Z
Answers.UserDisplayName: Lesley Evensen
Body: <p>After upgrading to 4.7.19, I'm now getting a warning:
  You have missing indices on some tables. This may cause poor performance.</p>
  
  <p>How can I find which indices are missing and then, how can I create them?</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-06-01T15:02:14.130Z
Comments.Text: I've thankfully found this thread, and I have Drush 7.40 installed and working well on the server. I have a Drupal 7 with Civi 4.7.19 installed and on the Warnings screen also have the indices warning. Clicking the button didn't work, and I followed [@Kainuk](https://civicrm.stackexchange.com/a/18506/3636)'s answer as well, but when I attempted to run:
  `drush cvapi System.updateindexes` I get the error: Array ( [error_code] => already exists [tip] => add debug=1 to your API call to have more info about the error [is_error] => 1 [error_message] => DB Error: already exists ) I have no idea how t
Comments.UserDisplayName: shayaknyc
Tags: <upgrade><database>
Title: How to find and/or fix missing indices?
ThreadText: # How to find and/or fix missing indices?
  
  (Asked by: username_0 on 2017-05-05)
  
  After upgrading to 4.7.19, I'm now getting a warning:
  You have missing indices on some tables. This may cause poor performance.
  
  How can I find which indices are missing and then, how can I create them?
  
  **Tags:** upgrade, database
  
  > **Comments:**
  > **username_1**: I've thankfully found this thread, and I have Drush 7.40 installed and working well on the server. I have a Drupal 7 with Civi 4.7.19 installed and on the Warnings screen also have the indices warning. Clicking the button didn't work, and I followed [@username_13](https://civicrm.stackexchange.com/a/18506/3636)'s answer as well, but when I attempted to run: `drush cvapi System.updateindexes` I get the error: Array ( [error_code] => already exists [tip] => add debug=1 to your API call to have more info about the error [is_error] => 1 [error_message] => DB Error: already exists ) I have no idea how t  
  
  ---
  
  ## 7 answers
  
  ### Answer by username_2 on 2017-07-10. Score: 15
  
  **Disclaimer, as with other answers, this is aimed at a fairly technical person who kinda understands what they're doing and has made lots of backups :-)**
  
  As [username_12 points out](https://civicrm.stackexchange.com/a/18985/35) you can find the exact problem looking at the latest log in the `ConfigAndLog` directory.
  
  For several of us the problem has been that there's a key **defined incorrectly** and the script's current response is to try a `CREATE INDEX...` statement, but it already exists, so it fails. Furthermore dropping this index with a simple `DROP KEY <keyname> ON <table> ....` cannot be done where a foreign key (FK) depends on having an index on those columns, as pointed out by [username_7](https://issues.civicrm.org/jira/browse/CRM-20533?focusedCommentId=103855&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-103855)
  
  Temporarily disabling FKs can be problematic. The way I got it up and running was as follows:
  
  1. Run the fixer, either from the status page or (for drupal users) with
  
     ```
     drush cvapi System.updateindexes
  
     ```
  
     If it didn't work, read on.
  2. Inspect the latest file in `ConfigAndLog` to find the table where it died. In my case this looked like
  
     ```
     CREATE UNIQUE INDEX UI_case_contact_id ON civicrm_case_contact
     (case_id, contact_id) [nativecode=1061 ** Duplicate key name
     'UI_case_contact_id'
  
     ```
  
     So it's the `UI_case_contact_id` key that's the problem here.
  3. Attempt to delete that key, if this works you can go back to step 1.
  
     ```
     drush civicrm-sql-cli
     MariaDB [your_db]> DROP INDEX UI_case_contact_id ON civicrm_case_contact;
  
     ```
  4. If deleting the key fails due to a FK constraint, e.g. like this:
  
     ```
     ERROR 1553 (HY000): Cannot drop index 'UI_case_contact_id': needed
     in a foreign key constraint.
  
     ```
  
     Then create the same key with a different name - copy the definition from the error message in (2), e.g.
  
     ```
     CREATE UNIQUE INDEX my_temp_key ON civicrm_case_contact
     (case_id, contact_id);
  
     ```
  
     You should now be able to go back to step 3 and successfully delete the original key since now the FK can depend on your new key called `my_temp_key`.
  5. Re-run the updater, hopefully it's ok (on that table/key at least) now. If so you can then delete your `my_temp_key` with
  
     ```
     DROP INDEX my_temp_key ON civicrm_case_contact;
  
     ```
  
     Nb. Several of us have had this part fail. This means the update script has not created the index for some reason. If this happens I'd advise it would probably be best practise to reissue the original `CREATE INDEX` command from the log (step 2 above), then you should be able to drop your temporary key ok. This way you leave the schema exactly as CiviCRM expects it to be, which is definitely going to be helpful in the long term.
  
  I put this solution together standing on the observations shared by others credited above, just thought it might be useful for me to spell out a step by step process. :-)
  
  > **Comments:**
  > **username_3**: I think the command in Step 3 should be "DROP INDEX" instead of "DROP KEY"?  I needed the former with mysql, anyway.   Also, I was able to follow these instructions to rebuild the indexes with the updater, but get the error "Cannot drop index 'my_temp': needed in a foreign key constraint" when I try to remove my temporary index.  Probably doesn't hurt to leave it there, so I'm not too worried about it...  
  > **username_2**: Hi username_3, having done this a few times, I've experienced that same problem. I've updated my answer. Also thanks for spotting the key/index thing   
  > **username_4**: Many thanks. Steps 3-5 worked perfectly for me. Note that I did encounter the "common" errors, but the suggested recoveries worked just fine for me.  
  > **username_4**: Interesting that I successfully ran this fix on my local xampp instance on my laptop. When I went live, my System Status page does not offer the Update Indices button, so I can't go about the same steps. I am running Wordpress 4.8 and updated Civi 4.7.16 to 4.6.22. The System Status function displays nothing on the upper part (where the Update Indices button should be). Any ideas how to restore that?  
  > **username_4**: The System Status page not fully displaying is actually a separate issue requiring CiviCRM cache to be fully cleared and default paths reset after the upgrade. I usually only do that step when moving the site from one location/machine to another. See https://civicrm.stackexchange.com/questions/19365/missing-indices-error-followed-by-missing-system-status-page  
  > **username_2**: You probably just need to clear the menu cache rather than reset paths (these two buttons are next to eachother), but if you're using the default paths there's no difference.  
  > **username_5**: Check out [CiviCRM Chat](https://chat.civicrm.org/civicrm/channels/wordpress) for Fri, 28 July 2017; 10:00.  KCristiano provided a fix for the System Status Page issue that worked for me and others.  
  > **username_6**: The first part of Step 5 failed for me, because there are duplicates in my civicrm_case_contact table: DB Error: already exists Debug information: CREATE UNIQUE INDEX UI_case_contact_id ON civicrm_case_contact (case_id, contact_id) [nativecode=1062 ** Duplicate entry '183-8015' for key 'UI_case_contact_id'] I have 28 duplicates, guess I'll need to sort those out manually.  
  > **username_6**: Following on from my previous comment: once I manually removed the duplicates, it worked.  
  
  ---
  
  ### Answer by username_7 on 2017-05-08. Score: 10
  
  The pop-up message should have a link "View details and manage alerts" that takes you to the System Status page. That includes an `Update Indices` button (yes there is a typo in 4.7.19 but it doesn't affect the function).
  
  `Update Indices` *should* create the missing indices which may take a while depending on which indices are missing and how much data you have in the affected tables, and then return you to the System Status page without that warning.
  
  However, there is a bug (<https://issues.civicrm.org/jira/browse/CRM-20533>) that causes the index creation to fail when an index of the same name already exists.
  
  If that affects your system, the warning will persist until a future release when this is fixed. If that bothers you, you can use the 'Hide' option.
  
  Note that this is warning of a problem that has likely been in your system for a while: the problem is not new, but the check and the attempt to fix it is new in 4.7.19.
  
  username_13's answer shows how to list the missing indices on a Drupal system, or for any CMS you can use:
  
  `cv ev 'print_r(CRM_Core_BAO_SchemaHandler::getMissingIndices())'`
  
  and run the update with
  
  `cv api System.updateindexes`
  
  
  ---
  
  ### Answer by username_8 on 2017-05-09. Score: 6
  
  After upgrading to 4.7.19 I found that indices cannot be created automatically; you can look at your CiviCRM error log to see which database statement is causing a fatal error - you might see problems such as a duplicate index.
  
  In my case I had to run these database statements before/after repairing indices, but it could be different on your site:
  
  ```
  ALTER TABLE civicrm_prevnext_cache DROP INDEX index_all;
  ALTER TABLE civicrm_option_value DROP FOREIGN KEY `FK_civicrm_option_value_option_group_id`;
  ALTER TABLE civicrm_option_value DROP INDEX index_option_group_id_name;
  ALTER TABLE civicrm_entity_tag DROP INDEX UI_entity_id_entity_table_tag_id;
  
  ```
  
  Then, click the index repair button, and finally, restore the constraint:
  
  ```
  ALTER TABLE civicrm_option_value ADD CONSTRAINT `FK_civicrm_option_value_option_group_id` FOREIGN KEY (`option_group_id`) REFERENCES `civicrm_option_group` (`id`) ON DELETE CASCADE;
  
  ```
  
  > **Comments:**
  > **username_11**: Warning: Changing the machine name as you do above (UPDATE civicrm_uf_group SET name...) can have wide repercussions, and in fact rendered my CiviCRM inoperable after I enabled CiviVolunter (https://issues.civicrm.org/jira/browse/VOL-309).  
  > **username_9**: Agreed, removed. For reference here's what you should not do:      UPDATE civicrm_uf_group SET name = CONCAT(name, '_', id) WHERE name NOT LIKE CONCAT('%', id);  
  > **username_10**: Thanks, this worked for me.  In my case, I only had to drop FK_civicrm_option_value_option_group_id, click the repair button and then add FK_civicrm_option_value_option_group_id back.  I was able to use phpMyAdmin to do this.  I'm using CiviCRM 4.7.27 and this still isn't fixed.  
  
  ---
  
  ### Answer by username_12 on 2017-06-12. Score: 5
  
  Had the same problem when I went from 4.7.14 to 4.7.20, same resolution as @username_8 but with a different index. Default location for the error logs is
  
  > /sites/default/files/civicrm/ConfigAndLog
  
  There I found an error adding the index UI\_case\_contact\_id . Coincidentally, this was the first index listed when running the drush ev command per @username_13 above. Still had to fix via db. For me it was:
  
  ```
  ALTER TABLE civicrm_case_contact DROP FOREIGN KEY `FK_civicrm_case_contact_contact_id`;
  ALTER TABLE civicrm_case_contact DROP FOREIGN KEY `FK_civicrm_case_contact_case_id`;
  ALTER TABLE civicrm_case_contact DROP INDEX UI_case_contact_id;
  
  ```
  
  Clicked on the button on the civicrm/a/#/status page, then ran these queries to restore the constraints
  
  ```
  ALTER TABLE civicrm_case_contact ADD CONSTRAINT `FK_civicrm_case_contact_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE;
  ALTER TABLE civicrm_case_contact ADD CONSTRAINT `FK_civicrm_case_contact_case_id` FOREIGN KEY (`case_id`) REFERENCES `civicrm_case` (`id`) ON DELETE CASCADE;
  
  ```
  
  BTW, even though drush wasn't needed to solve this problem, these sites were helpful getting it installed:
  <https://www.drupal.org/node/2809987#comment-11684107>
  <http://endlessgeek.com/2014/03/install-drush-cpanel-shared-hosting/>
  
  > **Comments:**
  > **username_5**: After checking for and finding the same issue in [civicrm.files]/ConfigAndLog, this worked for me after CiviCRM 4.7.20 --> 4.7.22.  I used phpMyAdmin to run the SQL queries, no issues. My config is LAMP running on AWS t2 micro with WordPress 4.8 as CMS.  Thanks, I now have a clean 4.7.22 install.  
  
  ---
  
  ### Answer by username_13 on 2017-05-07. Score: 4
  
  Are you using Drupal and do you have access to `drush`? Then you can use the shell. To show the missing indexes do:
  
  ```
   drush ev 'civicrm_initialize();print_r(CRM_Core_BAO_SchemaHandler::getMissingIndices())'
  
  ```
  
  To create them use
  
  ```
   drush cvapi System.updateindexes
  
  ```
  
  
  ---
  
  ### Answer by username_14 on 2017-05-06. Score: 1
  
  From the system check screen you should be able to click on a button to add them. Most likely this is because you are missing newly added indices
  
  e.g from
  <http://yoursite/civicrm/a/#/status>
  
  You should have an option to add the indices. Note that on a large site they may require an outage to add them as they may take a while
  
  > **Comments:**
  > **username_15**: Thanks for this answer. Should the updating of the indices happen in the background? When I click on the button, then a confirmation dialog, then a couple of seconds of whirling CiviCRM logo, then back to the System Status page where it still says that there are missing indices. Refreshing the page does not help.  
  > **username_16**: Had the same. Ran username_13's offering from terminal. Spat out an array for first, then drush cvapi System.updateindexes Array (     [error_code] => already exists     [tip] => add debug=1 to your API call to have more info about the error     [is_error] => 1     [error_message] => DB Error: already exists )  
  
  ---
  
  ### Answer by username_17 on 2017-06-18. Score: 0
  
  I upgraded from 4.7.8 to 4.7.20. After seeing 'missing indices' status msg I saw that table civicrm\_case\_contact did indeed have foreign keys for both contact\_id and case\_id- that's about all it has, being a link table. The problem is in the function that builds the existing\_sigs, required\_sigs arrays for SchemaHandler:findMissingIndices comparing civicrm\_case\_contact::1::case\_id::contact\_id with civicrm\_case\_contact::0::contact\_id
  Other tables listed as 'missing indices' included civicrm\_uf\_group:name but column `name` is not constrained to being unique; and civicrm\_activity:status\_id which is allowed to be null.
  This is a very useful function in theory, but maybe needs some work? Thank you all
  
  
  ---
  
  