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 = 4528 
WHERE 
  cscart_products_categories.product_id IN (
    1662418, 1659922, 1661347, 1657700, 
    1658556, 1659797, 1659562, 1657765, 
    1657745, 1661374, 1915737, 1659943, 
    1661339, 1659257, 1660558, 1659816, 
    2591113, 1658321, 1662787, 1664138, 
    1660123, 1664236, 1661363, 1916044, 
    1660260, 1657702, 1660929, 1663152, 
    2596602, 2099753, 1658439, 1663290, 
    1661128, 1658521, 1658562, 2068216
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.02414

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "49.16"
    },
    "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": 39,
            "rows_produced_per_join": 39,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "4.87",
              "eval_cost": "3.90",
              "prefix_cost": "8.77",
              "data_read_per_join": "624"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ],
            "attached_condition": "(`maniamall_cscart`.`cscart_products_categories`.`product_id` in (1662418,1659922,1661347,1657700,1658556,1659797,1659562,1657765,1657745,1661374,1915737,1659943,1661339,1659257,1660558,1659816,2591113,1658321,1662787,1664138,1660123,1664236,1661363,1916044,1660260,1657702,1660929,1663152,2596602,2099753,1658439,1663290,1661128,1658521,1658562,2068216))"
          }
        },
        {
          "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": 1,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "34.45",
              "eval_cost": "0.20",
              "prefix_cost": "47.12",
              "data_read_per_join": "5K"
            },
            "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')))"
          }
        },
        {
          "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": 1,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.84",
              "eval_cost": "0.20",
              "prefix_cost": "49.16",
              "data_read_per_join": "31"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "position"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id category_ids position
1657700 32420M
1657702 32420M
1657745 32420M
1657765 32420M
1658321 32420M
1658439 32420M
1658521 32420M
1658556 32420M
1658562 32420M
1659257 32420M
1659562 32420M
1659797 32420M
1659816 32420M
1659922 32420M
1659943 32420M
1660123 32420M
1660260 32420M
1660558 32420M
1660929 32420M
1661128 32420M
1661339 32420M
1661347 32420M
1661363 32420M
1661374 32420M
1662418 32420M
1662787 32420M
1663152 32420M
1663290 32420M
1664138 32420M
1664236 32420M
1915737 32420M
1916044 32420M
2068216 8786,4528M 0
2099753 32420M
2591113 5549,4528M 0
2596602 6381,4528M 0