SELECT 
  cscart_products_categories.product_id, 
  GROUP_CONCAT(
    IF(
      cscart_products_categories.link_type = "M", 
      CONCAT(
        cscart_products_categories.category_id, 
        "M"
      ), 
      cscart_products_categories.category_id
    )
  ) AS category_ids 
FROM 
  cscart_products_categories 
  INNER JOIN cscart_categories ON cscart_categories.category_id = cscart_products_categories.category_id 
  AND cscart_categories.storefront_id IN (0, 1) 
  AND (
    cscart_categories.usergroup_ids = '' 
    OR FIND_IN_SET(
      0, cscart_categories.usergroup_ids
    ) 
    OR FIND_IN_SET(
      1, cscart_categories.usergroup_ids
    )
  ) 
  AND cscart_categories.status IN ('A', 'H') 
WHERE 
  cscart_products_categories.product_id IN (
    109, 91, 92, 161, 188, 202, 203, 34, 31, 
    38, 80, 165, 166, 86, 106, 105, 247, 101, 
    33, 32, 163, 244, 212, 155, 102, 55, 176, 
    69, 138, 110, 36, 206, 28, 184, 183, 133, 
    132, 125, 120, 223, 225, 224, 146, 147, 
    93, 117, 219, 222, 220, 221, 87, 113, 
    139, 118, 83, 157, 123, 174, 171, 280, 
    282, 278, 336, 20, 119, 100, 46, 204, 
    45, 68, 29, 6, 8, 14, 9, 10, 19, 21, 7, 27, 
    124, 50, 51, 49, 48, 54, 44, 194, 137, 
    153, 164, 236, 234, 150, 151, 178, 181, 
    248, 65, 64, 63, 62, 66, 140, 141, 144, 
    143, 142, 13
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00120

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "42.37"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "3.64"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_categories",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "p_category_id"
            ],
            "rows_examined_per_scan": 84,
            "rows_produced_per_join": 3,
            "filtered": "4.00",
            "cost_info": {
              "read_cost": "19.61",
              "eval_cost": "0.67",
              "prefix_cost": "20.28",
              "data_read_per_join": "8K"
            },
            "used_columns": [
              "category_id",
              "storefront_id",
              "usergroup_ids",
              "status"
            ],
            "attached_condition": "((`atulecarter_atul_demo2`.`cscart_categories`.`storefront_id` in (0,1)) and ((`atulecarter_atul_demo2`.`cscart_categories`.`usergroup_ids` = '') or find_in_set(0,`atulecarter_atul_demo2`.`cscart_categories`.`usergroup_ids`) or find_in_set(1,`atulecarter_atul_demo2`.`cscart_categories`.`usergroup_ids`)) and (`atulecarter_atul_demo2`.`cscart_categories`.`status` in ('A','H')))"
          }
        },
        {
          "table": {
            "table_name": "cscart_products_categories",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "pt"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id"
            ],
            "key_length": "3",
            "ref": [
              "atulecarter_atul_demo2.cscart_categories.category_id"
            ],
            "rows_examined_per_scan": 4,
            "rows_produced_per_join": 3,
            "filtered": "27.08",
            "index_condition": "(`atulecarter_atul_demo2`.`cscart_products_categories`.`product_id` in (109,91,92,161,188,202,203,34,31,38,80,165,166,86,106,105,247,101,33,32,163,244,212,155,102,55,176,69,138,110,36,206,28,184,183,133,132,125,120,223,225,224,146,147,93,117,219,222,220,221,87,113,139,118,83,157,123,174,171,280,282,278,336,20,119,100,46,204,45,68,29,6,8,14,9,10,19,21,7,27,124,50,51,49,48,54,44,194,137,153,164,236,234,150,151,178,181,248,65,64,63,62,66,140,141,144,143,142,13))",
            "cost_info": {
              "read_cost": "15.76",
              "eval_cost": "0.73",
              "prefix_cost": "38.73",
              "data_read_per_join": "58"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id category_ids
6 191M
7 224M
8 190,193M
9 190,193M
10 190,193M
13 222M
14 191M
19 194M
20 222M
21 194M
27 225M
28 225M
29 176M
31 225M
32 176M
33 176M
34 225M
36 220M
38 225M
44 221M
45 221M
46 221M
48 186M
49 186M
50 186M
51 186M
54 202M
55 187M
62 189M
63 189M
64 189M
65 189M
66 189M
68 236M
69 236M
80 226M
83 246M
86 238M
87 246M
91 227M
92 227M
93 248M
100 242M
101 238M
102 227M
105 238M
106 238M
109 227M
110 243M
113 237M
117 237M
118 244M
119 244M
120 237M
123 244M
124 208M
125 237M
132 182M,179
133 182,179M
137 249M
138 249M
139 249M
140 180M
141 180M
142 180M
143 180M
144 180M
146 179M
147 179M
150 210M
151 210M
153 181M
155 181M
157 181M
161 198M,199
163 198M,199
164 198M,199
165 198M,199
166 199,198M
171 252M
174 252M
176 252M
178 252M
181 212M
183 200M
184 200M
188 213M
194 232M
202 230M
203 230M
204 232M
206 171M
212 172M
219 169M
220 169M
221 169M
222 169M
223 165M
224 165M
225 165M
234 217M
236 218M
244 168M
247 165M
248 248M,255
278 224M
280 224M
282 224M
336 203M