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 (
    86857, 84695, 88937, 87877, 85037, 91870, 
    90124, 90125, 90127, 90128, 88515, 
    88518, 87282, 87290, 88514, 88517, 
    88511, 91869, 91871, 91872, 91873, 
    91874, 91875, 85038, 92458, 92459, 
    92460, 92461, 92462, 92463, 92464, 
    92489, 92490, 92491, 92492, 92493, 
    92494, 92495, 86892, 90325, 88149, 
    88587, 92211, 92216, 92218, 92219, 
    92220, 92221, 92222, 92224, 92225, 
    92226, 92467, 84241, 89169, 89172, 
    87876, 88506, 88507, 88510, 87234, 
    87235, 87236, 87239, 87240, 87241, 
    87244, 87245, 87246, 87249, 87250, 
    87251, 86894, 90828, 92409, 92410, 
    92411, 92412, 92413, 92414, 92415, 
    92232, 92233, 92234, 92235, 92236, 
    92238, 92468, 92472, 92473, 92474, 
    82338, 84240, 88588, 84239, 82337
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00135

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "167.78"
    },
    "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": 295,
            "rows_produced_per_join": 295,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "29.87",
              "eval_cost": "29.50",
              "prefix_cost": "59.37",
              "data_read_per_join": "4K"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ],
            "attached_condition": "(`nuie_scalesta_net`.`cscart_products_categories`.`product_id` in (86857,84695,88937,87877,85037,91870,90124,90125,90127,90128,88515,88518,87282,87290,88514,88517,88511,91869,91871,91872,91873,91874,91875,85038,92458,92459,92460,92461,92462,92463,92464,92489,92490,92491,92492,92493,92494,92495,86892,90325,88149,88587,92211,92216,92218,92219,92220,92221,92222,92224,92225,92226,92467,84241,89169,89172,87876,88506,88507,88510,87234,87235,87236,87239,87240,87241,87244,87245,87246,87249,87250,87251,86894,90828,92409,92410,92411,92412,92413,92414,92415,92232,92233,92234,92235,92236,92238,92468,92472,92473,92474,82338,84240,88588,84239,82337))"
          }
        },
        {
          "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": 14,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "73.75",
              "eval_cost": "1.48",
              "prefix_cost": "162.62",
              "data_read_per_join": "38K"
            },
            "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": 14,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "3.69",
              "eval_cost": "1.48",
              "prefix_cost": "167.78",
              "data_read_per_join": "236"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "position"
            ]
          }
        }
      ]
    }
  }
}