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 
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') 
WHERE 
  cscart_products_categories.product_id IN (
    69657, 
    66613, 
    66677, 
    64871, 
    66508, 
    67004, 
    69641, 
    64895, 
    69766, 
    69574, 
    65454, 
    65777, 
    65893, 
    65160, 
    65063, 
    65831, 
    66637, 
    66500, 
    64975, 
    65822, 
    67836, 
    65540, 
    65721, 
    66086, 
    67788, 
    66150, 
    67812, 
    65801, 
    66708, 
    64634, 
    69789, 
    65970, 
    69701, 
    64872, 
    65979, 
    64815, 
    65040, 
    69693, 
    66246, 
    66380, 
    69220, 
    69195, 
    69244, 
    69228, 
    69813, 
    69725, 
    69624, 
    65508, 
    68371, 
    65103, 
    69333, 
    67203, 
    65500, 
    68387, 
    67252, 
    66492, 
    65953, 
    67235, 
    67926, 
    66676, 
    69757, 
    65087, 
    69749, 
    65023, 
    65390, 
    65319, 
    65809, 
    68714, 
    69829, 
    67155, 
    66656, 
    66420, 
    64692, 
    69268, 
    67872, 
    65302, 
    67283, 
    67028, 
    64535, 
    64751, 
    67828, 
    66629, 
    67396, 
    64543, 
    64855, 
    65961, 
    67953, 
    65479, 
    65785, 
    67123, 
    67107, 
    67853, 
    65463, 
    64651, 
    67339, 
    66598, 
    64807, 
    67804, 
    67091, 
    65713, 
    65492, 
    66302, 
    67299, 
    69869, 
    66254, 
    69566, 
    66580, 
    64676, 
    69550, 
    69349, 
    65564, 
    69292, 
    67251, 
    68135, 
    66564, 
    69400, 
    69741, 
    64511, 
    67267, 
    65745, 
    67764, 
    67934, 
    67115, 
    68411, 
    66310, 
    66532, 
    65143, 
    69781, 
    65934, 
    65358, 
    64935, 
    64567, 
    68866, 
    65271, 
    65334, 
    65793, 
    65471, 
    67388, 
    69861, 
    65422, 
    69616, 
    66657, 
    69341, 
    65516, 
    69590, 
    67888, 
    69733, 
    64735, 
    64919, 
    67748, 
    66190, 
    66198, 
    69236, 
    66436, 
    67163, 
    67380, 
    69558, 
    69373, 
    64959, 
    65367, 
    68331, 
    68781, 
    65532, 
    69316, 
    64700, 
    69276, 
    69357, 
    66460, 
    68339, 
    67347, 
    69365, 
    69845, 
    64727, 
    64791, 
    65769, 
    66094, 
    69717, 
    69709, 
    69821, 
    66372, 
    65697, 
    65729, 
    64527, 
    68355, 
    64660, 
    68181, 
    67372, 
    64783, 
    67219, 
    68403, 
    66428, 
    66262, 
    66476, 
    67331, 
    64519, 
    68478, 
    66078, 
    65263, 
    67044, 
    67796, 
    65524, 
    67880, 
    64551, 
    65207, 
    66356, 
    64775, 
    67756, 
    69284, 
    67099, 
    65199, 
    66444, 
    65151, 
    65279, 
    65572, 
    66540, 
    67012, 
    64743, 
    67307, 
    65159, 
    67315, 
    69381, 
    67147, 
    67275, 
    67060, 
    64863, 
    65015, 
    66158, 
    67227, 
    66405, 
    64831, 
    66206, 
    65031, 
    64847, 
    66988, 
    67131, 
    69853, 
    66102, 
    66222, 
    65239, 
    68363, 
    68395, 
    67179, 
    69260, 
    64839, 
    68626, 
    64642, 
    66980, 
    67052, 
    68938, 
    67291, 
    67243, 
    66524, 
    69649, 
    68161, 
    69773, 
    66700, 
    67404, 
    65120, 
    67364, 
    67076, 
    66445, 
    65978, 
    69837, 
    65548, 
    66278, 
    66316, 
    64999, 
    67918, 
    66126, 
    66348, 
    64823, 
    65007, 
    65111, 
    66230, 
    64708, 
    68379, 
    67068, 
    64576, 
    67171, 
    64759, 
    69633, 
    66270, 
    67187, 
    67772, 
    64936, 
    65556, 
    65366, 
    65064, 
    66166, 
    67323, 
    65039, 
    65342, 
    66110, 
    66182, 
    69084, 
    64668, 
    65310, 
    66996, 
    64799, 
    64903, 
    65247, 
    69805, 
    66597, 
    67211, 
    66142, 
    64559, 
    64991, 
    66317, 
    65095, 
    65318, 
    67820, 
    66294, 
    65382, 
    64927, 
    66516, 
    64767, 
    66484, 
    68090, 
    69308, 
    66134, 
    66238, 
    68072, 
    67139, 
    69300, 
    65055, 
    66332, 
    66118, 
    65406, 
    66556, 
    65737, 
    67355, 
    69582, 
    65135, 
    66396, 
    65842, 
    64719, 
    66692, 
    66727, 
    69203, 
    66468, 
    67020, 
    67036, 
    65231, 
    67195, 
    67780, 
    64887, 
    66548, 
    64911, 
    64976, 
    68323, 
    65223, 
    66340, 
    65398, 
    65119, 
    66357, 
    65183, 
    65350, 
    64684, 
    65407, 
    69252, 
    66174, 
    69389, 
    69211, 
    65753, 
    64951, 
    69598, 
    66404, 
    69009, 
    65175, 
    65856, 
    66388, 
    66621, 
    65255, 
    65215, 
    69187, 
    65191, 
    64967, 
    69324, 
    67363, 
    68347, 
    65430, 
    65079, 
    65287, 
    66572, 
    65705, 
    66279, 
    65761, 
    65580, 
    69606, 
    65446, 
    65438, 
    66214
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00737

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "725.21"
    },
    "grouping_operation": {
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_products_categories",
            "access_type": "range",
            "possible_keys": [
              "PRIMARY",
              "pt"
            ],
            "key": "pt",
            "used_key_parts": [
              "product_id"
            ],
            "key_length": "3",
            "rows_examined_per_scan": 784,
            "rows_produced_per_join": 784,
            "filtered": "100.00",
            "index_condition": "(`chocoramo`.`cscart_products_categories`.`product_id` in (69657,66613,66677,64871,66508,67004,69641,64895,69766,69574,65454,65777,65893,65160,65063,65831,66637,66500,64975,65822,67836,65540,65721,66086,67788,66150,67812,65801,66708,64634,69789,65970,69701,64872,65979,64815,65040,69693,66246,66380,69220,69195,69244,69228,69813,69725,69624,65508,68371,65103,69333,67203,65500,68387,67252,66492,65953,67235,67926,66676,69757,65087,69749,65023,65390,65319,65809,68714,69829,67155,66656,66420,64692,69268,67872,65302,67283,67028,64535,64751,67828,66629,67396,64543,64855,65961,67953,65479,65785,67123,67107,67853,65463,64651,67339,66598,64807,67804,67091,65713,65492,66302,67299,69869,66254,69566,66580,64676,69550,69349,65564,69292,67251,68135,66564,69400,69741,64511,67267,65745,67764,67934,67115,68411,66310,66532,65143,69781,65934,65358,64935,64567,68866,65271,65334,65793,65471,67388,69861,65422,69616,66657,69341,65516,69590,67888,69733,64735,64919,67748,66190,66198,69236,66436,67163,67380,69558,69373,64959,65367,68331,68781,65532,69316,64700,69276,69357,66460,68339,67347,69365,69845,64727,64791,65769,66094,69717,69709,69821,66372,65697,65729,64527,68355,64660,68181,67372,64783,67219,68403,66428,66262,66476,67331,64519,68478,66078,65263,67044,67796,65524,67880,64551,65207,66356,64775,67756,69284,67099,65199,66444,65151,65279,65572,66540,67012,64743,67307,65159,67315,69381,67147,67275,67060,64863,65015,66158,67227,66405,64831,66206,65031,64847,66988,67131,69853,66102,66222,65239,68363,68395,67179,69260,64839,68626,64642,66980,67052,68938,67291,67243,66524,69649,68161,69773,66700,67404,65120,67364,67076,66445,65978,69837,65548,66278,66316,64999,67918,66126,66348,64823,65007,65111,66230,64708,68379,67068,64576,67171,64759,69633,66270,67187,67772,64936,65556,65366,65064,66166,67323,65039,65342,66110,66182,69084,64668,65310,66996,64799,64903,65247,69805,66597,67211,66142,64559,64991,66317,65095,65318,67820,66294,65382,64927,66516,64767,66484,68090,69308,66134,66238,68072,67139,69300,65055,66332,66118,65406,66556,65737,67355,69582,65135,66396,65842,64719,66692,66727,69203,66468,67020,67036,65231,67195,67780,64887,66548,64911,64976,68323,65223,66340,65398,65119,66357,65183,65350,64684,65407,69252,66174,69389,69211,65753,64951,69598,66404,69009,65175,65856,66388,66621,65255,65215,69187,65191,64967,69324,67363,68347,65430,65079,65287,66572,65705,66279,65761,65580,69606,65446,65438,66214))",
            "cost_info": {
              "read_cost": "372.41",
              "eval_cost": "78.40",
              "prefix_cost": "450.81",
              "data_read_per_join": "12K"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ]
          }
        },
        {
          "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": [
              "chocoramo.cscart_products_categories.category_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 39,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "196.00",
              "eval_cost": "3.92",
              "prefix_cost": "725.21",
              "data_read_per_join": "131K"
            },
            "used_columns": [
              "category_id",
              "storefront_id",
              "usergroup_ids",
              "status"
            ],
            "attached_condition": "((`chocoramo`.`cscart_categories`.`storefront_id` in (0,1)) and ((`chocoramo`.`cscart_categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`chocoramo`.`cscart_categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`chocoramo`.`cscart_categories`.`usergroup_ids`))) and (`chocoramo`.`cscart_categories`.`status` in ('A','H')))"
          }
        }
      ]
    }
  }
}

