I noticed that Drupal 8 (latest from git) has 13 duplicate MySQL indexes in default install. These are indexes on entity_id while entity_id is the first field in the primary key. Duplicate indexes are bad for writing (all indexes have to be maintained) but also for reading (optimizer has more indexes to consider).

It concerns these tables:

  • block_content__body
  • block_content_revision__body
  • comment__comment_body
  • node__body
  • node__comment
  • node__field_image
  • node__field_tags
  • node_revision__body
  • node_revision__comment
  • node_revision__field_image
  • node_revision__field_tags
  • user__roles
  • user__user_picture

I checked an older (alpha version) we run somewhere and it quickly adds up to 126 duplicate indexes, so this seems bad. Eg:

  • block_content__body
  • block_content__field_author
  • block_content__field_call_to_action
  • block_content__field_image
  • block_content__field_link
  • block_content__field_quote
  • block_content_revision__body
  • block_content_revision__field_author
  • block_content_revision__field_call_to_action
  • block_content_revision__field_image
  • block_content_revision__field_link
  • block_content_revision__field_quote
  • comment__comment_body
  • comment_revision__comment_body
  • field_deleted_data_a83bd7ef10
  • field_deleted_revision_a83bd7ef10
  • node__body
  • node__comment
  • node__field_background_color
  • node__field_blocks
  • node__field_blog_enable_comments
  • node__field_blog_post_banner
  • node__field_blog_post_body
  • node__field_blog_post_person
  • node__field_blog_post_tags
  • node__field_blog_post_time_read
  • node__field_blog_teaser_image
  • node__field_event_date
  • node__field_event_image
  • node__field_event_location
  • node__field_functie
  • node__field_image
  • node__field_inhoud
  • node__field_job_body
  • node__field_job_catchphrase
  • node__field_meta_description
  • node__field_page_banner
  • node__field_page_body
  • node__field_page_catchphrase
  • node__field_person_avatar
  • node__field_person_blog_url
  • node__field_person_body
  • node__field_person_email
  • node__field_person_function
  • node__field_person_hover
  • node__field_person_linkedin_username
  • node__field_person_phone
  • node__field_person_twitter_username
  • node__field_person_weight
  • node__field_service_body
  • node__field_service_catchphrase
  • node__field_service_icon
  • node__field_service_icon_svg
  • node__field_service_tags
  • node__field_service_weight
  • node__field_slide_body
  • node__field_slide_image
  • node__field_slide_link
  • node__field_slide_recent_articles
  • node__field_slideshow_slides
  • node__field_uc_banner
  • node__field_uc_logo
  • node__field_uc_reference
  • node__field_uc_service_tags
  • node__field_uc_services
  • node__field_uc_teaser_image
  • node__field_uc_technology
  • node__field_uc_weight
  • node_revision__body
  • node_revision__comment
  • node_revision__field_background_color
  • node_revision__field_blocks
  • node_revision__field_blog_enable_comments
  • node_revision__field_blog_post_banner
  • node_revision__field_blog_post_body
  • node_revision__field_blog_post_person
  • node_revision__field_blog_post_tags
  • node_revision__field_blog_post_time_read
  • node_revision__field_blog_teaser_image
  • node_revision__field_event_date
  • node_revision__field_event_image
  • node_revision__field_event_location
  • node_revision__field_functie
  • node_revision__field_image
  • node_revision__field_inhoud
  • node_revision__field_job_body
  • node_revision__field_job_catchphrase
  • node_revision__field_meta_description
  • node_revision__field_page_banner
  • node_revision__field_page_body
  • node_revision__field_page_catchphrase
  • node_revision__field_person_avatar
  • node_revision__field_person_blog_url
  • node_revision__field_person_body
  • node_revision__field_person_email
  • node_revision__field_person_function
  • node_revision__field_person_hover
  • node_revision__field_person_linkedin_username
  • node_revision__field_person_phone
  • node_revision__field_person_twitter_username
  • node_revision__field_person_weight
  • node_revision__field_service_body
  • node_revision__field_service_catchphrase
  • node_revision__field_service_icon
  • node_revision__field_service_icon_svg
  • node_revision__field_service_tags
  • node_revision__field_service_weight
  • node_revision__field_slide_body
  • node_revision__field_slide_image
  • node_revision__field_slide_link
  • node_revision__field_slide_recent_articles
  • node_revision__field_slideshow_slides
  • node_revision__field_uc_banner
  • node_revision__field_uc_logo
  • node_revision__field_uc_reference
  • node_revision__field_uc_service_tags
  • node_revision__field_uc_services
  • node_revision__field_uc_teaser_image
  • node_revision__field_uc_technology
  • node_revision__field_uc_weight
  • taxonomy_term__field_content
  • taxonomy_term__field_meta_description
  • taxonomy_term_revision__field_content
  • taxonomy_term_revision__field_meta_description
  • user__user_picture
  • user_revision__user_picture

