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 = 7148 
WHERE 
  cscart_products_categories.product_id IN (
    98542, 98543, 98544, 98545, 98546, 98547, 
    98548, 98549, 98557, 98558, 98559, 
    98563, 98564, 98565, 98566, 98567, 
    98568, 98569, 98690, 98693, 98694, 
    98758, 98759, 98762, 98769, 98770, 
    98771, 98772, 98773, 98774, 98775, 
    98776, 98777, 98778, 98779, 98780, 
    98781, 98782, 98783, 98784, 98785, 
    98786, 98787, 98788, 98789, 98790, 
    98791, 98792
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00187

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "91.12"
    },
    "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": 160,
            "rows_produced_per_join": 160,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "16.32",
              "eval_cost": "16.00",
              "prefix_cost": "32.32",
              "data_read_per_join": "2K"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ],
            "attached_condition": "(`nuie_scalesta_net`.`cscart_products_categories`.`product_id` in (98542,98543,98544,98545,98546,98547,98548,98549,98557,98558,98559,98563,98564,98565,98566,98567,98568,98569,98690,98693,98694,98758,98759,98762,98769,98770,98771,98772,98773,98774,98775,98776,98777,98778,98779,98780,98781,98782,98783,98784,98785,98786,98787,98788,98789,98790,98791,98792))"
          }
        },
        {
          "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": 8,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "40.00",
              "eval_cost": "0.80",
              "prefix_cost": "88.32",
              "data_read_per_join": "20K"
            },
            "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')))"
          }
        },
        {
          "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": 8,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "2.00",
              "eval_cost": "0.80",
              "prefix_cost": "91.12",
              "data_read_per_join": "128"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "position"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id category_ids position
98542 7183,7283,7167M
98543 7183,7283,7167M
98544 7183,7283,7167M
98545 7183,7283,7167M
98546 7183,7283,7167M
98547 7183,7283,7167M
98548 7183,7283,7167M
98549 7183,7283,7167M
98557 7241,7309,7346,7208M
98558 7241,7309,7346,7208M
98559 7241,7309,7346,7208M
98563 7226,7225M
98564 7226,7225M
98565 7226,7225M
98566 7226,7225M
98567 7226,7225M
98568 7226,7225M
98569 7226,7225M
98690 7301,7151M
98693 7301,7151M
98694 7301,7151M
98758 7182,7300,7149M
98759 7341,7342,7343,7157M
98762 7263M
98769 7341,7342,7343,7157M
98770 7341,7342,7343,7157M
98771 7341,7342,7343,7157M
98772 7341,7342,7343,7157M
98773 7241,7309,7346,7208M
98774 7241,7309,7346,7208M
98775 7241,7309,7346,7208M
98776 7241,7309,7346,7208M
98777 7241,7309,7346,7208M
98778 7241,7309,7346,7208M
98779 7241,7309,7346,7208M
98780 7241,7309,7346,7208M
98781 7241,7309,7346,7208M
98782 7241,7309,7346,7208M
98783 7241,7309,7346,7208M
98784 7241,7309,7346,7208M
98785 7241,7309,7346,7208M
98786 7241,7309,7346,7208M
98787 7241,7309,7346,7208M
98788 7241,7309,7346,7208M
98789 7241,7309,7346,7208M
98790 7241,7309,7346,7208M
98791 7241,7309,7346,7208M
98792 7241,7309,7346,7208M