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 (
    1732000, 1661266, 1731933, 1658668, 
    1658805, 2539827, 1661450, 1658400, 
    1661340, 2072334, 1659066, 1163013, 
    1663387, 2253671, 1659620, 1658630, 
    1659207, 1658657, 1657931, 1661143, 
    1732317, 1664148, 1732319, 1657915, 
    1659840, 1658338, 1658635, 1658606, 
    1658866, 1658169, 1660818, 1659802, 
    1659293, 1660499, 1658645, 1661096
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00335

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "53.39"
    },
    "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": 43,
            "rows_produced_per_join": 43,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "5.30",
              "eval_cost": "4.30",
              "prefix_cost": "9.60",
              "data_read_per_join": "688"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ],
            "attached_condition": "(`maniamall_cscart`.`cscart_products_categories`.`product_id` in (1732000,1661266,1731933,1658668,1658805,2539827,1661450,1658400,1661340,2072334,1659066,1163013,1663387,2253671,1659620,1658630,1659207,1658657,1657931,1661143,1732317,1664148,1732319,1657915,1659840,1658338,1658635,1658606,1658866,1658169,1660818,1659802,1659293,1660499,1658645,1661096))"
          }
        },
        {
          "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": 2,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "37.27",
              "eval_cost": "0.22",
              "prefix_cost": "51.16",
              "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": 2,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "2.02",
              "eval_cost": "0.22",
              "prefix_cost": "53.39",
              "data_read_per_join": "34"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "position"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id category_ids position
1163013 32420M
1657915 32420M
1657931 32420M
1658169 32420M
1658338 32420M
1658400 32420M
1658606 32420M
1658630 32420M
1658635 32420M
1658645 32420M
1658657 32420M
1658668 32420M
1658805 32420M
1658866 32420M
1659066 32420M
1659207 32420M
1659293 32420M
1659620 32420M
1659802 32420M
1659840 32420M
1660499 32420M
1660818 32420M
1661096 32420M
1661143 32420M
1661266 32420M
1661340 32420M
1661450 32420M
1663387 32420M
1664148 32420M
1731933 32420,9712M
1732000 32420,23771M
1732317 32420,3124M
1732319 32420,3124M
2072334 8354,4528M 0
2253671 5549,4528M 0
2539827 4528,4496M 0