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.storefront_id IN (0, 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 = 7238 
WHERE 
  cscart_products_categories.product_id IN (
    89721, 86874, 86872, 86875, 86883, 86870, 
    86868, 86871, 86873, 86869, 86881, 
    86878, 86879, 89720, 86876, 86886, 
    89719, 89722, 86877, 86882, 86884, 
    86885, 89579, 89581, 89583, 89585, 
    89587, 89578, 89580, 89582, 89584, 
    89586, 89588, 89577, 85567, 89576, 
    94024, 94025, 94429, 94430, 94431, 
    94432, 94433, 94434, 94441, 95100, 
    95558, 95559, 95560, 95638, 95640, 
    95645, 95646, 95653, 95790, 95933, 
    95936, 95940, 96522, 96523, 96524, 
    96525, 96526, 96527, 96528, 96677, 
    96679, 96684, 96685, 96693, 98445, 
    98682, 99507, 99511, 99515, 100361, 
    100362, 100363, 100364, 100365, 100366, 
    100367, 100368, 100369, 100370, 100371, 
    100372, 100373, 100384, 100385, 101047, 
    101048, 101049, 101050, 101051, 101052
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.02402

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "86.69"
    },
    "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": 96,
            "rows_produced_per_join": 96,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "9.89",
              "eval_cost": "9.60",
              "prefix_cost": "19.49",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ],
            "attached_condition": "(`nuie_scalesta_net`.`cscart_products_categories`.`product_id` in (89721,86874,86872,86875,86883,86870,86868,86871,86873,86869,86881,86878,86879,89720,86876,86886,89719,89722,86877,86882,86884,86885,89579,89581,89583,89585,89587,89578,89580,89582,89584,89586,89588,89577,85567,89576,94024,94025,94429,94430,94431,94432,94433,94434,94441,95100,95558,95559,95560,95638,95640,95645,95646,95653,95790,95933,95936,95940,96522,96523,96524,96525,96526,96527,96528,96677,96679,96684,96685,96693,98445,98682,99507,99511,99515,100361,100362,100363,100364,100365,100366,100367,100368,100369,100370,100371,100372,100373,100384,100385,101047,101048,101049,101050,101051,101052))"
          }
        },
        {
          "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",
              "nuie_scalesta_net.cscart_products_categories.product_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 96,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "24.00",
              "eval_cost": "9.60",
              "prefix_cost": "53.09",
              "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": [
              "nuie_scalesta_net.cscart_products_categories.category_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 4,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "24.00",
              "eval_cost": "0.48",
              "prefix_cost": "86.69",
              "data_read_per_join": "12K"
            },
            "used_columns": [
              "category_id",
              "usergroup_ids",
              "status",
              "storefront_id"
            ],
            "attached_condition": "((`nuie_scalesta_net`.`cscart_categories`.`storefront_id` in (0,1)) and ((`nuie_scalesta_net`.`cscart_categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`nuie_scalesta_net`.`cscart_categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`nuie_scalesta_net`.`cscart_categories`.`usergroup_ids`))) and (`nuie_scalesta_net`.`cscart_categories`.`status` in ('A','H')))"
          }
        }
      ]
    }
  }
}

Result

product_id category_ids position
85567 7291M
86868 7239M
86869 7239M
86870 7239M
86871 7239M
86872 7239M
86873 7239M
86874 7239M
86875 7239M
86876 7239M
86877 7239M
86878 7239M
86879 7239M
86881 7239M
86882 7239M
86883 7239M
86884 7239M
86885 7239M
86886 7239M
89576 7291M
89577 7291M
89578 7291M
89579 7291M
89580 7291M
89581 7291M
89582 7291M
89583 7291M
89584 7291M
89585 7291M
89586 7291M
89587 7291M
89588 7291M
89719 7239M
89720 7239M
89721 7239M
89722 7239M
94024 7291M
94025 7291M
94429 7239M
94430 7239M
94431 7239M
94432 7239M
94433 7239M
94434 7239M
94441 7239M
95100 7238M 0
95558 7291M
95559 7291M
95560 7291M
95638 7291M
95640 7239M
95645 7291M
95646 7291M
95653 7291M
95790 7239M
95933 7291M
95936 7291M
95940 7291M
96522 7239M
96523 7239M
96524 7239M
96525 7239M
96526 7239M
96527 7239M
96528 7239M
96677 7291M
96679 7239M
96684 7291M
96685 7291M
96693 7291M
98445 7291M
98682 7239M
99507 7291M
99511 7291M
99515 7291M
100361 7291M
100362 7291M
100363 7291M
100364 7291M
100365 7291M
100366 7291M
100367 7291M
100368 7291M
100369 7291M
100370 7291M
100371 7291M
100372 7291M
100373 7291M
100384 7291M
100385 7291M
101047 7239M
101048 7239M
101049 7239M
101050 7239M
101051 7239M
101052 7239M