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.company_id = 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 = 286 
WHERE 
  cscart_products_categories.product_id IN (
    386230, 1026757, 671515, 1001748, 1002989, 
    1026639, 1026634, 1028815, 1029088, 
    459108, 562176, 1205512, 1779540, 
    1029029, 1914195, 1028861, 1006064, 
    999679, 1329210, 1005469, 1005477, 
    1026067, 1028489, 1001621, 1051465, 
    1000770, 1026826, 1051466, 1000273, 
    1005777, 1000893, 999510, 998713, 
    1026835, 1005493, 1000638, 1118393, 
    999450, 1914203, 999451, 1002439, 
    2089921, 1914202, 1033401, 2077210, 
    1730378, 1730377, 1730386, 1033409, 
    1730384, 999697, 1734632, 1232017, 
    1629494, 1232162, 1231209, 1231096, 
    1629497, 2068214, 1231338, 1232051, 
    1232167, 1231215, 1231839
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00312

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "227.68"
    },
    "grouping_operation": {
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_products_categories",
            "access_type": "range",
            "possible_keys": [
              "PRIMARY",
              "link_type",
              "pt"
            ],
            "key": "pt",
            "used_key_parts": [
              "product_id"
            ],
            "key_length": "3",
            "rows_examined_per_scan": 105,
            "rows_produced_per_join": 105,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "11.50",
              "eval_cost": "10.50",
              "prefix_cost": "22.00",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ],
            "attached_condition": "(`maniamall_cscart`.`cscart_products_categories`.`product_id` in (386230,1026757,671515,1001748,1002989,1026639,1026634,1028815,1029088,459108,562176,1205512,1779540,1029029,1914195,1028861,1006064,999679,1329210,1005469,1005477,1026067,1028489,1001621,1051465,1000770,1026826,1051466,1000273,1005777,1000893,999510,998713,1026835,1005493,1000638,1118393,999450,1914203,999451,1002439,2089921,1914202,1033401,2077210,1730378,1730377,1730386,1033409,1730384,999697,1734632,1232017,1629494,1232162,1231209,1231096,1629497,2068214,1231338,1232051,1232167,1231215,1231839))"
          }
        },
        {
          "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",
              "maniamall_cscart.cscart_products_categories.product_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 105,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "104.18",
              "eval_cost": "10.50",
              "prefix_cost": "136.68",
              "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": [
              "maniamall_cscart.cscart_products_categories.category_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 5,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "80.50",
              "eval_cost": "0.53",
              "prefix_cost": "227.68",
              "data_read_per_join": "13K"
            },
            "used_columns": [
              "category_id",
              "company_id",
              "usergroup_ids",
              "status"
            ],
            "attached_condition": "((`maniamall_cscart`.`cscart_categories`.`company_id` = 1) and ((`maniamall_cscart`.`cscart_categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`maniamall_cscart`.`cscart_categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`maniamall_cscart`.`cscart_categories`.`usergroup_ids`))) and (`maniamall_cscart`.`cscart_categories`.`status` in ('A','H')))"
          }
        }
      ]
    }
  }
}

Result