CODEWITHSUNDEEP
mysqlsqlnode.jssequelize.jsfastify
bluepuper
bluepuper

Reputation: 394

Request to MySQL with sequelize including some relations lasts anomaly long

Just bumped into this problem, can't figure it out for some days, mb cuz of my lack of knowledge of this technology.

I try to get user data when logging to service with integrated roles system.

So this is the structure of tables, it's pretty simple and abstract: enter image description here

And here is the problem, when I try to login to user's account that has a role that has many permissions(more than 50) then the user can get the response after 20-30-40 seconds(all that time takes request of sequelize model actually), but as soon as I remove the big amount of permissions from that role(so remains up to 10 permissions), request time drops to 1-3 seconds what is totally ok.

This is my sequelize request:

const userData = await this.Model.findByPk(user.id, {
           attributes: { exclude: ['companiesIds'] },
           include: [
               {
                   model: Company,
                   as: 'company',
                   required: true,
                   include: [
                       ... 3 models here...
                   ]
               },
               {
                   model: Company,
                   as: 'companies',
                   attributes: [... attributes ...],
                   through: { attributes: [] }
               },
               {
                   model: Roles,
                   as: 'RoleData',
                   required: false,
                   attributes: ['id', 'name', 'priority'],
                   include: [
                       {
                           model: Permissions,
                           as: 'permissions',
                           attributes: ['id', 'action_name'],
                           through: { attributes: [] }
                       },
                   ]
               }
           ]
       })

This is the relations:

Roles.belongsToMany(Permissions, { through: RolePermission, as: 'permissions' })
Permissions.belongsToMany(Roles, { through: RolePermission, as: 'roles' })

Roles.hasMany(User);
User.belongsTo(Roles, { foreignKey: 'roleId', as: 'RoleData'});

So when I wrap this request with time logs, I can know 20-30-40 seconds pass between logs, so seems like the problem exactly in this request. But can't understand why processing of extra 50 rows takes so long

Upvotes: 0

Views: 55

Answers (1)

Anatoly
Anatoly

Reputation: 22803

The problem is that you indicated several many-to-many associations in the same query. That means that in SQL all these records are multiplied (i.e. if you have 100 records in Companies and 100 in RolePermissions - you'll end up with 10000 records in total in the result SQL dataset). Try to keep only one many-to-many association. It would be better to execute as many queries as many many-to-many associations you want to get along with a user.

Upvotes: 1

Related Questions