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 (
    21, 
    31, 
    46, 
    47, 
    48, 
    49, 
    50, 
    51, 
    53, 
    88, 
    91, 
    92, 
    105, 
    126, 
    129, 
    137, 
    138, 
    139, 
    21567, 
    22712, 
    22713, 
    22714, 
    22715, 
    22716, 
    50989, 
    51081, 
    51082, 
    51083, 
    51085, 
    51086, 
    51087, 
    51088, 
    51089, 
    51090, 
    51091, 
    51092, 
    51093, 
    51095, 
    51096, 
    51097, 
    51098, 
    51099, 
    51100, 
    51101, 
    51102, 
    51103, 
    51105, 
    51121, 
    51122, 
    51123, 
    51125, 
    51126, 
    51127, 
    51128, 
    51129, 
    51152, 
    51153, 
    51154, 
    51155, 
    51156, 
    51157, 
    51158, 
    51159, 
    51160, 
    51172, 
    51173, 
    51174, 
    51176, 
    51221, 
    51223, 
    51224, 
    51296, 
    51297, 
    51298, 
    51299, 
    51300, 
    51301, 
    51302, 
    51303, 
    51304, 
    51305, 
    51306, 
    51307, 
    51308, 
    51309, 
    51316, 
    51317, 
    51318, 
    51319, 
    51320, 
    51321, 
    51322, 
    51323, 
    51324, 
    51340, 
    51341, 
    51342, 
    51343, 
    51366, 
    51367, 
    51368, 
    51369, 
    51370, 
    51371, 
    51372, 
    51373, 
    51395, 
    51396, 
    51397, 
    51443, 
    51452, 
    51453, 
    51496, 
    51497, 
    51498, 
    51555, 
    51556, 
    51564, 
    51565, 
    51566, 
    51567, 
    51568, 
    51569, 
    51570, 
    51571, 
    51572, 
    51573, 
    51574, 
    51575, 
    51576, 
    51577, 
    51578, 
    51579, 
    51580, 
    51581, 
    51582, 
    51583, 
    51584, 
    51585, 
    51586
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00364

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "202.03"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "63.90"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_categories",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "p_category_id"
            ],
            "rows_examined_per_scan": 383,
            "rows_produced_per_join": 15,
            "filtered": "4.00",
            "cost_info": {
              "read_cost": "40.09",
              "eval_cost": "1.53",
              "prefix_cost": "41.62",
              "data_read_per_join": "81K"
            },
            "used_columns": [
              "category_id",
              "storefront_id",
              "usergroup_ids",
              "status"
            ],
            "attached_condition": "((`test_uchur_k`.`cscart_categories`.`storefront_id` in (0,1)) and ((`test_uchur_k`.`cscart_categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`test_uchur_k`.`cscart_categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`test_uchur_k`.`cscart_categories`.`usergroup_ids`))) and (`test_uchur_k`.`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": [
              "test_uchur_k.cscart_categories.category_id"
            ],
            "rows_examined_per_scan": 18,
            "rows_produced_per_join": 63,
            "filtered": "23.17",
            "index_condition": "(`test_uchur_k`.`cscart_products_categories`.`product_id` in (21,31,46,47,48,49,50,51,53,88,91,92,105,126,129,137,138,139,21567,22712,22713,22714,22715,22716,50989,51081,51082,51083,51085,51086,51087,51088,51089,51090,51091,51092,51093,51095,51096,51097,51098,51099,51100,51101,51102,51103,51105,51121,51122,51123,51125,51126,51127,51128,51129,51152,51153,51154,51155,51156,51157,51158,51159,51160,51172,51173,51174,51176,51221,51223,51224,51296,51297,51298,51299,51300,51301,51302,51303,51304,51305,51306,51307,51308,51309,51316,51317,51318,51319,51320,51321,51322,51323,51324,51340,51341,51342,51343,51366,51367,51368,51369,51370,51371,51372,51373,51395,51396,51397,51443,51452,51453,51496,51497,51498,51555,51556,51564,51565,51566,51567,51568,51569,51570,51571,51572,51573,51574,51575,51576,51577,51578,51579,51580,51581,51582,51583,51584,51585,51586))",
            "cost_info": {
              "read_cost": "68.94",
              "eval_cost": "6.39",
              "prefix_cost": "138.14",
              "data_read_per_join": "1022"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id category_ids
21 10,40M,43
31 18,40M,43,391
46 18,40M,42,391
47 23M,45
48 10,40M,44
49 10,40M,43
50 23,40M,44
51 23,40M,43
53 10,40M,44
88 40M,42,235
91 9,40M,44
92 10,40M,42
105 6,40M,44,394
126 8,40M,42,394
129 24,40M,42
137 7,40M,42
138 24,40M,42
139 24,40M,42
21567 29M,40,42,394
22712 7M,40,42
22713 7M,40,42
22714 7M,40,42
22715 7M,40,42
22716 7M,40,42
50989 29M,40,42,394
51081 7M,40,42
51082 7M,40,42
51083 7M,40,42
51085 7M,40,42
51086 7M,40,42
51087 7M,40,42
51088 7M,40,42
51089 7M,40,42
51090 7M,40,42
51091 7M,40,42
51092 7M,40,42
51093 7M,40,42
51095 7M,40,42
51096 7M,40,42
51097 7M,40,42
51098 7M,40,42
51099 7M,40,42
51100 7M,40,42
51101 7M,40,42
51102 7M,40,42
51103 7M,40,42
51105 7M,40,42
51121 10,40M,42
51122 10,40M,42
51123 10,40M,42
51125 10,40M,44
51126 10,40M,44
51127 10,40M,44
51128 10,40M,44
51129 10,40M,44
51152 10,40M,43
51153 10,40M,43
51154 10,40M,43
51155 10,40M,43
51156 10,40M,44
51157 10,40M,44
51158 10,40M,44
51159 10,40M,44
51160 10,40M,44
51172 9,40M,44
51173 9,40M,44
51174 9,40M,44
51176 9,40M,44
51221 7,40M,42
51223 7,40M,42
51224 7,40M,42
51296 24,40M,42
51297 24,40M,42
51298 24,40M,42
51299 24,40M,42
51300 24,40M,42
51301 24,40M,42
51302 24,40M,42
51303 24,40M,42
51304 24,40M,42
51305 24,40M,42
51306 24,40M,42
51307 24,40M,42
51308 24,40M,42
51309 24,40M,42
51316 23,40M,43
51317 23,40M,43
51318 23,40M,43
51319 23,40M,43
51320 23,40M,44
51321 23,40M,44
51322 23,40M,44
51323 23,40M,44
51324 23,40M,44
51340 23M,45
51341 23M,45
51342 23M,45
51343 23M,45
51366 18,40M,43,391
51367 18,40M,43,391
51368 18,40M,43,391
51369 18,40M,43,391
51370 18,40M,42,391
51371 18,40M,42,391
51372 18,40M,42,391
51373 18,40M,42,391
51395 40M,42,235
51396 40M,42,235
51397 40M,42,235
51443 24,40M,42
51452 18,40M,42,391
51453 18,40M,42,391
51496 33M,40
51497 33M,40
51498 33M,40
51555 40M,42,235
51556 40M,42,235
51564 9M,40,42
51565 10,40M,42
51566 9M,40,42
51567 10M,40,42
51568 9M,40,42
51569 9M,40,42
51570 9M,40,42
51571 9M,40,42
51572 9M,40,42
51573 9M,40,42
51574 10,40M,42
51575 10,40M,42
51576 10,40M,42
51577 10,40M,42
51578 9M,40,42
51579 9M,40,42
51580 9M,40,42
51581 9M,40,42
51582 10M,40,42
51583 10M,40,42
51584 10M,40,42
51585 10M,40,42
51586 10M,40,42