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 (
    121, 119, 141, 23, 4, 142, 120, 62, 108, 
    107, 94, 8, 86, 54, 10, 93, 12, 32, 7, 130, 
    35, 16, 33, 110, 34, 89, 147, 114, 36, 
    128, 20, 52, 84, 109, 118, 19, 131, 146, 
    13, 61, 95, 15, 9, 85, 59, 111, 144, 60, 
    123, 145, 113, 6, 14, 63, 136, 50984, 
    127, 134, 42, 50985, 50986, 50988, 50993, 
    50992
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00344

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "175.36"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "27.58"
      },
      "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": 27,
            "filtered": "10.00",
            "index_condition": "(`test_uchur_k`.`cscart_products_categories`.`product_id` in (121,119,141,23,4,142,120,62,108,107,94,8,86,54,10,93,12,32,7,130,35,16,33,110,34,89,147,114,36,128,20,52,84,109,118,19,131,146,13,61,95,15,9,85,59,111,144,60,123,145,113,6,14,63,136,50984,127,134,42,50985,50986,50988,50993,50992))",
            "cost_info": {
              "read_cost": "68.94",
              "eval_cost": "2.76",
              "prefix_cost": "138.14",
              "data_read_per_join": "441"
            },
            "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": 27,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "6.89",
              "eval_cost": "2.76",
              "prefix_cost": "147.79",
              "data_read_per_join": "441"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "position"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id category_ids position
4 6,40M,42
6 6,40M,42
7 7,40M,42
8 7,40M,42,390
9 6,40M,42
10 6,40M,42
12 7,40M,42,390
13 6,40M,42
14 6,40M,42
15 6,40M,43
16 6,40M,44
19 9,40M,44
20 9,40M,43
23 6,40M,42
32 7,40M,42
33 7,40M,42
34 7,40M,42
35 7,40M,44
36 7,40M,43
42 18M,45,391
52 9,40M,42
54 24,40M,44
59 6,40M,42
60 6,40M,42
61 6,40M,42
62 6,40M,42
63 8M,45,394
84 28,40M,42,394
85 28,40M,42,394
86 29,40M,43,394
89 25,40M,42
93 9,40M,42
94 7,40M,43
95 31,40M,43
107 33,40M,42
108 33,40M,42
109 7,40M,42
110 7,40M,42
111 7,40M,42
113 6,40M,42
114 6,40M,42
118 6,40M,42
119 6,40M,42
120 6,40M,42
121 6,40M,42
123 6,40M,42
127 34M,45
128 10,40M,43
130 10,40M,42
131 9,40M,42
134 10,45M
136 37M,45,394
141 6,40M,42
142 6,40M,42
144 7,40M,42,390
145 7,40M,42,390
146 7,40M,42,390
147 7,40M,42
50984 10M,45
50985 10M
50986 10M
50988 10M
50992 25M
50993 25M