Result

product_id category_ids
64511 1461,828M
64519 1461,828M
64527 1461,828M
64535 1461,828M
64543 1473,828M
64551 1461,828M
64559 1461,828M
64567 1461,828M
64576 1461,828M
64634 1461,828M
64642 1461,828M
64651 1461,828M
64660 1461,828M
64668 1461,828M
64676 1461,828M
64684 1461,828M
64692 1461,828M
64700 1461,828M
64708 1461,828M
64719 1461,828M
64727 1461,828M
64735 1461,828M
64743 1461,828M
64751 1455,828M
64759 1461,828M
64767 1473,828M
64775 1455,828M
64783 1461,828M
64791 1455,828M
64799 1461,828M
64807 1455,828M
64815 1452,828M
64823 1455,828M
64831 1455,828M
64839 1452,828M
64847 1455,828M
64855 1452,828M
64863 1455,828M
64871 1473,828M
64872 1452,828M
64887 1455,828M
64895 1473,828M
64903 1455,828M
64911 1455,828M
64919 1452,828M
64927 1455,828M
64935 1473,828M
64936 1455,828M
64951 1455,828M
64959 1452,828M
64967 1455,828M
64975 1452,828M
64976 1455,828M
64991 1455,828M
64999 1452,828M
65007 1455,828M
65015 1455,828M
65023 1452,828M
65031 1455,828M
65039 1455,828M
65040 1452,828M
65055 1455,828M
65063 1452,828M
65064 1455,828M
65079 1455,828M
65087 1452,828M
65095 1455,828M
65103 1452,828M
65111 1452,828M
65119 1452,828M
65120 1455,828M
65135 1455,828M
65143 1452,828M
65151 1455,828M
65159 1455,828M
65160 1452,828M
65175 1473,828M
65183 1455,828M
65191 1455,828M
65199 1455,828M
65207 1455,828M
65215 1455,828M
65223 1455,828M
65231 1455,828M
65239 1455,828M
65247 1455,828M
65255 1455,828M
65263 1455,828M
65271 1455,828M
65279 1455,828M
65287 1455,828M
65302 1455,828M
65310 1455,828M
65318 1455,828M
65319 1452,828M
65334 1455,828M
65342 1452,828M
65350 1455,828M
65358 1455,828M
65366 1455,828M
65367 1452,828M
65382 1455,828M
65390 1452,828M
65398 1455,828M
65406 1452,828M
65407 1455,828M
65422 1452,828M
65430 1452,828M
65438 1452,828M
65446 1452,828M
65454 1452,828M
65463 1452,828M
65471 1452,828M
65479 1452,828M
65492 1452,828M
65500 1452,828M
65508 1452,828M
65516 1452,828M
65524 1452,828M
65532 1452,828M
65540 1452,828M
65548 1452,828M
65556 1452,828M
65564 1452,828M
65572 1452,828M
65580 1452,828M
65697 1451,828M
65705 1451,828M
65713 1451,828M
65721 1451,828M
65729 1451,828M
65737 1451,828M
65745 1451,828M
65753 1451,828M
65761 1473,828M
65769 1451,828M
65777 1451,828M
65785 1451,828M
65793 1451,828M
65801 1451,828M
65809 1451,828M
65822 1451,828M
65831 1451,828M
65842 1459,828M
65856 1473,828M
65893 1450,828M
65934 1473,828M
65953 1450,828M
65961 1450,828M
65970 1450,828M
65978 1455,828M
65979 1450,828M
66078 1455,828M
66086 1450,828M
66094 1455,828M
66102 1450,828M
66110 1455,828M
66118 1455,828M
66126 1455,828M
66134 1455,828M
66142 1455,828M
66150 1453,828M
66158 1455,828M
66166 1453,828M
66174 1453,828M
66182 1448,828M
66190 1448,828M
66198 1453,828M
66206 1448,828M
66214 1453,828M
66222 1453,828M
66230 1453,828M
66238 1453,828M
66246 1453,828M
66254 1448,828M
66262 1453,828M
66270 1473,828M
66278 1453,828M
66279 1448,828M
66294 1447,828M
66302 1453,828M
66310 1447,828M
66316 1453,828M
66317 1448,828M
66332 1447,828M
66340 1453,828M
66348 1448,828M
66356 1453,828M
66357 1448,828M
66372 1448,828M
66380 1448,828M
66388 1459,828M
66396 1448,828M
66404 1459,828M
66405 1448,828M
66420 1448,828M
66428 1459,828M
66436 1448,828M
66444 1453,828M
66445 1448,828M
66460 1447,828M
66468 1459,828M
66476 1459,828M
66484 1448,828M
66492 1448,828M
66500 1450,828M
66508 1473,828M
66516 1448,828M
66524 1448,828M
66532 1447,828M
66540 1448,828M
66548 1458,828M
66556 1458,828M
66564 1448,828M
66572 1459,828M
66580 1448,828M
66597 1448,828M
66598 1459,828M
66613 1473,828M
66621 1459,828M
66629 1448,828M
66637 1448,828M
66656 1453,828M
66657 1448,828M
66676 1450,828M
66677 1473,828M
66692 1448,828M
66700 1460,828M
66708 1460,828M
66727 1460,828M
66980 1448,828M
66988 1448,828M
66996 1448,828M
67004 1473,828M
67012 1448,828M
67020 1448,828M
67028 1458,828M
67036 1458,828M
67044 1458,828M
67052 1448,828M
67060 1473,828M
67068 1448,828M
67076 1448,828M
67091 1447,828M
67099 1458,828M
67107 1458,828M
67115 1448,828M
67123 1447,828M
67131 1448,828M
67139 1459,828M
67147 1447,828M
67155 1448,828M
67163 1448,828M
67171 1448,828M
67179 1448,828M
67187 1448,828M
67195 1448,828M
67203 1447,828M
67211 1460,828M
67219 1448,828M
67227 1458,828M
67235 1460,828M
67243 1458,828M
67251 1460,828M
67252 1448,828M
67267 1460,828M
67275 1460,828M
67283 1460,828M
67291 1460,828M
67299 1460,828M
67307 1460,828M
67315 1460,828M
67323 1460,828M
67331 1460,828M
67339 1460,828M
67347 1460,828M
67355 1460,828M
67363 1460,828M
67364 1473,828M
67372 1460,828M
67380 1460,828M
67388 1460,828M
67396 1460,828M
67404 1460,828M
67748 1448,828M
67756 1448,828M
67764 1448,828M
67772 1448,828M
67780 1448,828M
67788 1448,828M
67796 1448,828M
67804 1448,828M
67812 1448,828M
67820 1448,828M
67828 1448,828M
67836 1448,828M
67853 1448,828M
67872 1447,828M
67880 1458,828M
67888 1473,828M
67918 1447,828M
67926 1447,828M
67934 1448,828M
67953 1448,828M
68072 1460,828M
68090 1473,828M
68135 1460,828M
68161 1460,828M
68181 1460,828M
68323 1473,828M
68331 1450,828M
68339 1460,828M
68347 1460,828M
68355 1460,828M
68363 1460,828M
68371 1460,828M
68379 1460,828M
68387 1460,828M
68395 1460,828M
68403 1460,828M
68411 1460,828M
68478 1453,828M
68626 1459,828M
68714 1460,828M
68781 1450,828M
68866 1460,828M
68938 1460,828M
69009 1460,828M
69084 1460,828M
69187 1459,828M
69195 1460,828M
69203 1459,828M
69211 1459,828M
69220 1460,828M
69228 1460,828M
69236 1453,828M
69244 1459,828M
69252 1473,828M
69260 1460,828M
69268 1460,828M
69276 1460,828M
69284 1459,828M
69292 1453,828M
69300 1460,828M
69308 1460,828M
69316 1447,828M
69324 1452,828M
69333 1452,828M
69341 1450,828M
69349 1452,828M
69357 1459,828M
69365 1459,828M
69373 1460,828M
69381 1459,828M
69389 1459,828M
69400 1459,828M
69550 1459,828M
69558 1459,828M
69566 1459,828M
69574 1450,828M
69582 1459,828M
69590 1453,828M
69598 1447,828M
69606 1459,828M
69616 1455,828M
69624 1459,828M
69633 1452,828M
69641 1473,828M
69649 1473,828M
69657 1473,828M
69693 1457,828M
69701 1457,828M
69709 1457,828M
69717 1457,828M
69725 1457,828M
69733 1457,828M
69741 1457,828M
69749 1457,828M
69757 1457,828M
69766 1457,828M
69773 1457,828M
69781 1457,828M
69789 1457,828M
69805 1457,828M
69813 1457,828M
69821 1457,828M
69829 1457,828M
69837 1457,828M
69845 1457,828M
69853 1473,828M
69861 1457,828M
69869 1457,828M