Beta phase evaluation

Reference: https://www.drupal.org/core/beta-changes
Issue category Bug because the current database schema is not optimal
Issue priority Normal because this doesn't change any functionality
Unfrozen changes Unfrozen because it only changes the database schema for indexes, this doesn't change any API.
CommentFileSizeAuthor
#7 2428297-7.patch1.69 KBtvlooy
#4 2428297-3.patch673 bytestvlooy
#2 2428297-2.patch891 bytestvlooy
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

tvlooy’s picture

Issue summary: View changes
tvlooy’s picture

Status: Active » Needs review
FileSize
891 bytes

This patch fixes the behavior, but I'm not sure this is the right place to fix it.

Status: Needs review » Needs work

The last submitted patch, 2: 2428297-2.patch, failed testing.

tvlooy’s picture

FileSize
673 bytes
tvlooy’s picture

Status: Needs work » Needs review

This fixes it right where it should be fixed.

Status: Needs review » Needs work

The last submitted patch, 4: 2428297-3.patch, failed testing.

tvlooy’s picture

Status: Needs work » Needs review
FileSize
1.69 KB

Fix tests.

tvlooy’s picture

Issue summary: View changes
borisson_’s picture

Issue summary: View changes

Added beta evaluation.

Berdir’s picture

The duplication is because entity_id is already the first part in the primary key, and can be used to just query on that, the issue summary does not make that very clear, can you maybe update that.

I'm also wondering if a separate index for deleted is useful, that will in most cases always contain 0, and I don't think there is any performance-relevant query that would filter just on that? So we can possibly drop that too?

Same for langcode, what's the use case for querying *only* for the language code?

The primary query is probably loadFieldItems(), which does a query on entity_id, deleted, langcode and filters by delta. Does the order of the primary key work for that, or would it be worth switching langcode and delta there? The other one is probably entity query, specifically Tables::ensureFieldTable(), which joins on either entity_id or revision_id.

tvlooy’s picture

Issue summary: View changes
tvlooy’s picture

Issue summary: View changes
tvlooy’s picture

I updated the description a bit. Regarding the other indexes, someone added them (git blame says Alex Pott), maybe he can give us an insight about why and if the they can be removed.

Berdir’s picture

Title: Duplicate MySQL indexes » Duplicate index on entity_id column in dedicated field tables

alex is one of the core committers, you need to look at the commit message (the issue reference and those that got commit credit) to see who actually worked on it.

Also, whatever commit that was, it probably just moved things around.

Anyway, my two suggestions are less clear than the change here, which makes sense. Let's open a separate issue to discuss those two and then I'm happy to RTBC this.

JeroenT’s picture

Berdir’s picture

Status: Needs review » Reviewed & tested by the community

Thanks. I think this makes sense, as discussed, there is zero benefit from having this index, as the primary key solves this use case just fine.

JeroenT’s picture

alexpott’s picture

Status: Reviewed & tested by the community » Fixed

Nice find. Over indexing and unnecessary indexes have been a problem with Drupal for ages. 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 91ffcc1 and pushed to 8.0.x. Thanks!

  • alexpott committed 91ffcc1 on 8.0.x
    Issue #2428297 by tvlooy: Duplicate index on entity_id column in...

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.