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 = 271 
WHERE 
  cscart_products_categories.product_id IN (
    15943, 
    15951, 
    15952, 
    15956, 
    15958, 
    15959, 
    15944, 
    15960, 
    15961, 
    15962, 
    15966, 
    15957, 
    15965, 
    15945, 
    15968, 
    15946, 
    15947, 
    15949, 
    15950, 
    12520, 
    12497, 
    12528, 
    12512, 
    12511, 
    12527, 
    10580, 
    10579, 
    9936, 
    9931, 
    12521, 
    12504, 
    12502, 
    12505, 
    13099, 
    12514, 
    12513, 
    9759, 
    10496, 
    10560, 
    10561, 
    10581, 
    10102, 
    9940, 
    10578, 
    10143, 
    9947, 
    10567, 
    10568, 
    10565, 
    9933, 
    12499, 
    12500, 
    10200, 
    10201, 
    10566, 
    10545, 
    10227, 
    10552, 
    12506, 
    10693, 
    12508, 
    9953, 
    12522, 
    12507, 
    12498, 
    13101, 
    13100, 
    10699, 
    10695, 
    10195, 
    10576, 
    10696, 
    9760, 
    10583, 
    10701, 
    10582, 
    10139, 
    12503, 
    10575, 
    9932, 
    10075, 
    10135, 
    9952, 
    10074, 
    10573, 
    10577, 
    10651, 
    10642, 
    12529, 
    12515, 
    12525, 
    12532, 
    12523, 
    10198, 
    10557, 
    10202, 
    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
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00866

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": 185,
      "filtered": 100,
      "index_condition": "cscart_products_categories.product_id in (15943,15951,15952,15956,15958,15959,15944,15960,15961,15962,15966,15957,15965,15945,15968,15946,15947,15949,15950,12520,12497,12528,12512,12511,12527,10580,10579,9936,9931,12521,12504,12502,12505,13099,12514,12513,9759,10496,10560,10561,10581,10102,9940,10578,10143,9947,10567,10568,10565,9933,12499,12500,10200,10201,10566,10545,10227,10552,12506,10693,12508,9953,12522,12507,12498,13101,13100,10699,10695,10195,10576,10696,9760,10583,10701,10582,10139,12503,10575,9932,10075,10135,9952,10074,10573,10577,10651,10642,12529,12515,12525,12532,12523,10198,10557,10202,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)"
    },
    "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
9759 275M
9760 275M
9931 275M
9932 275M
9933 275M
9934 275M
9936 275M
9940 275M
9947 275M
9952 275M
9953 275M
10053 275M
10074 275M
10075 275M
10101 275M
10102 275M
10130 275M
10134 275M
10135 275M
10137 275M
10138 275M
10139 275M
10141 275M
10142 275M
10143 275M
10190 275M
10193 275M
10195 275M
10198 275M
10200 275M
10201 275M
10202 275M
10226 275M
10227 275M
10496 275M
10545 275M
10550 275M
10551 275M
10552 275M
10556 275M
10557 275M
10558 275M
10560 275M
10561 275M
10563 275M
10565 275M
10566 275M
10567 275M
10568 275M
10569 275M
10572 275M
10573 275M
10575 275M
10576 275M
10577 275M
10578 275M
10579 275M
10580 275M
10581 275M
10582 275M
10583 275M
10602 275M
10603 275M
10642 275M
10651 275M
10657 275M
10658 275M
10659 275M
10661 275M
10663 275M
10664 275M
10666 275M
10693 275M
10695 275M
10696 275M
10699 275M
10701 275M
12497 275M
12498 275M
12499 275M
12500 275M
12502 275M
12503 275M
12504 275M
12505 275M
12506 275M
12507 275M
12508 275M
12509 275M
12511 275M
12512 275M
12513 275M
12514 275M
12515 275M
12517 275M
12520 275M
12521 275M
12522 275M
12523 275M
12525 275M
12526 275M
12527 275M
12528 275M
12529 275M
12531 275M
12532 275M
13099 275M
13100 275M
13101 275M
15943 272M
15944 272M
15945 272M
15946 272M
15947 272M
15949 272M
15950 272M
15951 272M
15952 272M
15956 272M
15957 272M
15958 272M
15959 272M
15960 272M
15961 272M
15962 272M
15965 272M
15966 272M
15968 272M