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 = 7211 
WHERE 
  cscart_products_categories.product_id IN (
    99115, 99116, 99118, 99119, 99122, 99124, 
    99126, 99129, 99130, 99132, 99134, 
    99143, 99163, 99164, 99166, 99172, 
    99173, 99175, 99178, 99180, 99184, 
    99192, 99194, 99197, 99200, 99204, 
    99207, 99209, 99223, 99224, 99225, 
    99226, 99228, 99229, 99230, 99231, 
    99232, 99244, 99245, 99246, 99247, 
    99253, 99260, 99261, 99271, 99272, 
    99273, 99274, 99276, 99277, 99278, 
    99280, 99281, 99324, 99325, 99326, 
    99327, 99328, 99329, 99344, 99422, 
    99423, 99424, 99425, 99426, 99427, 
    99428, 99429, 99430, 99431, 99432, 
    99433, 99434, 99435, 99436, 99437, 
    99529, 99530, 99531, 99532, 99533, 
    99534, 99535, 99536, 99537, 99538, 
    99539, 99540, 99541, 99542, 99543, 
    99544, 99545, 99546, 99547, 99548
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.01672

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "137.39"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "11.59"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_categories",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "p_category_id"
            ],
            "rows_examined_per_scan": 208,
            "rows_produced_per_join": 8,
            "filtered": "4.00",
            "cost_info": {
              "read_cost": "20.72",
              "eval_cost": "0.83",
              "prefix_cost": "21.55",
              "data_read_per_join": "21K"
            },
            "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": "cscart_products_categories",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "link_type",
              "pt"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id"
            ],
            "key_length": "3",
            "ref": [
              "nuie_scalesta_net.cscart_categories.category_id"
            ],
            "rows_examined_per_scan": 117,
            "rows_produced_per_join": 11,
            "filtered": "1.18",
            "cost_info": {
              "read_cost": "2.33",
              "eval_cost": "1.16",
              "prefix_cost": "121.75",
              "data_read_per_join": "185"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ],
            "attached_condition": "(`nuie_scalesta_net`.`cscart_products_categories`.`product_id` in (99115,99116,99118,99119,99122,99124,99126,99129,99130,99132,99134,99143,99163,99164,99166,99172,99173,99175,99178,99180,99184,99192,99194,99197,99200,99204,99207,99209,99223,99224,99225,99226,99228,99229,99230,99231,99232,99244,99245,99246,99247,99253,99260,99261,99271,99272,99273,99274,99276,99277,99278,99280,99281,99324,99325,99326,99327,99328,99329,99344,99422,99423,99424,99425,99426,99427,99428,99429,99430,99431,99432,99433,99434,99435,99436,99437,99529,99530,99531,99532,99533,99534,99535,99536,99537,99538,99539,99540,99541,99542,99543,99544,99545,99546,99547,99548))"
          }
        },
        {
          "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": 11,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "2.90",
              "eval_cost": "1.16",
              "prefix_cost": "125.80",
              "data_read_per_join": "185"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "position"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id category_ids position
99115 7260M,7320
99116 7260M,7320
99118 7260M,7320
99119 7260M,7320
99122 7259M,7345,7347
99124 7259M,7345,7347
99126 7259M,7345,7347
99129 7259M,7345,7347
99130 7260M,7320
99132 7260M,7320
99134 7260M,7320
99143 7260M,7320
99163 7259M,7345,7347
99164 7260M,7320
99166 7260M,7320
99172 7259M,7345,7347
99173 7260M,7320
99175 7260M,7320
99178 7259M,7345,7347
99180 7260M,7320
99184 7259M,7345,7347
99192 7260M,7320
99194 7260M,7320
99197 7260M,7320
99200 7260M,7320
99204 7259M,7345,7347
99207 7259M,7345,7347
99209 7260M,7320
99223 7259M,7345,7347
99224 7259M,7345,7347
99225 7259M,7345,7347
99226 7259M,7345,7347
99228 7259M,7345,7347
99229 7259M,7345,7347
99230 7260M,7320
99231 7260M,7320
99232 7260M,7320
99244 7259M,7345,7347
99245 7260M,7320
99246 7260M,7320
99247 7260M,7320
99253 7259M,7345,7347
99260 7259M,7345,7347
99261 7260M,7320
99271 7259M,7345,7347
99272 7260M,7320
99273 7260M,7320
99274 7260M,7320
99276 7260M,7320
99277 7260M,7320
99278 7260M,7320
99280 7259M,7345,7347
99281 7259M,7345,7347
99324 7208M,7241,7309,7346
99325 7208M,7241,7309,7346
99326 7208M,7241,7309,7346
99327 7208M,7241,7309,7346
99328 7208M,7241,7309,7346
99329 7208M,7241,7309,7346
99344 7260M,7320
99422 7208M,7241,7309,7346
99423 7208M,7241,7309,7346
99424 7208M,7241,7309,7346
99425 7208M,7241,7309,7346
99426 7208M,7241,7309,7346
99427 7208M,7241,7309,7346
99428 7208M,7241,7309,7346
99429 7208M,7241,7309,7346
99430 7208M,7241,7309,7346
99431 7208M,7241,7309,7346
99432 7208M,7241,7309,7346
99433 7208M,7241,7309,7346
99434 7208M,7241,7309,7346
99435 7208M,7241,7309,7346
99436 7208M,7241,7309,7346
99437 7208M,7241,7309,7346
99529 7208M,7241,7309,7346
99530 7208M,7241,7309,7346
99531 7208M,7241,7309,7346
99532 7208M,7241,7309,7346
99533 7208M,7241,7309,7346
99534 7208M,7241,7309,7346
99535 7208M,7241,7309,7346
99536 7208M,7241,7309,7346
99537 7208M,7241,7309,7346
99538 7208M,7241,7309,7346
99539 7208M,7241,7309,7346
99540 7208M,7241,7309,7346
99541 7208M,7241,7309,7346
99542 7208M,7241,7309,7346
99543 7208M,7241,7309,7346
99544 7208M,7241,7309,7346
99545 7208M,7241,7309,7346
99546 7208M,7241,7309,7346
99547 7208M,7241,7309,7346
99548 7208M,7241,7309,7346