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
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00295

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "121.86"
    },
    "grouping_operation": {
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_products_categories",
            "access_type": "range",
            "possible_keys": [
              "PRIMARY",
              "pt"
            ],
            "key": "pt",
            "used_key_parts": [
              "product_id"
            ],
            "key_length": "3",
            "rows_examined_per_scan": 99,
            "rows_produced_per_join": 99,
            "filtered": "100.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))",
            "cost_info": {
              "read_cost": "42.66",
              "eval_cost": "9.90",
              "prefix_cost": "52.56",
              "data_read_per_join": "1K"
            },
            "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": 99,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "24.75",
              "eval_cost": "9.90",
              "prefix_cost": "87.21",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "position"
            ]
          }
        },
        {
          "table": {
            "table_name": "cscart_categories",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "p_category_id"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id"
            ],
            "key_length": "3",
            "ref": [
              "test_uchur_k.cscart_products_categories.category_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 4,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "24.75",
              "eval_cost": "0.50",
              "prefix_cost": "121.86",
              "data_read_per_join": "26K"
            },
            "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')))"
          }
        }
      ]
    }
  }
}

Result

product_id category_ids position
4 42,40M,6
7 7,40M,42
8 42,40M,390,7
10 6,42,40M
12 42,40M,7,390
16 40M,6,44
20 40M,43,9
23 40M,6,42
32 7,40M,42
33 40M,42,7
34 42,40M,7
35 44,40M,7
36 43,40M,7
52 9,42,40M
54 44,40M,24
62 6,40M,42
86 40M,43,394,29
89 42,40M,25
93 42,9,40M
94 43,7,40M
107 42,33,40M
108 40M,33,42
110 40M,7,42
114 40M,6,42
119 6,42,40M
120 40M,6,42
121 42,40M,6
128 10,43,40M
130 40M,42,10
141 40M,6,42
142 6,40M,42
147 7,42,40M