I don't think there is a reason to have an index on the column "entity_type", when the primary key is already [entity_type, entity_id, deleted, delta, language].

Not sure about other database engines, but MySQL would just use the first part of the primary key if you had a query selecting rows by entity_type.

----------------

Also (though I'm not sure on these ones), some of the other indexes are also worth re-evaluating...

entity_id is probably never used without "entity_type", so that would be covered by the primary key as well.

revision_id is unique within each entity_type, just like entity_id, as far as I know. So it will commonly be used without "entity_id", but never without "entity_type". That means another multi-column index [entity_type, revision_id] might make more sense.

language could be used to globally delete all content of a language. But I don't know if that is ever done. Otherwise it would have no purpose either. Same goes for bundle and deleted.

Comments

ralf.strobel’s picture

ralf.strobel CreditAttribution: ralf.strobel commented
Title: Reduce over-indexing of "field_data_field_xxx" tables » Unnecessary index "entity_type" in field_data_field_xxx tables
Category: feature » task

All of this goes for the "field_revision_field_xxx" tables as well, of course.

ralf.strobel’s picture

ralf.strobel CreditAttribution: ralf.strobel commented
Title: Unnecessary index "entity_type" in field_data_field_xxx tables » Reduce over-indexing of "field_data_field_xxx" tables
Category: task » feature
Priority: Minor » Normal

Another note:

InnoDB appends the primary key fields to the entries of every secondary index. Since the primary key is currently quite long, encompassing almost all fields, this gives even more reason to reduce the number of secondary indexes.

I just checked on some of my field data tables and the index takes about 7 times more space than the actual data! And that's while most of the columns are for structure and don't even store the actual field data, so the real overhead is probably a lot bigger.

ralf.strobel’s picture

ralf.strobel CreditAttribution: ralf.strobel commented
Title: Unnecessary index "entity_type" in field_data_field_xxx tables » Over-indexing of "field_data_field_xxx" tables
Version: 7.7 » 8.x-dev
Category: task » bug

Alright, since nobody seemed to care about this when I posted it in D7, I'm now changing it to D8 and hoping for a backport to D7. I've checked that the issue is just as valid for D8.

Affected code is in field_sql_storage.module, _field_sql_storage_schema():

  'primary key' => array('entity_type', 'entity_id', 'deleted', 'delta', 'language'),
  'indexes' => array(
    'entity_type' => array('entity_type'),
    'bundle' => array('bundle'),
    'deleted' => array('deleted'),
    'entity_id' => array('entity_id'),
    'revision_id' => array('revision_id'),
    'language' => array('language'),
  ),

I suggest this instead:

 'primary key' => array('entity_type', 'entity_id', 'deleted', 'delta', 'language'),
  'indexes' => array(
    'revision' => array('entity_type', 'revision_id', 'deleted', 'delta', 'language'),
    'bundle' => array('bundle'), //required?
    'deleted' => array('deleted'), //required?
    'language' => array('language'), //required?
  ),
ralf.strobel’s picture

ralf.strobel CreditAttribution: ralf.strobel commented
Issue summary: View changes

Moved some parts of my comment #1 into the main issue.

Version: 8.0.x-dev » 8.1.x-dev

Drupal 8.0.6 was released on April 6 and is the final bugfix release for the Drupal 8.0.x series. Drupal 8.0.x will not receive any further development aside from security fixes. Drupal 8.1.0-rc1 is now available and sites should prepare to update to 8.1.0.

Bug reports should be targeted against the 8.1.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.2.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.1.x-dev » 8.2.x-dev

Drupal 8.1.9 was released on September 7 and is the final bugfix release for the Drupal 8.1.x series. Drupal 8.1.x will not receive any further development aside from security fixes. Drupal 8.2.0-rc1 is now available and sites should prepare to upgrade to 8.2.0.

Bug reports should be targeted against the 8.2.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.3.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.2.x-dev » 8.3.x-dev

Drupal 8.2.6 was released on February 1, 2017 and is the final full bugfix release for the Drupal 8.2.x series. Drupal 8.2.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.3.0 on April 5, 2017. (Drupal 8.3.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.3.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.4.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.3.x-dev » 8.4.x-dev

Drupal 8.3.6 was released on August 2, 2017 and is the final full bugfix release for the Drupal 8.3.x series. Drupal 8.3.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.4.0 on October 4, 2017. (Drupal 8.4.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.4.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.5.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.4.x-dev » 8.5.x-dev

Drupal 8.4.4 was released on January 3, 2018 and is the final full bugfix release for the Drupal 8.4.x series. Drupal 8.4.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.5.0 on March 7, 2018. (Drupal 8.5.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.5.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.6.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.5.x-dev » 8.6.x-dev

Drupal 8.5.6 was released on August 1, 2018 and is the final bugfix release for the Drupal 8.5.x series. Drupal 8.5.x will not receive any further development aside from security fixes. Sites should prepare to update to 8.6.0 on September 5, 2018. (Drupal 8.6.0-rc1 is available for testing.)

Bug reports should be targeted against the 8.6.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.7.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.6.x-dev » 8.8.x-dev

Drupal 8.6.x will not receive any further development aside from security fixes. Bug reports should be targeted against the 8.8.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.9.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

Version: 8.8.x-dev » 8.9.x-dev

Drupal 8.8.7 was released on June 3, 2020 and is the final full bugfix release for the Drupal 8.8.x series. Drupal 8.8.x will not receive any further development aside from security fixes. Sites should prepare to update to Drupal 8.9.0 or Drupal 9.0.0 for ongoing support.

Bug reports should be targeted against the 8.9.x-dev branch from now on, and new development or disruptive changes should be targeted against the 9.1.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

catch’s picture

catch
he/him
Primary language English
CreditAttribution: catch at Third and Grove commented
Title: Over-indexing of "field_data_field_xxx" tables » Over-indexing of "ENTITY_TYPE__field_xxx" tables
Issue summary: View changes
Issue tags: +Bug Smash Initiative

These have changed a bit, we currently have the following:

Primary key: entity_id, deleted, delta, langcode

bundle, bundle

revision_id, revision_id

field_{tags_target_id} field_{tags_target_id}

Or as a SHOW INDEXES:

MariaDB d8 > SHOW INDEXES FROM node__field_tags;
+------------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name             | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| node__field_tags |          0 | PRIMARY              |            1 | entity_id            | A         |         184 |     NULL | NULL   |      | BTREE      |         |               |
| node__field_tags |          0 | PRIMARY              |            2 | deleted              | A         |         184 |     NULL | NULL   |      | BTREE      |         |               |
| node__field_tags |          0 | PRIMARY              |            3 | delta                | A         |         184 |     NULL | NULL   |      | BTREE      |         |               |
| node__field_tags |          0 | PRIMARY              |            4 | langcode             | A         |         184 |     NULL | NULL   |      | BTREE      |         |               |
| node__field_tags |          1 | bundle               |            1 | bundle               | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| node__field_tags |          1 | revision_id          |            1 | revision_id          | A         |         184 |     NULL | NULL   |      | BTREE      |         |               |
| node__field_tags |          1 | field_tags_target_id |            1 | field_tags_target_id | A         |         184 |     NULL | NULL   |      | BTREE      |         |               |
+------------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.001 sec)

However, I really wonder whether we have many queries using the bundle or revision ID indexes, so it's possible those could be dropped.

This issue might be a duplicate of/duplicated by others elsewhere, but haven't found them to link to yet.

Version: 8.9.x-dev » 9.2.x-dev

Drupal 8 is end-of-life as of November 17, 2021. There will not be further changes made to Drupal 8. Bugfixes are now made to the 9.3.x and higher branches only. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.2.x-dev » 9.3.x-dev

Version: 9.3.x-dev » 9.4.x-dev

Drupal 9.3.15 was released on June 1st, 2022 and is the final full bugfix release for the Drupal 9.3.x series. Drupal 9.3.x will not receive any further development aside from security fixes. Drupal 9 bug reports should be targeted for the 9.4.x-dev branch from now on, and new development or disruptive changes should be targeted for the 9.5.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

pameeela’s picture

pameeela CreditAttribution: pameeela commented
Category: Bug report » Task
Issue tags: +DrupalSouth

Based on the OP and the comment by @catch, I think this is a task rather than a bug, as nothing is broken but could be improved.

Version: 9.4.x-dev » 9.5.x-dev

Drupal 9.4.9 was released on December 7, 2022 and is the final full bugfix release for the Drupal 9.4.x series. Drupal 9.4.x will not receive any further development aside from security fixes. Drupal 9 bug reports should be targeted for the 9.5.x-dev branch from now on, and new development or disruptive changes should be targeted for the 10.1.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.5.x-dev » 11.x-dev

Drupal core is moving towards using a “main” branch. As an interim step, a new 11.x branch has been opened, as Drupal.org infrastructure cannot currently fully support a branch named main. New developments and disruptive changes should now be targeted for the 11.x branch. For more information, see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.