When wp_postmeta becomes the bottleneck.

WordPress can be an excellent home for a complex directory. That does not mean every directory query should be expressed as a growing stack of post-meta joins.

Picture a search for active teachers who teach piano, offer in-person lessons and fall within a chosen area. Add membership-dependent visibility, several locations per teacher and stable pagination. The challenge is no longer fetching a few custom fields. It is selecting a precise set of entities through several relationships.

Our work on the Australian Music Teachers Register shows why discovery and membership deserve deliberate engineering. This article develops a general search architecture for that class of problem. The illustrative schema and queries below are not a claim that AMTR uses this exact implementation, and no synthetic benchmark is presented as a client result.

Inspect the query you actually execute

Start with the SQL emitted for a representative request, not just the PHP arguments passed to WP_Query. Record the filters, result count, database version, dataset size and cache state. Preserve a difficult search with few matches as well as an easy search with many.

Read the execution plan. Which index is selected? How many rows are examined? Where do joins multiply intermediate results? Is the database sorting a large candidate set before returning a small page? A query that looks compact in PHP can still ask the database to perform an expensive amount of work.

On MySQL versions supporting it, EXPLAIN ANALYZE executes the query and reports observed execution details. That makes it different from a plan-only inspection: use bounded queries in a suitable test environment. MariaDB’s analysis syntax and output differ; match the command to the engine you actually run. MySQL documents these distinctions.

Separate editorial storage from search access

Custom fields are useful for editorial flexibility. A search workload may benefit from a separate, typed representation designed for its actual predicates. Think of that representation as a rebuildable index owned by your application, with clearly defined authoritative source records.

One table might represent searchable teachers. Another might represent the many-to-many relationship between teachers and subjects. Locations may deserve their own table because one teacher can work in several places. The appropriate model follows cardinality and query patterns; one enormous row is not automatically an improvement over post metadata.

WordPress supports plugin-owned database tables, including schema versioning and upgrade routines. That support is permission to choose an appropriate model, not a reason to replace ordinary metadata everywhere. A modest site with simple filters may be better served by a carefully written query and no additional storage system.

Design indexes around predicates and ordering

The following teaching example uses a flat teacher projection plus a relationship table. Replace wp_ with the installation’s actual prefix. These statements describe the relevant columns and indexes; a production migration must also specify the engine, character set, lifecycle and schema version.

CREATE TABLE wp_teacher_search (
    teacher_id BIGINT UNSIGNED NOT NULL,
    region_id BIGINT UNSIGNED NOT NULL,
    is_visible TINYINT UNSIGNED NOT NULL DEFAULT 0,
    sort_name VARCHAR(160) NOT NULL,
    source_version BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (teacher_id),
    KEY visible_region_name (is_visible, region_id, sort_name, teacher_id)
);

CREATE TABLE wp_teacher_subject (
    teacher_id BIGINT UNSIGNED NOT NULL,
    subject_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (teacher_id, subject_id),
    KEY subject_teacher (subject_id, teacher_id)
);

The composite index expresses one access pattern: visibility and region followed by a stable name order. It is not a universal index for every possible filter combination. Indexes also increase write work and storage. Inspect representative plans before adding the next one. The ordering of columns matters to which prefixes the database can use efficiently. MySQL’s composite-index documentation is useful background.

A relationship test is not always a join

If the question is whether a teacher has a matching subject, an existence predicate states that intention directly. It also avoids duplicating an outer teacher row simply because several matching relationship rows exist. The optimiser’s actual strategy still needs inspection; rewriting a join as EXISTS is not a universal speed switch.

SELECT t.teacher_id, t.sort_name
FROM wp_teacher_search AS t
WHERE t.is_visible = 1
  AND t.region_id = 7
  AND EXISTS (
      SELECT 1
      FROM wp_teacher_subject AS s
      WHERE s.teacher_id = t.teacher_id
        AND s.subject_id = 12
  )
  AND (
      t.sort_name > 'Morgan'
      OR (t.sort_name = 'Morgan' AND t.teacher_id > 481)
  )
ORDER BY t.sort_name, t.teacher_id
LIMIT 21;

The values are illustrative. In application code, bind visitor-supplied values with $wpdb->prepare(); allowlist identifiers and sorting modes rather than interpolating them. Fetching one extra record answers whether another page exists without requiring an exact total on every request.

This is keyset pagination: the next page begins after a particular ordered pair. It avoids increasingly deep offsets, but it changes the product contract. Arbitrary “jump to page 47” navigation is no longer free. Updates to names can move records between requests, so decide whether live results or a stable snapshot is appropriate. Specify a consistent collation and retain the ID as a tie-breaker.

Geographic search needs its own candidate strategy

For radius searches, a coarse geographic filter can reduce the candidates before calculating exact distances. That might be a bounding box followed by a distance calculation, or an appropriate spatial-index strategy. The choice depends on the database, data distribution and required geographic precision.

Do not treat latitude and longitude as ordinary text fields and assume numeric casts will preserve useful index access. Also account for locations near the antimeridian, multiple teaching locations and the difference between straight-line distance and travel distance. A fast query that drops valid teachers near a boundary is still wrong.

The hard part is keeping the projection correct

A second representation introduces synchronisation work. Define which changes invalidate it: profile edits, subject assignments, location updates, publication status, membership expiry and deletion. Not every change arrives through the same admin screen.

Make rebuild operations repeatable. Record a source version or equivalent concurrency token so a delayed job cannot overwrite a newer projection. Persist related authoritative changes and their pending projection work durably; a hook firing in memory is not evidence that a job survived a process crash.

When rebuilding at scale, build into a separate generation, compare counts and sampled records, then switch readers deliberately. Preserve changes made during the backfill through a change log or catch-up pass. An atomic table rename alone does not solve edits that occurred while the new table was being populated.

Search visibility and security also need different guarantees. A briefly stale display name may be acceptable. A revoked entitlement remaining usable is not. Recheck access against authoritative policy before protected actions, even when the search projection is allowed to lag.

A measured example from AMTR

Our AMTR performance case study records two approximate V7 development comparisons after targeted indexing: a geographic candidate lookup for Moss Vale within a 40 km radius went from 25.3 ms to 1.2 ms; a Suburb + Teaching lookup across 77 relationships went from 101 ms to 2 ms.

These are elapsed times for specific database operations from the historical development decision log. They are not production percentiles, whole-page load times or a controlled benchmark of the illustrative schema above. The published record does not establish a full hardware, cache-state and repeated-run protocol, so we use it as a scoped engineering observation, not a transferable speed guarantee. Exact radius filtering still follows geographic candidate selection.

Prove both speed and equivalence

Run old and new queries against the same fixture dataset and compare result IDs, order and pagination boundaries. Include teachers with no subjects, duplicate locations, expired access, identical names and recently deleted profiles. Then compare timings under cold and warm conditions and realistic concurrency.

A useful result is not simply “the new SQL is faster”. It is that the search still returns the right people, the projection can recover from missed work, and the measured improvement justifies the extra system you now operate. That is the threshold at which a custom search model earns its place.

Code examples illustrate the architecture and identify application-specific interfaces. Adapt them to your environment and test the failure paths before deployment. Sources are linked alongside the relevant discussion.