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 (
    286, 
    278, 
    280, 
    282, 
    247, 
    255, 
    256, 
    257, 
    249, 
    250, 
    251, 
    252, 
    253, 
    254, 
    248, 
    245, 
    246, 
    66, 
    166, 
    192, 
    194, 
    195, 
    196, 
    197, 
    198, 
    199, 
    200, 
    201, 
    202, 
    203, 
    204, 
    230, 
    231, 
    232, 
    233, 
    234, 
    235, 
    236, 
    237, 
    238, 
    239, 
    78, 
    79, 
    80, 
    81, 
    82, 
    83, 
    84, 
    85, 
    86, 
    87, 
    88, 
    89, 
    90, 
    91, 
    92, 
    93, 
    94, 
    95, 
    96, 
    97, 
    100, 
    101, 
    102, 
    103, 
    104, 
    105, 
    106, 
    107, 
    108, 
    109, 
    110, 
    111, 
    112, 
    113, 
    114, 
    115, 
    116, 
    117, 
    118, 
    119, 
    120, 
    121, 
    122, 
    123, 
    124, 
    125, 
    127, 
    129, 
    131, 
    132, 
    133, 
    134, 
    135, 
    136, 
    137, 
    138, 
    139, 
    140, 
    141, 
    142, 
    143, 
    144, 
    145, 
    146, 
    147, 
    148, 
    149, 
    150, 
    151, 
    152, 
    153, 
    154, 
    155, 
    156, 
    157, 
    160, 
    161, 
    163, 
    164, 
    165, 
    167, 
    168, 
    169, 
    170, 
    172, 
    173, 
    175
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00207

JSON explain

{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "read_sorted_file": {
          "filesort": {
            "sort_key": "cscart_products_categories.product_id",
            "table": {
              "table_name": "cscart_products_categories",
              "access_type": "ALL",
              "possible_keys": ["PRIMARY", "pt"],
              "rows": 281,
              "filtered": 53.02490997,
              "attached_condition": "cscart_products_categories.product_id in (286,278,280,282,247,255,256,257,249,250,251,252,253,254,248,245,246,66,166,192,194,195,196,197,198,199,200,201,202,203,204,230,231,232,233,234,235,236,237,238,239,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,127,129,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,160,161,163,164,165,167,168,169,170,172,173,175)"
            }
          }
        }
      },
      {
        "table": {
          "table_name": "cscart_categories",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "c_status", "p_category_id"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["category_id"],
          "ref": ["cscart.cscart_products_categories.category_id"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "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')"
        }
      }
    ]
  }
}

Result

product_id category_ids
66 189M
78 226M
79 226M
80 226M
81 226M
82 246M
83 246M
84 246M
85 246M
86 238M
87 246M
88 247M
89 247M
90 247M
91 227M
92 227M
93 248M
94 248M
95 248M
96 248M
97 242M
100 242M
101 238M
102 227M
103 242M
104 242M
105 238M
106 238M
107 227M
108 242M
109 227M
110 243M
111 243M
112 243M
113 237M
114 243M
115 208M
116 243M
117 237M
118 244M
119 244M
120 237M
121 244M
122 208M
123 244M
124 208M
125 237M
127 209M
129 209M
131 209M
132 179,182M
133 179M,182
134 182M,179
135 179,182M
136 249M
137 249M
138 249M
139 249M
140 180M
141 180M
142 180M
143 180M
144 180M
145 179M
146 179M
147 179M
148 179M
149 179M
150 210M
151 210M
152 210M
153 181M
154 181M
155 181M
156 181M
157 181M
160 210M
161 199,198M
163 199,198M
164 198M,199
165 198M,199
166 199,198M
167 198,197M,199
168 199,198,197M
169 197M,198,199
170 212M
172 199M
173 199M
175 199M
192 232M
194 232M
195 232M
196 232M
197 229M,231
198 229M
199 229M
200 230M
201 230M
202 230M
203 230M
204 232M
230 216M
231 216M
232 216M
233 217M
234 217M
235 217M
236 218M
237 218M
238 235M
239 235M
245 235M
246 199M
247 165M
248 263M,255,248M
249 258,263M
250 263M,257
251 259M
252 259M
253 259M
254 260M
255 260M
256 261M
257 260M
278 224M
280 224M
282 224M
286 242M