I am using MySQL 8.0 and I have an ACL table, which has two columns int UserID and tinyint GroupID. They both form a composite primary key. A user can be in many groups, however there are only a few users who have such groups (administrators, moderators, etc.). I have to get the user's ACL groups when they log in, but because of that I have to perform two SELECT queries, one of User table and another of ACL table.
Would it be a good choice to change the data type of GroupID to JSON and store all group IDs of a user in one field? I could then use INNER JOIN and get all necessary data with a single query and UserID would become the primary key of ACL table. Are there any drawbacks to such a structure or perhaps better solutions? I intend to mostly just use the SELECT operation on the GroupID field, UPDATE and INSERT would be rarely used.