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 (
    130, 51106, 51107, 51112, 51111, 51110, 
    51109, 51108, 128, 51118, 51117, 51116, 
    51115, 51114, 51113, 51123, 51122, 
    51121, 92, 49, 51129, 51128, 51127, 
    51126, 51125, 48, 51132, 51131, 51134, 
    51133, 51130, 41, 51139, 51138, 51137, 
    51136, 51135, 40, 51144, 51143, 51142, 
    51141, 51140, 39, 51146, 51145, 38, 
    51148, 51147, 51150, 51149, 37, 51155, 
    51154, 51153, 51152, 21, 51158, 51157, 
    51156, 53, 51160, 51159, 50984, 51450, 
    51451, 51329, 51328, 51327, 50985, 
    51331, 51330, 50986, 51332, 51339, 
    51338, 51337, 51336, 50988, 134, 51565, 
    51574, 51575, 51576, 51577, 51567, 
    51582, 51583, 51584, 51585, 51586
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00338

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "174.46"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "36.32"
      },
      "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": 36,
            "filtered": "13.17",
            "index_condition": "(`test_uchur_k`.`cscart_products_categories`.`product_id` in (130,51106,51107,51112,51111,51110,51109,51108,128,51118,51117,51116,51115,51114,51113,51123,51122,51121,92,49,51129,51128,51127,51126,51125,48,51132,51131,51134,51133,51130,41,51139,51138,51137,51136,51135,40,51144,51143,51142,51141,51140,39,51146,51145,38,51148,51147,51150,51149,37,51155,51154,51153,51152,21,51158,51157,51156,53,51160,51159,50984,51450,51451,51329,51328,51327,50985,51331,51330,50986,51332,51339,51338,51337,51336,50988,134,51565,51574,51575,51576,51577,51567,51582,51583,51584,51585,51586))",
            "cost_info": {
              "read_cost": "68.94",
              "eval_cost": "3.63",
              "prefix_cost": "138.14",
              "data_read_per_join": "581"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id category_ids
21 10,40M,43
37 10,40M,42
38 10,40M,42
39 10,40M,42
40 10,40M,44
41 10,40M,43
48 10,40M,44
49 10,40M,43
53 10,40M,44
92 10,40M,42
128 10,40M,43
130 10,40M,42
134 10,45M
50984 10M,45
50985 10M
50986 10M
50988 10M
51106 10,40M,42
51107 10,40M,42
51108 10,40M,42
51109 10,40M,42
51110 10,40M,42
51111 10,40M,42
51112 10,40M,42
51113 10,40M,43
51114 10,40M,43
51115 10,40M,43
51116 10,40M,43
51117 10,40M,43
51118 10,40M,43
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
51130 10,40M,43
51131 10,40M,43
51132 10,40M,43
51133 10,40M,43
51134 10,40M,43
51135 10,40M,44
51136 10,40M,44
51137 10,40M,44
51138 10,40M,44
51139 10,40M,44
51140 10,40M,42
51141 10,40M,42
51142 10,40M,42
51143 10,40M,42
51144 10,40M,42
51145 10,40M,42
51146 10,40M,42
51147 10,40M,42
51148 10,40M,42
51149 10,40M,42
51150 10,40M,42
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
51327 10M
51328 10M
51329 10M
51330 10M
51331 10M
51332 10M
51336 10M
51337 10M
51338 10M
51339 10M
51450 10M,45
51451 10M,45
51565 10,40M,42
51567 10M,40,42
51574 10,40M,42
51575 10,40M,42
51576 10,40M,42
51577 10,40M,42
51582 10M,40,42
51583 10M,40,42
51584 10M,40,42
51585 10M,40,42
51586 10M,40,42