Issue With Dataloader And Many-to-many Relations

by ADMIN 49 views

Performance Issue with Dataloader in Many-to-Many Relations

When working with many-to-many relations in MikroORM, using the dataloader functionality can cause a significant performance issue. The generated SQL query with dataloader used differs from a corresponding non-dataloader query, resulting in an unneeded extra join that can lead to a huge amount of duplicated, unneeded rows appearing in the SQL execution result.

SQL Query Comparison

To illustrate this issue, let's compare the two SQL queries generated by MikroORM with and without dataloader. The first query is generated with dataloader enabled:

select
	`c0` .*,
	`p1`.`id` as `p1__id`
from
	`category` as `c0`
left join `category_products` as `c2` on
	`c0`.`id` = `c2`.`category_id`
left join `product` as `p1` on
	`c2`.`product_id` = `p1`.`id`
left join `category_products` as `c3` on
	`c0`.`id` = `c3`.`category_id`
where
	`c3`.`product_id` in (61876)

And the second query is generated without dataloader:

select
	`c1` .*,
	`c0`.`product_id` as `fk__product_id`,
	`c0`.`category_id` as `fk__category_id`
from
	`category_products` as `c0`
inner join `category` as `c1` on
	`c0`.`category_id` = `c1`.`id`
where
	`c0`.`product_id` in (61876)

As you can see, there's an extra join in the dataloader-enabled query, which is the pivot table category_products being joined twice. This double join causes extra rows that satisfy the where condition to appear in the end result.

Reproduction

The issue can be reproduced by using the following code:

product.categories.init({ dataloader: true });

This will generate the SQL query with the extra join, resulting in a performance issue.

Driver and MikroORM Version

The issue is observed with the following configuration:

  • Driver: @mikro-orm/mysql
  • MikroORM version: 6.4.10
  • Node.js version: 22
  • Operating system: Linux

Conclusion

Using the dataloader functionality in many-to-many relations can cause a significant performance issue due to the extra join generated by MikroORM. This issue can be remedied by altering the SQL query generated by MikroORM to avoid introducing double joins when dataloader is used.

Recommendations

To avoid this issue, it's recommended to:

  • Use the dataloader functionality with caution, especially in many-to-many relations.
  • Monitor the SQL queries generated by MikroORM to detect any performance issues.
  • Consider altering the SQL query generated by MikroORM to avoid introducing double joins when dataloader is used.

Reproduction Repository

The reproduction repository can be found at: https://github.com/sosmo/mikroorm-reproduction

Validations

The issue has been validated by:

Q: What is the issue with dataloader and many-to-many relations?

A: The issue is that using the dataloader functionality in many-to-many relations can cause a significant performance issue due to the extra join generated by MikroORM.

Q: What is the difference between the SQL queries generated with and without dataloader?

A: The SQL query generated with dataloader enabled has an extra join, which is the pivot table category_products being joined twice. This double join causes extra rows that satisfy the where condition to appear in the end result.

Q: Can you provide an example of the SQL queries generated with and without dataloader?

A: Yes, here are the two SQL queries:

select
	`c0` .*,
	`p1`.`id` as `p1__id`
from
	`category` as `c0`
left join `category_products` as `c2` on
	`c0`.`id` = `c2`.`category_id`
left join `product` as `p1` on
	`c2`.`product_id` = `p1`.`id`
left join `category_products` as `c3` on
	`c0`.`id` = `c3`.`category_id`
where
	`c3`.`product_id` in (61876)

select
	`c1` .*,
	`c0`.`product_id` as `fk__product_id`,
	`c0`.`category_id` as `fk__category_id`
from
	`category_products` as `c0`
inner join `category` as `c1` on
	`c0`.`category_id` = `c1`.`id`
where
	`c0`.`product_id` in (61876)

Q: How can I reproduce the issue?

A: You can reproduce the issue by using the following code:

product.categories.init({ dataloader: true });

Q: What driver and MikroORM version are affected by this issue?

A: The issue is observed with the following configuration:

  • Driver: @mikro-orm/mysql
  • MikroORM version: 6.4.10
  • Node.js version: 22
  • Operating system: Linux

Q: How can I avoid this issue?

A: To avoid this issue, you can:

  • Use the dataloader functionality with caution, especially in many-to-many relations.
  • Monitor the SQL queries generated by MikroORM to detect any performance issues.
  • Consider altering the SQL query generated by MikroORM to avoid introducing double joins when dataloader is used.

Q: Where can I find the reproduction repository?

A: The reproduction repository can be found at: https://github.com/sosmo/mikroorm-reproduction

Q: What validations have been performed to ensure this is a concrete bug?

A: The issue has been validated by: