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 = 290 
WHERE 
  cscart_products_categories.product_id IN (
    1232946, 600081, 591543, 611228, 591145, 
    611260, 1779540, 594090, 621042, 459123, 
    592444, 589118, 1232940, 1232908, 
    594006, 595804, 866378, 590566, 592825, 
    796380, 698317, 1233022, 594292, 1232971, 
    612103, 589509, 1232911, 866376, 1233039, 
    589731, 1068615, 696911, 590525, 611377, 
    590341, 614832
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00471

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "132.26"
    },
    "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": 62,
            "rows_produced_per_join": 62,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "7.16",
              "eval_cost": "6.20",
              "prefix_cost": "13.36",
              "data_read_per_join": "992"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ],
            "attached_condition": "(`maniamall_cscart`.`cscart_products_categories`.`product_id` in (1232946,600081,591543,611228,591145,611260,1779540,594090,621042,459123,592444,589118,1232940,1232908,594006,595804,866378,590566,592825,796380,698317,1233022,594292,1232971,612103,589509,1232911,866376,1233039,589731,1068615,696911,590525,611377,590341,614832))"
          }
        },
        {
          "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": 62,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "61.04",
              "eval_cost": "6.20",
              "prefix_cost": "80.60",
              "data_read_per_join": "992"
            },
            "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": 3,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "45.47",
              "eval_cost": "0.31",
              "prefix_cost": "132.27",
              "data_read_per_join": "8K"
            },
            "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
459123 4528,5257,2689M
589118 8203M
589509 8192M
589731 8371M
590341 8392M
590525 8186M
590566 8320M
591145 8445M
591543 32420,8449M
592444 8337M
592825 8509M
594006 43883M
594090 8562M
594292 8572M
595804 8268M
600081 8680M
611228 32420,3209M
611260 32420,7898M
611377 32420,3281M
612103 8425,32420,42632,3174M
614832 32420,42632,7907M
621042 8771,8772,8776M
696911 3158M
698317 32420,42632,3205M
796380 4528,8618,11611M
866376 4528,8184M
866378 4528,8184M
1068615 20534M
1232908 4528,16886M
1232911 4528,16883M
1232940 4528,16890M
1232946 4528,16840M
1232971 4528,16895M
1233022 16892M
1233039 16883M
1779540 4528,14864,28866M