Page API find method performance with large custom field datasets

Answer first

For ProcessWire, $pages->find() performance on large page trees is driven by the SQL that the selector produces and by the cost of hydrating Page objects with custom field data. There is no built-in field projection in find(); when pages are returned, their fields are loaded as part of hydration.

Confirmed facts:

• Selectors are translated to SQL WHERE clauses against the pages table and the per-field data tables. Only fields referenced in the selector are used for filtering.
• ProcessWire creates database indexes on core columns such as pages.id, pages.parent_id, pages.template_id, and on the pages_id column of each field data table. The presence and usefulness of an index for a given filter depends on the field type and how the data is stored.
• find() returns a PageArray. Without a limit, the query can scan a large portion of the tree and then hydrate every matched Page, which increases memory and time linearly with result size.

Likely explanation, not confirmed without inspection:

• Filtering on unindexed or text-heavy field types, and especially using operators like contains on PageText / Textarea fields, forces full scans or expensive LIKE operations on field data tables.
• Returning many pages with many custom fields increases serialization and PHP object hydration cost even when the SQL is fast, because each Page loads its field values from separate tables.

Practical steps for this case

1. Filter on indexed, selective fields first. Prefer filters on template, parent, id, or field types that store scalar values in indexed columns, e.g. Integer, Page reference, Boolean. Defer or avoid filtering on large text fields in the selector.
2. Always bound result size. Use limit and process in chunks instead of unbounded finds.

   $pages->find("template=blog, limit=200");

   For deep scans, iterate with an increasing id anchor rather than large offsets.
3. Reduce hydration work. If you only need IDs or a few fields, fetch only those and avoid looping over full Page objects. Where possible, narrow the template set before applying custom field filters.
4. Verify database indexes. Inspect the schema for the field data tables used in selectors and confirm an index exists on pages_id and on the data column when the field type supports it. This is version and field-type dependent.
5. Enable query profiling for verification. With debug enabled, inspect the generated SQL and execution time for representative selectors. Measure response time and memory for a small page size with full fields versus a minimal selector.

Assumptions and uncertainty. ProcessWire field storage and indexing behavior varies by field type and version. The recommendations above assume standard MySQL/MariaDB storage and that selectors are used for filtering, not just for retrieval. Exact index names and the cost of text operators should be verified in your installation.

One diagnostic detail that changes the recommendation: which custom field types are used in the selector filters versus which are only needed after pages are retrieved. If the slowdown is caused by filtering on unindexed text fields, the strategy is index/field type change; if it is caused by hydrating large field payloads for many pages, the strategy is limit/chunking and reducing per-page field load.