Database Query Time

Help
1

Summary

When creating a Many To Any relation you can select the type of the relation field string(default), integer.

Basic Example

No response

Motivation

When you create a many to any relation field the relation(item) field is always a string. When you run the query it needs to CAST the target tables id to CHAR(255). Which is a slow database operation. We have a query with m2a from 1 collection to 3 other collections. The query takes 6 seconds to complete. We changed the type in the relations table to integer and it took 100-200ms

Detailed Design

Somewhere in the schema we need to define the type of the relation field to determine when to use CAST or not.

Requirements List

Must Have:

  • Must be able to select database string or integer type for new m2a relations

Drawbacks

  • When u use the integer type for relation you can’t add all the collections as m2a only collections with integer as ID.

Alternatives

The impact would be slow database performance about a 10x improvement.

Adoption Strategy

You can leave the type as a string.

Unresolved Questions

No response

1 Like
2

Thanks for the write up here!

Without knowing the details of how you’re hosting Directus or what type of database you’re using or how many collections you have or how much data, it’s hard to say why your specific query is taking six seconds to complete with a many-to-any relationship.

It does make me wonder if there’s something else going on inside your database or configuration because it’s definitely not the norm and it’s not an issue that I’ve personally seen before. And I’ve worked with a ton of Directus instances over the years.

A few things to check:

  • Database indexes - Make sure you have proper indexes on your foreign key columns
  • Database performance - Run some direct SQL queries to see if the slowness is at the database level
  • Data volume - How much data are we talking about in these related collections?
  • Server resources - CPU, memory, disk I/O on your database server

Six seconds is definitely way outside normal performance expectations for a many-to-any relationship query, even with complex data structures.

If you haven’t already, I would suggest adding this as a GitHub issue to our Directus core repository.

This will help ensure our engineering team sees it and can properly track it for potential improvements to query performance.