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 = 265 
WHERE 
  cscart_products_categories.product_id IN (
    10556, 
    10101, 
    10558, 
    10142, 
    10134, 
    10141, 
    10053, 
    10138, 
    10130, 
    9934, 
    10563, 
    10569, 
    10572, 
    10602, 
    10603, 
    10137, 
    12517, 
    12531, 
    12526, 
    12509, 
    10226, 
    10551, 
    10550, 
    10193, 
    10659, 
    10664, 
    10666, 
    10661, 
    10663, 
    10190, 
    10657, 
    10658, 
    10194, 
    10692, 
    12530, 
    12516, 
    12496, 
    12519, 
    12510, 
    10087, 
    10100, 
    10698, 
    10478, 
    9883, 
    9901, 
    10493, 
    10485, 
    10476, 
    10479, 
    10494, 
    10492, 
    10468, 
    9903, 
    10487, 
    9899, 
    10473, 
    10486, 
    9881, 
    9896, 
    9892, 
    10481, 
    9876, 
    10474, 
    10491, 
    10489, 
    10471, 
    9880, 
    10472, 
    9885, 
    10488, 
    9897, 
    9895, 
    10467, 
    9900, 
    10470, 
    10466, 
    10477, 
    9889, 
    10475, 
    9890, 
    10483, 
    10495, 
    10490, 
    10469, 
    9877, 
    9894, 
    9891, 
    10480, 
    9893, 
    9878, 
    9886, 
    9882, 
    9888, 
    10482, 
    13091, 
    12551, 
    12638, 
    12634, 
    12636, 
    12635, 
    12599, 
    12627, 
    12600, 
    12639, 
    15800, 
    12595, 
    12587, 
    12593, 
    12620, 
    12623, 
    12619, 
    12585, 
    12616, 
    12601, 
    12628, 
    12602, 
    12640, 
    12647, 
    12588, 
    12605, 
    12606, 
    12637, 
    12612, 
    12613, 
    12641, 
    12554, 
    12552, 
    12642
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00300

JSON explain

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "cscart_products_categories",
      "access_type": "range",
      "possible_keys": ["PRIMARY", "pt"],
      "key": "pt",
      "key_length": "3",
      "used_key_parts": ["product_id"],
      "rows": 176,
      "filtered": 100,
      "index_condition": "cscart_products_categories.product_id in (10556,10101,10558,10142,10134,10141,10053,10138,10130,9934,10563,10569,10572,10602,10603,10137,12517,12531,12526,12509,10226,10551,10550,10193,10659,10664,10666,10661,10663,10190,10657,10658,10194,10692,12530,12516,12496,12519,12510,10087,10100,10698,10478,9883,9901,10493,10485,10476,10479,10494,10492,10468,9903,10487,9899,10473,10486,9881,9896,9892,10481,9876,10474,10491,10489,10471,9880,10472,9885,10488,9897,9895,10467,9900,10470,10466,10477,9889,10475,9890,10483,10495,10490,10469,9877,9894,9891,10480,9893,9878,9886,9882,9888,10482,13091,12551,12638,12634,12636,12635,12599,12627,12600,12639,15800,12595,12587,12593,12620,12623,12619,12585,12616,12601,12628,12602,12640,12647,12588,12605,12606,12637,12612,12613,12641,12554,12552,12642)"
    },
    "table": {
      "table_name": "product_position_source",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY", "pt"],
      "key": "PRIMARY",
      "key_length": "6",
      "used_key_parts": ["category_id", "product_id"],
      "ref": [
        "const",
        "noframes_angrybeaver.cscart_products_categories.product_id"
      ],
      "rows": 1,
      "filtered": 100
    },
    "table": {
      "table_name": "cscart_categories",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY", "c_status", "p_category_id"],
      "key": "PRIMARY",
      "key_length": "3",
      "used_key_parts": ["category_id"],
      "ref": ["noframes_angrybeaver.cscart_products_categories.category_id"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "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')"
    }
  }
}

Result

product_id category_ids position
9876 327M
9877 327M
9878 327M
9880 327M
9881 327M
9882 327M
9883 327M
9885 327M
9886 327M
9888 327M
9889 327M
9890 327M
9891 327M
9892 327M
9893 327M
9894 327M
9895 327M
9896 327M
9897 327M
9899 327M
9900 327M
9901 327M
9903 327M
9934 275M
10053 275M
10087 275M
10100 275M
10101 275M
10130 275M
10134 275M
10137 275M
10138 275M
10141 275M
10142 275M
10190 275M
10193 275M
10194 275M
10226 275M
10466 327M
10467 327M
10468 327M
10469 327M
10470 327M
10471 327M
10472 327M
10473 327M
10474 327M
10475 327M
10476 327M
10477 327M
10478 327M
10479 327M
10480 327M
10481 327M
10482 327M
10483 327M
10485 327M
10486 327M
10487 327M
10488 327M
10489 327M
10490 327M
10491 327M
10492 327M
10493 327M
10494 327M
10495 327M
10550 275M
10551 275M
10556 275M
10558 275M
10563 275M
10569 275M
10572 275M
10602 275M
10603 275M
10657 275M
10658 275M
10659 275M
10661 275M
10663 275M
10664 275M
10666 275M
10692 275M
10698 275M
12496 275M
12509 275M
12510 275M
12516 275M
12517 275M
12519 275M
12526 275M
12530 275M
12531 275M
12551 267,270,268M
12552 267,270,268M
12554 267,270,268M
12585 270,267,1688M
12587 270,267,1688M
12588 285,270,267M
12593 270,267,1688M
12595 270,267,1688M
12599 277,267,270M
12600 277,267,270M
12601 267,270,277M
12602 270,277,267M
12605 267,277,270M
12606 277,270,267M
12612 270,267,277M
12613 270,267,277M
12616 277,270,267M
12619 267,270,1688M
12620 270,1688,267M
12623 267,1688,270M
12627 277,270,267M
12628 267,277,270M
12634 267,270,268M
12635 267,268,270M
12636 268,267,270M
12637 277,267,270M
12638 267,277,285,270M
12639 270,285,277,267M
12640 270,277,285,267M
12641 277,270,285,267M
12642 277,267,285,270M
12647 277,270,285,267M
13091 285,270,267M
15800 285,270,267,277M