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, 
  product_position_source.position AS position 
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') 
  LEFT JOIN cscart_products_categories AS product_position_source ON cscart_products_categories.product_id = product_position_source.product_id 
  AND product_position_source.category_id = 5 
WHERE 
  cscart_products_categories.product_id IN (
    51567, 51565, 51564, 139, 129, 51566, 
    51301, 51, 50, 51497, 51498, 53, 22713, 
    21, 22714, 22712, 46, 22715, 22716, 
    51496, 21567, 91, 50989, 47, 105, 48, 
    49, 92, 137, 88, 126, 31, 51358, 115, 
    37, 11, 140, 41, 30, 90, 38, 124, 81, 56, 
    135, 82, 116, 117, 125, 106, 39, 193, 
    65, 40, 22710, 22711, 17, 57, 122, 58, 
    5, 22, 18, 27
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00487

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "179.37"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "30.54"
      },
      "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": 30,
            "filtered": "11.08",
            "index_condition": "(`test_uchur_k`.`cscart_products_categories`.`product_id` in (51567,51565,51564,139,129,51566,51301,51,50,51497,51498,53,22713,21,22714,22712,46,22715,22716,51496,21567,91,50989,47,105,48,49,92,137,88,126,31,51358,115,37,11,140,41,30,90,38,124,81,56,135,82,116,117,125,106,39,193,65,40,22710,22711,17,57,122,58,5,22,18,27))",
            "cost_info": {
              "read_cost": "68.94",
              "eval_cost": "3.05",
              "prefix_cost": "138.14",
              "data_read_per_join": "488"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ]
          }
        },
        {
          "table": {
            "table_name": "product_position_source",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "pt"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id",
              "product_id"
            ],
            "key_length": "6",
            "ref": [
              "const",
              "test_uchur_k.cscart_products_categories.product_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 30,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "7.64",
              "eval_cost": "3.05",
              "prefix_cost": "148.83",
              "data_read_per_join": "488"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "position"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id category_ids position
5 6,40M,42
11 8,40M,42,394
17 6,40M,44
18 6,40M,43
21 10,40M,43
22 6,40M,42
27 6,40M,42
30 18,40M,44,391
31 18,40M,43,391
37 10,40M,42
38 10,40M,42
39 10,40M,42
40 10,40M,44
41 10,40M,43
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
56 8,40M,42,394
57 6,40M,44
58 6,40M,43
65 25M,45
81 8,40M,43,394
82 8,40M,44,394
88 40M,42,235
90 9,40M,43
91 9,40M,44
92 10,40M,42
105 6,40M,44,394
106 6,40M,43,394
115 8,40M,42,394
116 8,40M,42,394
117 8,40M,42,394
122 6,40M,42
124 7,40M,42
125 7,40M,42
126 8,40M,42,394
129 24,40M,42
135 36M,45,394
137 7,40M,42
139 24,40M,42
140 25,40M,42
193 9,40M,43
21567 29M,40,42,394
22710 18M,40,42,392
22711 18M,40,42,392
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
51301 24,40M,42
51358 18,40M,42,391
51496 33M,40
51497 33M,40
51498 33M,40
51564 9M,40,42
51565 10,40M,42
51566 9M,40,42
51567 10M,40,42