Consider batching the per-site options queries on very large Multisite networks #2027

Model_Custom_Fonts::get_files_used_by_other_sites() from #2012 (not merged yet) runs one options query per other site. It only runs on an admin delete, an edit that drops a file, or a font source adoption, and never on render. On very large networks, particularly with a separate database server, a chunked UNION ALL could cut that time. This is low priority below about 1,000 sites.

Measurements

One read of every site's gfpdf_settings, median of warm runs, on MariaDB 12.3 in local Docker with synthetic tables (a 3.9 KB settings row and 100 filler options per site):

Chunks of 1,000 were slower than chunks of 100 at 1,000 and 10,000 sites. Unserializing and looping over the results took 29 ms at 10,000 sites. At 10,000 sites the tables exceed the test server's table_open_cache of 2,000, so reopening tables is a large part of those times.

The database here had almost no network delay. With a separate database server, each per-site query also pays the round trip, which would be roughly 1 s at 1,000 sites and 10 s at 10,000 with a 1 ms delay. That part is calculated, not measured.

What it would need

• A missing table makes the whole UNION fail. If that reads as "no files in use", up to a chunk's worth of sites' files could be deleted, where today a missing table only skips that one site. Check $wpdb->last_error after each chunk and retry that chunk one site at a time.
• Older sites can have different table collations, which may also fail a UNION. This hasn't been tested, and the same retry would cover it.
• Model_Required_Fonts::each_site_font_key() also reads other sites one query at a time and does more work per site, so it should be looked at together with this lookup.