I still see 2.0 beta 8 being slower than 1.8 and I have all possible optimizations and caches applied...
I used Claude Code to try to profile it and analyze the performance and asked it to prepare a report. It's an AI after all, maybe it's right, maybe it's wrong but I hope someone will take a look, @IanM ? Here's the report:
Performance Issue: Discussion list TTFB ~1.5s on Flarum 2.0 beta 8 — caused by mentions and likes tag-permission subqueries
Environment
- Flarum 2.0 beta 8
- PHP 8.4.18 (FPM), OPcache + JIT enabled
- MySQL 8.4.8,
innodb_buffer_pool_size = 512M (entire DB fits in memory)
- Redis for cache, sessions, and queue (via fof/redis)
- 32 extensions enabled, 6 restricted tags
- ~2,200 discussions, ~54,000 posts, ~900 users
- 2-core VPS, 4GB RAM, CPU 98% idle
Symptoms
The discussion list page (/forum/ and /forum/t/{tag}) takes ~1.5s TTFB, while other pages are fast:
| Page | TTFB |
|---|
| Discussion list | ~1.5s |
| Tag page | ~1.5s |
| Single discussion | ~0.3s |
| User profile | ~0.3s |
| Admin dashboard | ~0.1s |
| API (5 discussions) | ~0.6s |
For comparison, the same forum data on Flarum 1.8 (SiteGround shared hosting) loads the discussion list in ~0.4s.
Root Cause Analysis
I captured the MySQL general query log for a single discussion list page load (guest user, 20 discussions):
- 287 queries executed in 1.52 seconds
- 150 queries (52%) hit the
tags table — tag permission checking
- 94 queries hit
groups — N+1 user group fetches
- 89 individual
group_user lookups (one per user on the page)
- 41 individual discussion fetches (N+1)
Extension-by-extension benchmarking
I isolated each extension's impact by disabling them one at a time:
| Configuration | TTFB | Change |
|---|
| All 32 extensions (baseline) | ~1.55s | — |
Without flarum/mentions | ~0.74s | -52% |
Without flarum/likes | ~0.94s | -39% |
Without fof/upload | ~1.54s | negligible |
| Without all 3 | ~0.95s | -38% |
The flarum/mentions query
The mentioned_by_count query is the single biggest performance issue. For each page load, it runs a correlated subquery across 40 post IDs. Each subquery includes ~10 levels of nesting to check:
- Tag-based permissions (
perm_tags.is_restricted checks)
- Parent tag permissions
- Discussion visibility (private, approved, hidden)
- Post visibility (private, approved, hidden)
- Each level duplicated for each permission dimension
The query is over 100 lines of SQL. A simplified structure:
SELECT id, (
SELECT count(*) FROM posts
INNER JOIN post_mentions_post ON ...
WHERE flarum_reserved_1.id = post_mentions_post.mentions_post_id
AND EXISTS (
SELECT 1 FROM discussions WHERE discussions.id = posts.discussion_id
AND (discussions.id NOT IN (
SELECT discussion_id FROM discussion_tag WHERE tag_id NOT IN (
SELECT tags.id FROM tags WHERE (
tags.id IN (SELECT perm_tags.id FROM tags AS perm_tags WHERE ...)
AND (tags.parent_id IN (SELECT perm_tags.id FROM tags AS perm_tags WHERE ...)
OR tags.parent_id IS NULL)
)
)
))
AND (discussions.is_private = 0 OR (...))
AND (discussions.hidden_at IS NULL OR (...))
AND (discussions.comment_count > 0 OR (...))
)
AND (posts.is_private = 0 OR (...))
AND (posts.hidden_at IS NULL OR (...))
) AS mentioned_by_count
FROM posts AS flarum_reserved_1
WHERE flarum_reserved_1.id IN (/* 40 post IDs */)
The same nested perm_tags pattern repeats ~8 times within a single execution of this query.
The flarum/likes query
Similarly, the likes extension adds likes_count and liked-by user queries with the same tag-permission nesting pattern, contributing ~0.6s.
The Underlying Issue
The Flarum 2.0 tag-permission system embeds the full permission check as nested SQL subqueries into every visibility-scoped query. This means:
- Each permission check re-queries the
tags table with the perm_tags subquery pattern
- These checks are not cached or materialized — they run fresh in every query
- Extensions like
flarum/mentions and flarum/likes that add aggregate counts (mentioned_by_count, likes_count) inherit the full permission scope, multiplying the cost
- With 6 restricted tags, the subqueries check against tag IDs on every nesting level
On Flarum 1.x, the equivalent page load for the same dataset takes ~0.4s — the permission model was presumably simpler or more efficient.
Suggestion
The tag-permission scope could potentially be:
- Computed once per request and passed as a simple ID list (e.g.,
WHERE tag_id IN (1,3,7,10,...)) rather than re-running the nested subquery pattern hundreds of times
- Cached in Redis for the guest user scope (most common) since tag permissions rarely change
- Optimized in the mentions/likes extensions to use a JOIN-based approach rather than correlated subqueries with full permission re-evaluation
Steps to Reproduce
- Install Flarum 2.0 beta 8 with flarum/tags, flarum/mentions, flarum/likes
- Import a database with ~2000+ discussions and 5+ restricted tags
- Load the discussion list as a guest
- Measure TTFB — expected ~1.5s vs ~0.3s for a single discussion page
