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 = 7170 
WHERE 
  cscart_products_categories.product_id IN (
    90997, 85063, 85149, 85195, 85241, 83564, 
    83565, 83077, 83574, 90913, 90992, 
    83057, 83058, 83606, 83607, 89825, 
    89826, 89877, 89878, 89949, 89950, 
    90893, 90894, 90972, 90973, 91515, 
    91516, 85060, 85146, 85192, 85238, 
    86134, 86136, 86157, 86159, 86642, 
    86644, 86711, 86713, 82632, 83529, 
    84543, 84544, 84609, 84610, 85126, 
    86183, 86206, 86507, 86783, 87294, 
    87295, 89078, 90003, 85103, 86131, 
    86154, 86639, 86708, 85850, 85851, 
    85860, 85861, 85089, 85175, 85221, 
    85267, 85100, 83078, 83558, 83563, 
    83575, 90914, 90993, 85061, 85147, 
    85193, 85239, 87314, 87315, 87326, 
    87327, 87338, 87339, 87350, 87351, 
    84324, 84325, 85319, 85416, 85467, 
    85518, 86608, 86677, 82636, 82637
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00285

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 (90997,85063,85149,85195,85241,83564,83565,83077,83574,90913,90992,83057,83058,83606,83607,89825,89826,89877,89878,89949,89950,90893,90894,90972,90973,91515,91516,85060,85146,85192,85238,86134,86136,86157,86159,86642,86644,86711,86713,82632,83529,84543,84544,84609,84610,85126,86183,86206,86507,86783,87294,87295,89078,90003,85103,86131,86154,86639,86708,85850,85851,85860,85861,85089,85175,85221,85267,85100,83078,83558,83563,83575,90914,90993,85061,85147,85193,85239,87314,87315,87326,87327,87338,87339,87350,87351,84324,84325,85319,85416,85467,85518,86608,86677,82636,82637))"
          }
        },
        {
          "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"
            ]
          }
        }
      ]
    }
  }
}