Optimizing database structure

System Information
  • Strapi Version: 4.8.2
  • Operating System: AlmaLinux release 8.7
  • Database: MySQL (MariaDB 10.3)
  • Node Version: 18.12.1
  • NPM Version: 8.19.2
  • Yarn Version: Using NPM


I’m relatively new to using Strapi in a production grade setup. However I’m already facing some performance issues when I have relatively large tables.

I have 3 separate collection types:
Unit (GPS Unit)
Beacon (could be an iTag)
Detection (record of when a given beacon was detected and by what unit)

Since these collection types are connected through relations, please consider the following relation types from the detection:
Detection → unit (unit has many detections)
Detection → beacon (beacon has many detections)

Because of this, I get the main table for detections and then two links tables for the relation data.

HOWEVER, because of this I get very slow queries and I’m currently at around 800.000 records, but this will grow.

Since I know a detection will ALWAYS have 1 unit and 1 beacon I would prefer if the relation could be handled through simple foreign key fields in the detection table.

Therefore my question is: Is there any way to alter the way relations are structured in the database?

Any help is appreciated, thanks!