Hi,
We’re running Directus as a headless CMS and we’ve been experiencing a recurring issue where the /relations endpoint becomes extremely slow after bulk table creation, causing our reverse proxy to return 504 errors and locking users out of the admin UI.
Stack
What happens
After creating several tables with FK constraints in a short period of time — either via POST /schema/apply (schema migrate from another environment) or directly from the Directus UI — theGET /relations call takes ~30s instead of the usual ~2s. HAProxy times out at 30s, Directus never populates its schema cache, and the admin UI is stuck in a loop of 504s.
In both cases, the issue resolved itself spontaneously after a few hours, with no intervention on our part.
What we found
Running EXPLAIN FORMAT=TREE on the underlying Directus query during the slow period showed MySQL estimating ~77,000 intermediate rows instead of the usual ~120, using a table scan + deduplication instead of an index lookup:
-> Table scan on <temporary> (cost=0.01..972.06 rows=77566)
-> Temporary table with deduplication (cost=51629.76..52601.81 rows=77566)
-> Nested loop inner join ...
Our hypothesis is that bulk DDL operations (many tables + FK constraints created in a short time) cause the InnoDB optimizer statistics for MySQL’s internal Data Dictionary tables (mysql.foreign_keys, mysql.foreign_key_column_usage) to become stale. The optimizer then picks a suboptimal plan for the information_schema.referential_constraints JOIN key_column_usage query. Spontaneous recovery is likely due to innodb_stats_auto_recalc kicking in asynchronously.
Note: the DBA confirmed that ANALYZE TABLE is not possible on system/DD tables, so we can’t manually trigger a stats refresh on the affected tables.
Question
Has anyone experienced this? Any known workaround or fix?
Thanks
2 Likes
Quick follow-up with additional verification
We ran a few more checks to validate the fix is safe.
Constraint name uniqueness: we queried both our test and staging databases:
SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, COUNT(*) AS cnt
FROM information_schema.referential_constraints
WHERE CONSTRAINT_SCHEMA = 'unahotels'
GROUP BY CONSTRAINT_SCHEMA, CONSTRAINT_NAME
HAVING cnt > 1;
Result: 0 rows on both environments. FK constraint names are all unique within the schema (754/754 on test, 765/765 on staging), which aligns with MySQL/InnoDB enforcing schema-level uniqueness for FK names. This means AND kcu.TABLE_NAME = rc.TABLE_NAME is logically redundant in MySQL, it cannot change the result set, but it does allow the optimizer to build a significantly better plan.
Confirmed timings on both environments:
|
Without fix |
With fix |
| Test DB |
~31s |
~0.29s |
| Staging DB |
~32s |
~0.24s |
EXPLAIN FORMAT=TREE on both environments with the fix shows a structurally different plan: the Union materialize node is now scoped per-table instead of iterating across all FK × table combinations.
So to summarize: the extra predicate is safe (redundant, not filtering anything out), and it consistently brings the query from ~30s down to ~0.25s across both environments.
Update - some additional data points
We did some deeper analysis and wanted to share in case it’s useful.
Timing decomposition on the underlying query:
referential_constraints alone: ~0.15s-
key_column_usage alone: ~0.09s
-
Running EXPLAIN ANALYZE on our test environment showed a Union materialize node running 263,146 loops instead of ~754 (our FK count). That’s approximately 754 FK × 349 tables — which might suggest the optimizer isn’t filtering by table early enough.
We tried adding AND kcu.TABLE_NAME = rc.TABLE_NAME to the JOIN in packages/schema/src/dialects/mysql.ts (foreignKeys() method)
JOIN information_schema.key_column_usage AS kcu
ON rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
AND kcu.TABLE_NAME = rc.TABLE_NAME -- added
With this change the same query ran in ~0.4s with 754 loops and returned identical results.
Does this analysis seem plausible? We’re wondering if there are edge cases where CONSTRAINT_NAME could be reused across different tables in the same schema. If so, this change would need more careful thought. Any feedback from someone more familiar with the codebase would be very welcome.