Studies Multiverse is a free study-abroad directory: 31,000 institutions in 224 countries, course lists, and tools for costs, post-study work rules and scholarships. It runs on Cloudflare Workers and D1 (SQLite), server-rendered, in 7 languages.

Last week the D1 dashboard showed 5 billion rows read in a day. Real visitors were a tiny fraction of that.

What was reading them

  • Meta's crawlers (meta-externalagent, meta-webindexer) were walking every directory listing in all 7 languages.
  • Applebot and others found the filtered URLs: every subject x city x page combination is a new address.
  • 50,000 course pages each ran a "same course at other places" query that read about 2,600 rows.

Three fixes

  1. Make the index usable. My list query had optional filters written as (?2 IS NULL OR e.kind = ?2). SQLite can't use an index through that, so every page counted the whole table. A fast path that builds the WHERE clause from only the filters in use dropped the main listing from ~30,000 rows to a few hundred.
  2. Add the missing indexes. Two composite indexes on the course table, (country_slug, search_key) and (country_slug, entity_id), turned the heaviest query into an index lookup.
  3. Don't serve crawlers what they can't index. Filtered lists are noindex and canonical to the plain list anyway, so crawlers now get a 301 to the plain list. Course detail pages (noindex for now) return a short 403 to crawlers. People see everything as before.

Takeaways

  • Check rows read, not request counts. A cheap-looking page can be the expensive one.
  • "Optional filter" SQL patterns quietly disable indexes. Run EXPLAIN QUERY PLAN.
  • If a URL is noindex, crawlers have no reason to fetch it at all.

The site: https://studiesmultiverse.com. Feedback welcome.