Page deletion cost scales with the number of FieldtypePage fields on the site

[adrianbj]

Page deletion cost scales with the number of FieldtypePage fields on the site

Short description

FieldtypePage::hookPagesDelete() runs on every Pages::delete() and issues two DELETE statements for every FieldtypePage field in the installation, regardless of the page being deleted. The cost is a function of how many page reference fields the site has, not of the page. On installs with many such fields a single page delete becomes hundreds of statements, and on a remote database (RDS and similar) the round trip latency makes ordinary deletes take seconds.

https://github.com/processwire/processwire/blob/dev/wire/modules/Fieldtype/FieldtypePage/FieldtypePage.module#L84

foreach($this->wire()->fields->findByType('FieldtypePage') as $field) {
    $table = $database->escapeTable($field->table);
    $query = $database->prepare("DELETE FROM `$table` WHERE data=:page_id");
    ...
    $query = $database->prepare("DELETE FROM `$table` WHERE pages_id=:page_id");
    ...
}

Expected behaviour

Deleting a page costs roughly in proportion to that page: its own field data, its children, references to it.

Actual behaviour

It costs 2 x (number of FieldtypePage fields on the site) statements, minimum, however small the page.

Steps to reproduce

On an install with a large number of page reference fields:

1. SELECT COUNT(*) FROM fields WHERE type='FieldtypePage' (124 on the site below)
2. Note SHOW SESSION STATUS LIKE 'Com_delete'
3. Delete a single leaf page with no children via the API
4. Note it again

Measurements

Site with 124 FieldtypePage fields, ProcessWire 3.0.270, PHP 8.5.

Deleting one leaf page, no children:

	DELETE statements
total	264
from hookPagesDelete	248 (94%)
everything else	16

Where it becomes serious is a remote database. On AWS RDS, per statement round trip latency dominates and each page delete measured ~1.8 seconds. That applies to any page, not a special case.

It compounds with repeater cleanup, since each repeater item is a page:

• Deleting one RepeaterMatrix item that cascaded to 338 pages: ~167,000 DELETE statements, 11.7 seconds (local MySQL).
• Purging a backlog of 18,842 stale repeater "ready" items on the RDS site: ~9.5 hours, essentially all of it round trip latency.

Profiling that 11.7s delete with a WireProfilerInterface implementation attributed 8,338 ms inclusive to FieldtypePage::hookPagesDelete across 675 invocations.

Suggested fixes

1. Merge the two statements per table (simple, PR attached).

DELETE FROM `$table` WHERE data=:page_id OR pages_id=:page_id2

Both columns are indexed, so this resolves as an index_merge union rather than a scan:

EXPLAIN DELETE FROM field_x WHERE data=N OR pages_id=N
type: index_merge   key: data,PRIMARY   Extra: Using union(data,PRIMARY)

Measured 264 -> 140 statements on the same page. Behaviour verified unchanged against a page holding both inbound references and its own references.

2. Batch across a recursive delete (larger, not attempted).

When Pages::delete() removes a subtree, the hook currently fires per page. Collecting the ids and issuing one DELETE ... WHERE data IN (...) per table per tree rather than per page would turn the 338 page cascade above from ~167,000 statements into a few hundred. This is a more invasive change and I have not attempted it, but it is where the real win is for anyone deleting pages in bulk.

3. Skip tables that cannot match (probably not worth it).

The pages_id half can only match fields on the deleted page's own template. Restricting it that way would halve the work on its own, but it would leave orphaned rows behind if a page's template ever changed without those rows being cleaned, so it is likely unsafe without further guarantees.

Environment

• ProcessWire 3.0.270
• PHP 8.5, MySQL 8 (AWS RDS, and local MySQL for comparison)
• 124 FieldtypePage fields, ~56,000 pages

[adrianbj]

PR for the first suggested fix (merging the two statements per table): processwire/processwire#339