Unnecessary index on langcode and deleted column in dedicated field tables.

Thanks for creating this, I'd like some feedback from yched on this.

\Drupal\Core\Entity\Sql\SqlContentEntityStorage::loadFieldItems() does:

      $results = $this->database->select($table, 't')
        ->fields('t')
        ->condition($load_current ? 'entity_id' : 'revision_id', $ids, 'IN')
        ->condition('deleted', 0)
        ->condition('langcode', $langcodes, 'IN')
        ->orderBy('delta')
        ->execute();

This means that the regular "load current revision" operation is ok with the primary key. But a "load a previous revision" operation (which, IIRC, is very frequent with workbench_moderation), wouldn't.
This case would probably be better handled with a dedicated multi-column index on "revision_id, deleted, delta, langcode", rather than currently two separate indexes on 'revision_id' and 'deleted'.

Then, it's possible that at some earlier point the index on 'deleted' was used for purging of deleted values. But currently \Drupal\Core\Entity\Sql\SqlContentEntityStorage::readFieldItemsToPurge() runs
- a first query to determine entity ids (using the index on 'bundle'),
- and then a second query to collect field values on those entity ids (which then uses the primary index)
so it looks like the index on 'deleted' is in fact useless now.

I'm not fully sure about existing queries that might use the 'langcode' index - waving to @plach for that :-)

I'm not aware of use cases for queries having conditions on the langcode column alone. Given its very low cardinality, the langcode index seems pretty useless. Since Views joins dedicated tables based on entity_id, deleted and langcode columns, a composite index with these three values would seem more useful to me.

This case would probably be better handled with a dedicated multi-column index on "revision_id, deleted, delta, langcode", rather than currently two separate indexes on 'revision_id' and 'deleted'.

Makes sense to me too.

Btw, also the bundle index does not look very useful.

Removed useless indexes.

So,

- It's been established that 'revision_id' and 'deleted' are not needed anymore

- The index on 'bundles' is used by SqlContentEntityStorage::readFieldItemsToPurge() :

    (simplified a bit)  
    $table_name = $table_mapping->getDedicatedDataTableName($storage_definition, $is_deleted);
    $entity_query = $this->database->select($table_name, 't', array('fetch' => \PDO::FETCH_ASSOC));
    $entity_query
      ->distinct(TRUE)
      ->fields('t', array('entity_id'))
      ->condition('bundle', $field_definition->getTargetBundle())
      ->range(0, $batch_size);

so I don't think we can remove it. Back to NW for that.

- According to #3 and #4, we would need new composite indexes. Let's do that in a separate issue - opened #2471635: Missing indexes on dedicated field tables

Updated the patch to not remove 'bundle'

Thanks, looks good to me :-)

RTBC if green.

This issue is a normal bug fix, and doesn't include any disruptive changes, so it is allowed per https://www.drupal.org/core/beta-changes. Committed f364fc7 and pushed to 8.0.x. Thanks!