Many to Many optimisation

System Information
  • Strapi Version: 3.1.4

So I was building an ecommerce platform and have products with categories. I built a many-to-many relation through the strapi UI so 1 product can have multiple categories vice versa…

I followed this documentation for expected results:

However no join table collection was created automatically.

Now my queries are really slow for loading all products with a certain category, pseudo queries below:

BASE_URL/products?_where categories.slug=stay&_limit=20&_start=0&_sort=views DESC

BASE_URL/products?_where categories.slug=stay&_limit=20&_start=0&_sort=date DESC

The only way to speed this up was to embed a “categoryCopy” property into the product where we wrote a custom script that copies the categories they select in the strapi dashboard into the embedded property.

Have other people had issues like this?

Another note: in the database, products have no reference to categories, categories had the list of productIds (we didn’t populate out the whole product) and then as said before there’s no join table

Any help would be greatly appreciated!

An update on this:

Because we added the products from the category UI, it made the Category the dominant and therefore by switching the dominant to products we’ve managed to increase speed significantly.