What a directory page costs (and how to measure it)

systems/directory-query-cost.md · System · nbmn, directory, performance, mariadb, cache, single-flight, ops, lesson

status: stable · written by claude-opus-5-5 on 2026-10-26 · stale after 2027-04-01

Where the time goes when a NearByMeNow directory page misses its cache, the floor no clause can beat, why page and filter variety defeated the cache, and how to time a statement on the live database without guessing.

What a directory page costs

A NearByMeNow directory page (/niche/restaurants, /state/pa/, a city) is two statements on a miss: a count and a page of ids, then one cheap primary-key fetch of the 24 wide rows. Both statements walk every listed business (about 9,400 in Oct 2026): the niche test is an OR across primary_niche and the Google types JSON, and no index serves an OR like that. Measured server-side at load ~11:

piece	cost
the walk itself, no niche test (the floor)	~90 ms
one JSON_EXTRACT per row on top of it	~0 ms extra
the photo-first ORDER BY over every listed business	~1.3 s
the same sort for restaurants (1,775 rows)	~0.3 s

So the walk is not the problem. What you evaluate per row is.

The two costs that were removed

1. The niche clause repeated itself (v2.2.684). It ran JSON_EXTRACT(types,'$[0]') once

per mapped Google type and JSON_CONTAINS once per type: ~60 JSON parses per business on restaurants. One IN (...) and one REGEXP '(?-i)"(a|b|c)"' ask the same question (count 849 to 216 ms, ids 1,216 to 546 ms). The regexp is exact only because types is a flat array of [a-z0-9_] strings: a quoted token can only be a whole element. If that ever changes, the equivalence has to be re-proven, not assumed.

2. Every page re-sorted the same set (v2.2.685). The page cache is keyed on the

filters, page included, so page 41 and page 42 of one listing were two full sorts, and ?open=1 (a post-filter on the same statement) was a third. The count and the first 2,400 sorted ids are now cached per statement; a page is a slice.

Why the cache kept missing

The cache table held 158,078 directory entries (942 MB) on Oct 26, and 200+ of them were restaurants page 41 alone: subcategory × price × rating × sort × open combinations that crawlers walk. Single flight (GET_LOCK) stops a stampede on one key; it does nothing about a million distinct keys. Two levers, in order of power:

• Refuse the combinations nobody needs before any work (v2.2.682's crawler block for

robots-disallowed URLs). That is where most of the variety died.

• Cache below the page on what actually varies the work (the statement), so the

variety that remains shares one computation.

Gotcha: a cache key built from SQL text

nbmnNicheMatchClause() numbers its placeholders from a per-request counter (:ntv1_0 if it runs first, :ntv2_0 after the subcategory chips ran). Hashing the SQL text plus params stored the identical restaurants set under two keys a second apart. Key on the statement with its values written in (strtr over the placeholders), never on its text. Any helper that names placeholders with a counter has this property.

How to measure, without fooling yourself

• Time on the server, not over HTTP. dz query round-trips cost 0.25 to 0.6 s and

vary more than the statements do. This times the statement inside MariaDB: SELECT x.n, TIMESTAMPDIFF(MICROSECOND, NOW(6), SYSDATE(6))/1000 AS ms FROM (SELECT COUNT(*) n FROM (<the query>) z) x. NOW(6) is the statement's start, SYSDATE(6) the moment the row is produced. Put the derived table first, or the subquery runs after the clock read and reports ~1 ms.

• dz query returns at most 200 rows while reporting the true count in rows.

Page with LIMIT 200 OFFSET n before trusting any "all of X" result. (A first sweep here checked 200 of 263 mapped types and looked complete.)

• Validate a rewrite against the old answer over every row, in one query:

SUM(COALESCE(old,0) <> COALESCE(new,0)). Generate both clauses with the real PHP (stub the DB-backed maps from live rows) rather than hand-copying them.

• Check whether the database is the bottleneck at all before tuning it. On Oct 26 the

4 GB buffer pool held everything (0.1 disk reads a second) and 1 to 2 statements ran at a time, while load sat at ~10: the load was PHP requests, ~60% of them one crawler. SHOW GLOBAL STATUS sampled 30 s apart gives the rates; dz sys cannot see processes.

Related: NearByMeNow, the directory framework, and the 521 root cause (a network-wide 521 is fail2ban, a different layer: check it before touching PHP). The Sept 23 outage that introduced single flight and the Oct 5 crawler incident are in memory as project_nbmn_outage_sept_2026 and project_perf_incident_oct_2026.