The indexes on the entity_usage tabel get huge

Here's a review of all the queries in the module to see how they might be affected.

Deletes

\Drupal\entity_usage\EntityUsage::registerUsage()

      $this->connection->delete($this->tableName)
        ->condition($target_id_column, $target_id)
        ->condition('target_type', $target_type)
        ->condition($source_id_column, $source_id)
        ->condition('source_type', $source_type)
        ->condition('source_langcode', $source_langcode)
        ->condition('source_vid', $source_vid)
        ->condition('method', $method)
        ->condition('field_name', $field_name)
        ->execute();

This can use the secondary indexes and I think the source and target combination will be enough to ensure that there are not too many rows to filter with the other conditions. This query might be a tiny bit slower but I do think it will be noticeable. There is a chance it will be quicker because the keys will be removed quicker.

\Drupal\entity_usage\EntityUsage::bulkDeleteTargets

    $query = $this->connection->delete($this->tableName)
      ->condition('target_type', $target_type);

Uses the secondary indexes.

\Drupal\entity_usage\EntityUsage::bulkDeleteSources

  public function bulkDeleteSources($source_type): void {
    $query = $this->connection->delete($this->tableName)
      ->condition('source_type', $source_type);

Uses the secondary indexes.

\Drupal\entity_usage\EntityUsage::deleteByField

    $query = $this->connection->delete($this->tableName)
      ->condition('source_type', $source_type)
      ->condition('field_name', $field_name);

Uses the secondary index - could be better but this is unchanged by this issue - also field deletion is not that common.

\Drupal\entity_usage\EntityUsage::deleteBySourceEntity()

    $query = $this->connection->delete($this->tableName)
      ->condition($source_id_column, $source_id)
      ->condition('source_type', $source_type);
    if ($source_langcode) {
      $query->condition('source_langcode', $source_langcode);
    }
    if ($source_vid) {
      $query->condition('source_vid', $source_vid);
    }

Uses the secondard index - could be better - we could consider adding source_langcode and source_vid to the secondary indexes.

\Drupal\entity_usage\EntityUsage::deleteByTargetEntity()

    $target_id_column = $this->isInt($target_id) ? 'target_id' : 'target_id_string';

    $query = $this->connection->delete($this->tableName)
      ->condition($target_id_column, $target_id)
      ->condition('target_type', $target_type);

Uses the secondary indexes.

Selects

\Drupal\entity_usage\EntityUsage::listSources()

    $target_id_column = $this->isInt($target_entity->id()) ? 'target_id' : 'target_id_string';
    $query = $this->connection->select($this->tableName, 'e')
      ->addTag('entity_usage_list_sources')
      ->addMetaData('entity_usage', [
        'target_entity' => $target_entity,
      ])
      ->fields('e', [
        'source_id',
        'source_id_string',
        'source_type',
        'source_langcode',
        'source_vid',
        'method',
        'field_name',
        'count',
      ])
      ->condition($target_id_column, $target_entity->id())
      ->condition('target_type', $target_entity->getEntityTypeId())
      ->condition('count', 0, '>')
      ->orderBy('source_type')
      ->orderBy('source_id', 'DESC')
      ->orderBy('source_vid', 'DESC')
      ->orderBy('source_langcode');

Uses the secondary index

\Drupal\entity_usage\EntityUsage::listTargets()

    $query = $this->connection->select($this->tableName, 'e')
      ->addTag('entity_usage_list_targets')
      ->addMetaData('entity_usage', [
        'source_entity' => $source_entity,
        'vid' => $vid,
      ])
      ->fields('e', [
        'target_id',
        'target_id_string',
        'target_type',
        'method',
        'field_name',
        'count',
      ])
      ->condition($source_id_column, $source_entity->id())
      ->condition('source_type', $source_entity->getEntityTypeId())
      ->condition('count', 0, '>')
      ->orderBy('target_id', 'DESC');

    if ($vid) {
      $query->condition('source_vid', $vid);
    }

Uses the secondary index. Could be better (but existing if we add the vid)

\Drupal\entity_usage\EntityUsage::listTargetEntitiesByFieldAndMethod()

    $source_id_column = $this->isInt($source_id) ? 'source_id' : 'source_id_string';
    $query = $this->connection->select($this->tableName, 'e')
      ->fields('e', [
        'target_id',
        'target_id_string',
        'target_type',
      ])
      ->condition($source_id_column, $source_id)
      ->condition('source_type', $source_entity_type_id)
      ->condition('source_vid', $source_vid ?: 0)
      ->condition('field_name', $field_name)
      ->condition('method', $method)
      ->condition('count', 0, '>')
      ->orderBy('target_id', 'DESC');

Uses the secondary index - but also could be improved. Not made worse here.

Merge

\Drupal\entity_usage\EntityUsage::registerUsage()

      $this->connection->merge($this->tableName)
        ->keys([
          $target_id_column => $target_id,
          'target_type' => $target_type,
          $source_id_column => $source_id,
          'source_type' => $source_type,
          'source_langcode' => $source_langcode,
          'source_vid' => $source_vid ?: 0,
          'method' => $method,
          'field_name' => $field_name,
        ])
        ->fields(['count' => $count])
        ->execute();

Will use the secondary indexes... this is the 1 query that might be a little worse. But again the smaller index size might also help and source_id_string was not in the primary index so...

There are no updates or upserts to the entity_usage table.

Another thing the primary key was doing was providing a uniqueness check. That said it was missing the source_id_string field so it was probably broken a bit in this case. And also the use of the merge in \Drupal\entity_usage\EntityUsage::registerUsage() ensures the uniqueness - and we don't support any other API of writing to this table and if you do you are responsible for that.

Therefore I think this is a good approach and will but the table on a diet!

Realised that this will require changes to bulk loading as we currently relying on the primary key to not introduce duplicates...

FWIW still testing whether or not this actually improves things. Running on a huge site with over 10 million rows in the entity usage table...

So... I've tried this out on a site which has a huge entity usage table (over 10,000,000 rows!)....

Before

+---------------+--------------+----------------------+------------+
| database_name | table_name   | index_name           | size_in_mb |
+---------------+--------------+----------------------+------------+
| drupal        | entity_usage | source_entity        |    1380.97 |
| drupal        | entity_usage | PRIMARY              |    1024.00 |
| drupal        | entity_usage | source_entity_string |     775.00 |
| drupal        | entity_usage | target_entity        |     765.00 |
| drupal        | entity_usage | target_entity_string |     765.00 |
+---------------+--------------+----------------------+------------+

Total: 4709.97

After

+---------------+--------------+----------------------+------------+
| database_name | table_name   | index_name           | size_in_mb |
+---------------+--------------+----------------------+------------+
| drupal        | entity_usage | PRIMARY              |    1069.00 |
| drupal        | entity_usage | source_entity        |     456.00 |
| drupal        | entity_usage | target_entity        |     415.00 |
| drupal        | entity_usage | target_entity_string |     230.92 |
| drupal        | entity_usage | source_entity_string |     194.80 |
+---------------+--------------+----------------------+------------+

Total: 2365.72

So this is super interesting... the primary index is basically the same size - I believe the PRIMARY index is just the same as the table data. But as we can see the secondary indexes are way way smaller. So this is definitely worth doing.

So this is a saving of nearly 50%! Definitely seems worth pursuing especially as we need to add another index for #3547273: Deleting paragraphs causes the entity usage tab to display confusing information

This issue found an obscure postrgres bug - see #3561800: Using \Drupal\Core\Database\Query\Insert::from() on postgres on a table with a serial field can result in duplicate key error... implemented slightly different way of inserting from the bulk table to the entity_usage table to workaround this but also it might be better anyway as inserting serial IDs into the db is not thread safe...

Moving to the new branch.

@scott_euser I don't think we can add the batch recreate to the update - if you put your site into maintenance mode as you should that'd take your site down unnecessarily for hours. And yes this will definitely only go in the next major version of the module.

@scott_euser @vitaliyb98 thank you very very much for testing this and confirming my research. You both definitely will get credit.

I'm going to commit this to the 5.x dev branch as this is a key issue for the 5.0 release that will hopefully happen once we've solved #3547273: Deleting paragraphs causes the entity usage tab to display confusing information

One option is we could run the batch update by default but use a setting to allow sites to override if they want. I guess I'm still concerned that this will result in production deployments catching people out and suddenly taking ages. Hard problem. I think on balance the current approach is best but I'm open to ideas!

@berdir it doesn't but I was planning on making this change with #3547273: Deleting paragraphs causes the entity usage tab to display confusing information following close behind because both would have required re-indexing. But as it turns out it don;t think that one is going to result in a schema change so this is the single that I plan for 5.x before 5.0.0 that requires re-indexing.

I've merged this into 5.x