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 = 1098 
WHERE 
  cscart_products_categories.product_id IN (
    480519, 524885, 697773, 866373, 1118377, 
    697683, 612103, 880671, 1434961, 694679, 
    822677, 614913, 301401, 822659, 613219, 
    613221, 1025388, 822679, 1025367, 
    1025365, 1230911, 1338749, 1731846, 
    1732251, 359020, 1153153, 1153155, 
    1338924, 1231165, 1731742, 1732158, 
    1231166, 1732357, 1153734, 1732013, 
    1663556
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00343

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "182.34"
    },
    "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": 84,
            "rows_produced_per_join": 84,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "9.40",
              "eval_cost": "8.40",
              "prefix_cost": "17.80",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ],
            "attached_condition": "(`maniamall_cscart`.`cscart_products_categories`.`product_id` in (480519,524885,697773,866373,1118377,697683,612103,880671,1434961,694679,822677,614913,301401,822659,613219,613221,1025388,822679,1025367,1025365,1230911,1338749,1731846,1732251,359020,1153153,1153155,1338924,1231165,1731742,1732158,1231166,1732357,1153734,1732013,1663556))"
          }
        },
        {
          "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": 84,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "83.35",
              "eval_cost": "8.40",
              "prefix_cost": "109.54",
              "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": 4,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "64.40",
              "eval_cost": "0.42",
              "prefix_cost": "182.34",
              "data_read_per_join": "10K"
            },
            "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

product_id category_ids position
301401 4528,5862
359020 4528,6381
480519 4528,14541,1571M
524885 4528,7127M
612103 8425,32420,42632,3174M
613219 32420,3154M
613221 32420,3154M
614913 32420,3154M
694679 32420,3129M
697683 32420,42632,3154M
697773 32420,3126M
822659 4528,7127M
822677 4528,42632,5870M
822679 1571,4528,2799M
866373 1152,4528,2920M
880671 4528,5870M
1025365 16582M
1025367 16582M
1025388 16582M
1118377 11422,32420,21168M
1153153 4528,11590,10976M
1153155 4528,11590,10976M
1153734 4528,23281M
1230911 4528,5842M
1231165 32420,16864M
1231166 32420,16864M
1338749 3247,4528,32420,21065M
1338924 3206,4528,32420,23310M
1434961 4528,42632,7128M
1663556 10887M
1731742 32420,3174M
1731846 32420,3174M
1732013 32420,37477M
1732158 3154M
1732251 3119,32420,1165M
1732357 3119,32420,1165M