Problem/Motivation

The domain_path module adds a domain_id base field to the path_alias entity. All queries in DomainAliasRepository filter by domain_id together with alias or path and langcode.

Core defines four composite indexes on the path_alias tables covering (alias|path, langcode, id, status). The entity system also creates a single-column index on domain_id (path_alias_field__domain_id__target_id). For domain-filtered queries, the database must either intersect two separate indexes or use one and filter the other column from the table — neither is optimal for a high-traffic code path like alias resolution.

By including domain_id in the composite indexes, a single index scan covers (alias|path, langcode, domain_id) together, eliminating the need for index merges or additional lookups.

Proposed resolution

Amend core's four existing indexes to insert domain_id after langcode, using an imperative approach (install/update/uninstall hooks):

Table Index name Amended columns
path_alias path_alias__alias_langcode_id_status alias, langcode, domain_id, id, status
path_alias path_alias__path_langcode_id_status path, langcode, domain_id, id, status
path_alias_revision path_alias_revision__alias_langcode_id_status alias, langcode, domain_id, id, status
path_alias_revision path_alias_revision__path_langcode_id_status path, langcode, domain_id, id, status

The leftmost prefix (alias|path, langcode) is preserved so core's own queries still benefit from the index.

Implementation in domain_path.install:

  • domain_path_install() — amends indexes on fresh install
  • domain_path_update_10007() — amends indexes for existing installs
  • domain_path_uninstall() — restores core's original indexes before the entity system drops the domain_id column

Remaining tasks

Review and commit.

Command icon Show commands

Start within a Git clone of the project using the version control instructions.

Or, if you do not have SSH keys set up on git.drupalcode.org:

Comments

mably created an issue. See original summary.

mably’s picture

Status: Active » Needs review
mably’s picture

Issue summary: View changes

  • mably committed e14c0aaf on 3.x
    task: #3577052 Amend path_alias indexes with domain_id for faster...

  • mably committed cb36ad60 on 2.x
    task: #3577052 Amend path_alias indexes with domain_id for faster...
mably’s picture

Status: Needs review » Fixed

Now that this issue is closed, review the contribution record.

As a contributor, attribute any organization that helped you, or if you volunteered your own time.

Maintainers, credit people who helped resolve this issue.

  • mably committed cd83a078 on 3.x
    docs: #3577052 Document database indexes in architecture page
    

Status: Fixed » Closed (fixed)

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