Skip to content
This repository was archived by the owner on Sep 18, 2026. It is now read-only.
This repository was archived by the owner on Sep 18, 2026. It is now read-only.

Schema: Add missing core indexes (type_client_id, room_type_date) #11

Closed as not planned

Description

@josephfusco

Our indexes don't match core's optimized query patterns.

Current indexes:
```sql
PRIMARY KEY (id),
UNIQUE KEY room_type (room(191), type), -- wrong, should be removed
KEY room_id (room(50), id),
KEY room_timestamp (room(50), timestamp)
```

Core indexes (PR #11256):
```sql
PRIMARY KEY (collaboration_id),
KEY type_client_id (type,client_id),
KEY room (room,collaboration_id),
KEY room_type_date (room,type,date_gmt),
KEY date_gmt (date_gmt)
```

Missing indexes:

  1. `KEY type_client_id (type,client_id)`

    • Used to filter out a client's own updates in poll responses
    • Core query: `WHERE type != 'awareness' AND client_id != %s`
  2. `KEY room_type_date (room,type,date_gmt)`

    • Covers polling AND cleanup queries
    • Composite index for `WHERE room = %s AND type = %s AND date_gmt < %s`

Changes needed:
```sql
DROP INDEX room_type,
DROP INDEX room_id,
DROP INDEX room_timestamp,
ADD KEY type_client_id (type,client_id),
ADD KEY room (room,collaboration_id),
ADD KEY room_type_date (room,type,date_gmt),
ADD KEY date_gmt (date_gmt)
```

Depends on: #9 (client_id column), #4 (date_gmt column)

Reference: WordPress/wordpress-develop#11256

Activity

  1. josephfusco commented on Aug 17, 2026

    @josephfusco
    CollaboratorAuthor

    Core needs these indexes because it stores both awareness and CRDT updates in wp_collaboration:

    • type_client_id: filters awareness by client
    • room_type_date: prunes old awareness states

    We delegate awareness to Presence API, so wp_collaboration only holds CRDT updates with no type/client_id filtering.

    Decision: Composite storage architecture (Trac #64696 comment:177, wordpress-develop PR #11256) validates this split.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions