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 = 2910 
WHERE 
  cscart_products_categories.product_id IN (
    353532, 356902, 357282, 350575, 355728, 
    356167, 350926, 357286, 356512, 349790, 
    351099, 354282, 358480, 350638, 350688, 
    353865, 357710, 352980, 353206, 355154, 
    358261, 351951, 352073, 351462, 352653, 
    354290, 351935, 352811, 352873, 354375, 
    359089, 359114, 350517, 355847, 349813, 
    352946, 353520, 353681, 354627, 354811, 
    359311, 351432, 355249, 358215, 354018, 
    354435, 354914, 356477, 357143, 349716, 
    350536, 352173, 352739, 353489, 350939, 
    357331, 352468, 356663, 357269, 350547, 
    351953, 350816, 354295, 357466, 358621, 
    349648, 351487, 356045, 351853, 356054, 
    356596, 356682, 358680, 351050, 352974, 
    357372, 358921, 355190, 351314, 352331, 
    353884, 358851, 352036, 357127, 358158, 
    351164, 351319, 356338, 358667, 353173, 
    354778, 355935, 351970, 353344, 355203, 
    356622, 350069, 350576, 351389, 355200
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00326

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "281.15"
    },
    "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": 100,
            "rows_produced_per_join": 100,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "21.15",
              "eval_cost": "20.00",
              "prefix_cost": "41.15",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ],
            "attached_condition": "(`webmarco`.`cscart_products_categories`.`product_id` in (353532,356902,357282,350575,355728,356167,350926,357286,356512,349790,351099,354282,358480,350638,350688,353865,357710,352980,353206,355154,358261,351951,352073,351462,352653,354290,351935,352811,352873,354375,359089,359114,350517,355847,349813,352946,353520,353681,354627,354811,359311,351432,355249,358215,354018,354435,354914,356477,357143,349716,350536,352173,352739,353489,350939,357331,352468,356663,357269,350547,351953,350816,354295,357466,358621,349648,351487,356045,351853,356054,356596,356682,358680,351050,352974,357372,358921,355190,351314,352331,353884,358851,352036,357127,358158,351164,351319,356338,358667,353173,354778,355935,351970,353344,355203,356622,350069,350576,351389,355200))"
          }
        },
        {
          "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",
              "webmarco.cscart_products_categories.product_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 100,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "100.00",
              "eval_cost": "20.00",
              "prefix_cost": "161.15",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "position"
            ]
          }
        },
        {
          "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": [
              "webmarco.cscart_products_categories.category_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 5,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "100.00",
              "eval_cost": "1.00",
              "prefix_cost": "281.15",
              "data_read_per_join": "29K"
            },
            "used_columns": [
              "category_id",
              "usergroup_ids",
              "status",
              "storefront_id"
            ],
            "attached_condition": "((`webmarco`.`cscart_categories`.`storefront_id` in (0,1)) and ((`webmarco`.`cscart_categories`.`usergroup_ids` = '') or find_in_set(0,`webmarco`.`cscart_categories`.`usergroup_ids`) or find_in_set(1,`webmarco`.`cscart_categories`.`usergroup_ids`)) and (`webmarco`.`cscart_categories`.`status` in ('A','H')))"
          }
        }
      ]
    }
  }
}