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 (
    211, 
    210, 
    209, 
    67, 
    25, 
    116, 
    213, 
    215, 
    226, 
    200, 
    154, 
    53, 
    52, 
    111, 
    82, 
    84, 
    228, 
    85, 
    88, 
    287, 
    315, 
    26, 
    35, 
    47, 
    81, 
    107, 
    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, 
    286, 
    123, 
    174, 
    171, 
    280, 
    282, 
    278, 
    331, 
    335, 
    339, 
    341, 
    355, 
    356, 
    357, 
    358, 
    359, 
    360, 
    361, 
    362, 
    363, 
    364, 
    365, 
    366, 
    367, 
    368, 
    369, 
    370, 
    379, 
    380, 
    381, 
    382, 
    383, 
    384, 
    385, 
    386, 
    387, 
    388, 
    389, 
    390, 
    391, 
    392, 
    393, 
    394, 
    395, 
    396, 
    397
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00147

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "39.08"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "3.53"
      },
      "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.53",
              "eval_cost": "0.67",
              "prefix_cost": "20.20",
              "data_read_per_join": "8K"
            },
            "used_columns": [
              "category_id",
              "storefront_id",
              "usergroup_ids",
              "status"
            ],
            "attached_condition": "((`pankajecarter_systemfour`.`cscart_categories`.`storefront_id` in (0,1)) and ((`pankajecarter_systemfour`.`cscart_categories`.`usergroup_ids` = '') or find_in_set(0,`pankajecarter_systemfour`.`cscart_categories`.`usergroup_ids`) or find_in_set(1,`pankajecarter_systemfour`.`cscart_categories`.`usergroup_ids`)) and (`pankajecarter_systemfour`.`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": [
              "pankajecarter_systemfour.cscart_categories.category_id"
            ],
            "rows_examined_per_scan": 3,
            "rows_produced_per_join": 3,
            "filtered": "35.01",
            "index_condition": "(`pankajecarter_systemfour`.`cscart_products_categories`.`product_id` in (211,210,209,67,25,116,213,215,226,200,154,53,52,111,82,84,228,85,88,287,315,26,35,47,81,107,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,286,123,174,171,280,282,278,331,335,339,341,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397))",
            "cost_info": {
              "read_cost": "13.34",
              "eval_cost": "0.71",
              "prefix_cost": "35.55",
              "data_read_per_join": "56"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id category_ids
25 195M
26 220M
28 225M
31 225M
32 176M
33 176M
34 225M
35 220M
36 220M
38 225M
47 247M
52 202M
53 202M
55 187M
67 236M
69 236M
80 226M
81 226M
82 246M
83 246M
84 246M
85 246M
86 238M
87 246M
88 247M
91 227M
92 227M
93 248M
101 238M
102 227M
105 238M
106 238M
107 227M
109 227M
110 243M
111 243M
113 237M
116 243M
117 237M
118 244M
120 237M
123 244M
125 237M
132 179,182M
133 179M,182
138 249M
139 249M
146 179M
147 179M
154 181M
155 181M
157 181M
161 198M,199
163 199,198M
165 198M,199
166 198M,199
171 252M
174 252M
176 252M
183 200M
184 200M
188 213M
200 230M
202 230M
203 230M
206 171M
209 172M
210 172M
211 172M
212 172M
213 201M
215 201M
219 169M
220 169M
221 169M
222 169M
223 165M
224 165M
225 165M
226 201M
228 170M
244 168M
247 165M
278 224M
280 224M
282 224M
286 242M
287 242M
315 242M
331 235M
335 235M
339 235M
341 235M
355 235M
356 235M
357 235M
358 235M
359 235M
360 235M
361 235M
362 235M
363 235M
364 235M
365 235M
366 235M
367 235M
368 235M
369 235M
370 235M
379 235M
380 235M
381 235M
382 235M
383 235M
384 235M
385 235M
386 235M
387 235M
388 235M
389 235M
390 235M
391 235M
392 235M
393 235M
394 235M
395 235M
396 235M
397 235M