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 = 7254 
WHERE 
  cscart_products_categories.product_id IN (
    97147, 97148, 97149, 97150, 97151, 97152, 
    97153, 97154, 97155, 97156, 97157, 
    97158, 97159, 97160, 97161, 97162, 
    97163, 97164, 97165, 97166, 97167, 
    97168, 97169, 97170, 97171, 97172, 
    97173, 97174, 97175, 97176, 97177, 
    97178, 97179, 97180, 97181, 97182, 
    97183, 97184, 97185, 97186, 97187, 
    97188, 97189, 97190, 97191, 97192, 
    97193, 97194, 97195, 97196, 97197, 
    97198, 97199, 97200, 97201, 97202, 
    97203, 97204, 97205, 97206, 97207, 
    97208, 97209, 97210, 97211, 97212, 
    97213, 97214, 97215, 97216, 97217, 
    97218, 97228, 97229, 97230, 97231, 
    97232, 97233, 97234, 97235, 97236, 
    97237, 97238, 97239, 97240, 97241, 
    97242, 97243, 97244, 97245, 97246, 
    97247, 97248, 97249, 97250, 97251
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00308

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "54.77"
    },
    "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 (97147,97148,97149,97150,97151,97152,97153,97154,97155,97156,97157,97158,97159,97160,97161,97162,97163,97164,97165,97166,97167,97168,97169,97170,97171,97172,97173,97174,97175,97176,97177,97178,97179,97180,97181,97182,97183,97184,97185,97186,97187,97188,97189,97190,97191,97192,97193,97194,97195,97196,97197,97198,97199,97200,97201,97202,97203,97204,97205,97206,97207,97208,97209,97210,97211,97212,97213,97214,97215,97216,97217,97218,97228,97229,97230,97231,97232,97233,97234,97235,97236,97237,97238,97239,97240,97241,97242,97243,97244,97245,97246,97247,97248,97249,97250,97251))"
          }
        },
        {
          "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": "53.09",
              "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')))"
          }
        },
        {
          "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": 4,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.20",
              "eval_cost": "0.48",
              "prefix_cost": "54.77",
              "data_read_per_join": "76"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "position"
            ]
          }
        }
      ]
    }
  }
}