Contrib.social

Block Content database index for reusable column

Open on Drupal.org →
Created on 13 January 2022, almost 3 years ago
Updated 3 March 2024, 10 months ago

Problem/Motivation

Currently, there is no database index for the 'reusable' column, leading to very slow queries on sites with large amounts of inline_blocks. My site has ~250,000 inline blocks created via an import of old Paragraphs content into Layout Builder.

On a full cache clear, my database server now get's stuck on this query for several minutes:

SELECT base_table.revision_id AS revision_id, base_table.id AS id
FROM
block_content base_table
INNER JOIN block_content_field_data block_content_field_data ON block_content_field_data.id = base_table.id
WHERE (block_content_field_data.reusable IN ('1')) AND (block_content_field_data.default_langcode IN ('1'));

Query Time: (gave up after waiting 400 seconds)

After adding the index: 

CREATE INDEX block_content_field_data_reusable USING BTREE ON block_content_field_data (reusable);

Query Time: 281ms

I believe this query is generated by core/modules/block_content/src/Plugin/Derivative/BlockContent.php

  /**
   * {@inheritdoc}
   */
  public function getDerivativeDefinitions($base_plugin_definition) {
    $block_contents = $this->blockContentStorage->loadByProperties(['reusable' => TRUE]);
    ...
  }

Steps to reproduce

  1. Create a large amount of inline_blocks which are not re-usable.
  2. Rebuild cache.

Proposed resolution

Add a schema update to add the index.

Remaining tasks

  • Review by someone who knows what they are doing
🐛 Bug report
Status

Fixed

Version

11.0 🔥

Component
Block content  →

Last updated 20 days ago

Created by

🇬🇧United Kingdom mattjones86 🇬🇧 GMT+0

Live updates comments and jobs are added and updated live.
  • Blocks-Layouts

    Blocks and Layouts Initiative. See the #2811175 Add layouts to Drupal issue.

  • Performance

    It affects performance. It is often combined with the Needs profiling tag.

Sign in to follow issues

Merge Requests

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

contrib.social Blog FAQ Discussions
Production build 0.71.5 2024