Skip to main content
Formatting fixes
Source Link
Toby Speight
  • 88.3k
  • 14
  • 104
  • 327

I am understanding that your goal is to return a set of data that a user is authorized to see. I further understand that ChannelUsers and WorkGroupUsers are only used to validate the authorization. (I see you do select ChannelUsers.user_id but I don't think you really need it - you can take it from Users.id`Users.id instead, I believe).

Given that, I believe you should test an alternative query to see whether this yield better performance. The SQL would be similar to this....:

        SELECT 
          ${view}
        FROM Timers
        INNER JOIN WorkTypes
          ON Timers.worktype_id=WorkTypes.id
          AND WorkTypes.channel_id=?
        INNER JOIN Channels
          ON Channels.id=ChannelUsers.channel_id
        INNER JOIN Users
          ON Users.id=?
        INNER JOIN WorkGroups
          ON WorkGroupUsers.workgroup_id=WorkGroups.id
        WHERE EXISTS (
            SELECT NULL
            FROM ChannelUsers
            WHERE ChannelUsers.channel_id=WorkTypes.channel_id
              AND ChannelUsers.user_id=Users.id
        ) AND EXISTS (
            SELECT NULL
            FROM WorkGroupUsers
            WHERE WorkGroupUsers.workgroup_id=Channels.workgroup_id
              AND WorkGroupUsers.user_id=Users.id
        );

The main advantage of using EXISTS is that it won't "duplicate" the results as a join would so you don't have to do a DISTINCT; it also stop searching as soon as it has found its match. If you have indices on ChannelUsers(channel_id, user_id) and WorkGroupUsers(workgroup_id, user_id this may help, as EXISTS can blow through the index and thus get an answer faster in that manner.

Profile the original SQL against this and see what you get. The results also will be less with the EXCEPT, so validate this is acceptable.

I am understanding that your goal is to return a set of data that a user is authorized to see. I further understand that ChannelUsers and WorkGroupUsers are only used to validate the authorization. (I see you do select ChannelUsers.user_id but I don't think you really need it - you can take it from Users.id` instead, I believe).

Given that, I believe you should test an alternative query to see whether this yield better performance. The SQL would be similar to this....

        SELECT 
          ${view}
        FROM Timers
        INNER JOIN WorkTypes
          ON Timers.worktype_id=WorkTypes.id
          AND WorkTypes.channel_id=?
        INNER JOIN Channels
          ON Channels.id=ChannelUsers.channel_id
        INNER JOIN Users
          ON Users.id=?
        INNER JOIN WorkGroups
          ON WorkGroupUsers.workgroup_id=WorkGroups.id
        WHERE EXISTS (
            SELECT NULL
            FROM ChannelUsers
            WHERE ChannelUsers.channel_id=WorkTypes.channel_id
              AND ChannelUsers.user_id=Users.id
        ) AND EXISTS (
            SELECT NULL
            FROM WorkGroupUsers
            WHERE WorkGroupUsers.workgroup_id=Channels.workgroup_id
              AND WorkGroupUsers.user_id=Users.id
        );

The main advantage of using EXISTS is that it won't "duplicate" the results as a join would so you don't have to do a DISTINCT; it also stop searching as soon as it has found its match. If you have indices on ChannelUsers(channel_id, user_id) and WorkGroupUsers(workgroup_id, user_id this may help, as EXISTS can blow through the index and thus get an answer faster in that manner.

Profile the original SQL against this and see what you get. The results also will be less with the EXCEPT, so validate this is acceptable.

I am understanding that your goal is to return a set of data that a user is authorized to see. I further understand that ChannelUsers and WorkGroupUsers are only used to validate the authorization. (I see you do select ChannelUsers.user_id but I don't think you really need it - you can take it from Users.id instead, I believe).

Given that, I believe you should test an alternative query to see whether this yield better performance. The SQL would be similar to this:

        SELECT 
          ${view}
        FROM Timers
        INNER JOIN WorkTypes
          ON Timers.worktype_id=WorkTypes.id
          AND WorkTypes.channel_id=?
        INNER JOIN Channels
          ON Channels.id=ChannelUsers.channel_id
        INNER JOIN Users
          ON Users.id=?
        INNER JOIN WorkGroups
          ON WorkGroupUsers.workgroup_id=WorkGroups.id
        WHERE EXISTS (
            SELECT NULL
            FROM ChannelUsers
            WHERE ChannelUsers.channel_id=WorkTypes.channel_id
              AND ChannelUsers.user_id=Users.id
        ) AND EXISTS (
            SELECT NULL
            FROM WorkGroupUsers
            WHERE WorkGroupUsers.workgroup_id=Channels.workgroup_id
              AND WorkGroupUsers.user_id=Users.id
        );

The main advantage of using EXISTS is that it won't "duplicate" the results as a join would so you don't have to do a DISTINCT; it also stop searching as soon as it has found its match. If you have indices on ChannelUsers(channel_id, user_id) and WorkGroupUsers(workgroup_id, user_id this may help, as EXISTS can blow through the index and thus get an answer faster in that manner.

Profile the original SQL against this and see what you get. The results also will be less with the EXCEPT, so validate this is acceptable.

Source Link
this
  • 2k
  • 14
  • 24

I am understanding that your goal is to return a set of data that a user is authorized to see. I further understand that ChannelUsers and WorkGroupUsers are only used to validate the authorization. (I see you do select ChannelUsers.user_id but I don't think you really need it - you can take it from Users.id` instead, I believe).

Given that, I believe you should test an alternative query to see whether this yield better performance. The SQL would be similar to this....

        SELECT 
          ${view}
        FROM Timers
        INNER JOIN WorkTypes
          ON Timers.worktype_id=WorkTypes.id
          AND WorkTypes.channel_id=?
        INNER JOIN Channels
          ON Channels.id=ChannelUsers.channel_id
        INNER JOIN Users
          ON Users.id=?
        INNER JOIN WorkGroups
          ON WorkGroupUsers.workgroup_id=WorkGroups.id
        WHERE EXISTS (
            SELECT NULL
            FROM ChannelUsers
            WHERE ChannelUsers.channel_id=WorkTypes.channel_id
              AND ChannelUsers.user_id=Users.id
        ) AND EXISTS (
            SELECT NULL
            FROM WorkGroupUsers
            WHERE WorkGroupUsers.workgroup_id=Channels.workgroup_id
              AND WorkGroupUsers.user_id=Users.id
        );

The main advantage of using EXISTS is that it won't "duplicate" the results as a join would so you don't have to do a DISTINCT; it also stop searching as soon as it has found its match. If you have indices on ChannelUsers(channel_id, user_id) and WorkGroupUsers(workgroup_id, user_id this may help, as EXISTS can blow through the index and thus get an answer faster in that manner.

Profile the original SQL against this and see what you get. The results also will be less with the EXCEPT, so validate this is acceptable.