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 (
    30395, 
    30818, 
    30374, 
    30287, 
    30313, 
    30566, 
    30434, 
    30499, 
    30242, 
    30164, 
    30304, 
    30432, 
    30542, 
    30165, 
    30397, 
    30445, 
    30495, 
    30483, 
    30564, 
    30209, 
    30221, 
    30577, 
    30473, 
    30392, 
    30557, 
    30450, 
    30516, 
    30606, 
    30349, 
    30362, 
    30595, 
    30599, 
    30102, 
    30521, 
    30213, 
    30422, 
    30573, 
    30338, 
    30100, 
    30312, 
    30344, 
    30437, 
    30536, 
    30578, 
    30339, 
    30508, 
    30212, 
    30474, 
    30195, 
    30311, 
    30352, 
    30183, 
    30138, 
    30149, 
    30451, 
    30162, 
    30447, 
    30228, 
    30576, 
    30163, 
    30288, 
    30452, 
    30457, 
    30526, 
    30188, 
    30270, 
    30205, 
    30532, 
    30423, 
    30193, 
    30272, 
    30317, 
    30113, 
    30194, 
    30431, 
    30216, 
    30315, 
    30453, 
    30314, 
    30335, 
    30545, 
    30235, 
    30438, 
    30501, 
    30475, 
    30220, 
    30318, 
    30407, 
    30513, 
    30559, 
    30265, 
    30468, 
    30108, 
    30281, 
    30290, 
    30370, 
    30170, 
    30360, 
    30405, 
    30342, 
    30570, 
    30404, 
    30425, 
    30455, 
    30561, 
    30616, 
    30190, 
    30328, 
    30329, 
    30294, 
    30394, 
    30406, 
    30246, 
    30343, 
    30409, 
    30219, 
    30222, 
    30439, 
    30135, 
    30184, 
    30186, 
    30196, 
    30302, 
    30139, 
    30189, 
    30273, 
    30380, 
    30167, 
    30393, 
    30126, 
    30416, 
    30208, 
    30543, 
    30579, 
    30620, 
    30340, 
    30408, 
    30553, 
    30386, 
    30390, 
    30589, 
    30148, 
    30322, 
    30470, 
    30492, 
    30177, 
    30207, 
    30478, 
    30613, 
    30227, 
    30400, 
    30309, 
    30463, 
    30558, 
    30156, 
    30245, 
    30560, 
    30137, 
    30154, 
    30299, 
    30341, 
    30348, 
    30602, 
    30609, 
    30255, 
    30454, 
    30621, 
    30192, 
    30198, 
    30243, 
    30308, 
    30391, 
    30612, 
    30498, 
    30563, 
    30610, 
    30618, 
    30241, 
    30263, 
    30114, 
    30257, 
    30398, 
    30441, 
    30489, 
    30519, 
    30574, 
    30580, 
    30625, 
    30375, 
    30379, 
    30411, 
    30446, 
    30592, 
    30615, 
    30623, 
    30158, 
    30161, 
    30300, 
    30330, 
    30357, 
    30476, 
    30584, 
    30176, 
    30320, 
    30456, 
    30460, 
    30461, 
    30565, 
    30588, 
    30591, 
    30601, 
    30112, 
    30175, 
    30197, 
    30337, 
    30487, 
    30104, 
    30110, 
    30316, 
    30421, 
    30522, 
    30231, 
    30429, 
    30440, 
    30617, 
    30099, 
    30152, 
    30179, 
    30168, 
    30262, 
    30286, 
    30403, 
    30410, 
    30442, 
    30567, 
    30569, 
    30597, 
    30605, 
    30614, 
    30092, 
    30418, 
    30562, 
    30581, 
    30244, 
    30279, 
    30292, 
    30310, 
    30554, 
    30583, 
    30586, 
    30458, 
    30459, 
    30153, 
    30214, 
    30331, 
    30171, 
    30232, 
    30297, 
    30353, 
    30480, 
    30568, 
    30347, 
    30350, 
    30366, 
    30503, 
    30624, 
    30098, 
    30141, 
    30364, 
    30424, 
    30469, 
    30530, 
    30551, 
    30585, 
    30619, 
    30247, 
    30256, 
    30399, 
    30412, 
    30511, 
    30172, 
    30203, 
    30252, 
    30266, 
    30464, 
    30479, 
    30598, 
    30334, 
    30383, 
    30387, 
    30402, 
    30467, 
    30510, 
    30608, 
    30103, 
    30301, 
    30507, 
    30523, 
    30107, 
    30115, 
    30116, 
    30142, 
    30169, 
    30199, 
    30206, 
    30111, 
    30120, 
    30224, 
    30254, 
    30280, 
    30305, 
    30202, 
    30283, 
    30396, 
    30433, 
    30502, 
    30524, 
    30128, 
    30389, 
    30611, 
    30151, 
    30275, 
    30471, 
    30531, 
    30552, 
    30607, 
    30260, 
    30327, 
    30504, 
    30106, 
    30182, 
    30204, 
    30226, 
    30230, 
    30274, 
    30278, 
    30285, 
    30346, 
    30377, 
    30512, 
    30529, 
    30571, 
    30465, 
    30105, 
    30200, 
    30361, 
    30354, 
    30359, 
    30443, 
    30514, 
    30269, 
    30117, 
    30218, 
    30368, 
    30382, 
    30372, 
    30518, 
    30417, 
    30587, 
    30140, 
    30150, 
    30180, 
    30181, 
    30217, 
    30264, 
    30277, 
    30321, 
    30240, 
    30215, 
    30271, 
    30101, 
    30427, 
    30477, 
    30500, 
    30155, 
    30259, 
    30378, 
    30324, 
    30159, 
    30178, 
    30234, 
    30355, 
    30462, 
    30261, 
    30109, 
    30494, 
    30282, 
    30157, 
    30326, 
    30535, 
    30537, 
    30118
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.01049

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": "(`cscartdb`.`cscart_products_categories`.`product_id` in (30395,30818,30374,30287,30313,30566,30434,30499,30242,30164,30304,30432,30542,30165,30397,30445,30495,30483,30564,30209,30221,30577,30473,30392,30557,30450,30516,30606,30349,30362,30595,30599,30102,30521,30213,30422,30573,30338,30100,30312,30344,30437,30536,30578,30339,30508,30212,30474,30195,30311,30352,30183,30138,30149,30451,30162,30447,30228,30576,30163,30288,30452,30457,30526,30188,30270,30205,30532,30423,30193,30272,30317,30113,30194,30431,30216,30315,30453,30314,30335,30545,30235,30438,30501,30475,30220,30318,30407,30513,30559,30265,30468,30108,30281,30290,30370,30170,30360,30405,30342,30570,30404,30425,30455,30561,30616,30190,30328,30329,30294,30394,30406,30246,30343,30409,30219,30222,30439,30135,30184,30186,30196,30302,30139,30189,30273,30380,30167,30393,30126,30416,30208,30543,30579,30620,30340,30408,30553,30386,30390,30589,30148,30322,30470,30492,30177,30207,30478,30613,30227,30400,30309,30463,30558,30156,30245,30560,30137,30154,30299,30341,30348,30602,30609,30255,30454,30621,30192,30198,30243,30308,30391,30612,30498,30563,30610,30618,30241,30263,30114,30257,30398,30441,30489,30519,30574,30580,30625,30375,30379,30411,30446,30592,30615,30623,30158,30161,30300,30330,30357,30476,30584,30176,30320,30456,30460,30461,30565,30588,30591,30601,30112,30175,30197,30337,30487,30104,30110,30316,30421,30522,30231,30429,30440,30617,30099,30152,30179,30168,30262,30286,30403,30410,30442,30567,30569,30597,30605,30614,30092,30418,30562,30581,30244,30279,30292,30310,30554,30583,30586,30458,30459,30153,30214,30331,30171,30232,30297,30353,30480,30568,30347,30350,30366,30503,30624,30098,30141,30364,30424,30469,30530,30551,30585,30619,30247,30256,30399,30412,30511,30172,30203,30252,30266,30464,30479,30598,30334,30383,30387,30402,30467,30510,30608,30103,30301,30507,30523,30107,30115,30116,30142,30169,30199,30206,30111,30120,30224,30254,30280,30305,30202,30283,30396,30433,30502,30524,30128,30389,30611,30151,30275,30471,30531,30552,30607,30260,30327,30504,30106,30182,30204,30226,30230,30274,30278,30285,30346,30377,30512,30529,30571,30465,30105,30200,30361,30354,30359,30443,30514,30269,30117,30218,30368,30382,30372,30518,30417,30587,30140,30150,30180,30181,30217,30264,30277,30321,30240,30215,30271,30101,30427,30477,30500,30155,30259,30378,30324,30159,30178,30234,30355,30462,30261,30109,30494,30282,30157,30326,30535,30537,30118))",
            "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": [
              "cscartdb.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": "((`cscartdb`.`cscart_categories`.`storefront_id` in (0,1)) and ((`cscartdb`.`cscart_categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`cscartdb`.`cscart_categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`cscartdb`.`cscart_categories`.`usergroup_ids`))) and (`cscartdb`.`cscart_categories`.`status` in ('A','H')))"
          }
        }
      ]
    }
  }
}

Result

product_id category_ids
30092 900,347M
30098 906,28M
30099 900,347M
30100 900,347M
30101 900,347M
30102 900,347M
30103 900,347M
30104 900,347M
30105 900,347M
30106 900,347M
30107 900,347M
30108 900,347M
30109 900,347M
30110 900,347M
30111 900,347M
30112 901,28M
30113 900,347M
30114 900,347M
30115 900,347M
30116 900,347M
30117 900,347M
30118 900,347M
30120 900,347M
30126 901,28M
30128 901,28M
30135 900,347M
30137 906,28M
30138 901,28M
30139 900,347M
30140 900,347M
30141 900,347M
30142 901,28M
30148 901,28M
30149 909,18M
30150 900,347M
30151 901,28M
30152 900,347M
30153 900,347M
30154 901,28M
30155 900,347M
30156 901,28M
30157 900,347M
30158 901,28M
30159 900,347M
30161 900,347M
30162 901,28M
30163 900,347M
30164 901,28M
30165 900,347M
30167 927,347M
30168 900,347M
30169 900,347M
30170 900,347M
30171 900,347M
30172 901,28M
30175 901,28M
30176 927,347M
30177 901,28M
30178 900,347M
30179 901,28M
30180 900,347M
30181 900,347M
30182 900,347M
30183 901,28M
30184 901,28M
30186 904,580M
30188 905,347M
30189 901,28M
30190 901,28M
30192 901,28M
30193 901,28M
30194 906,28M
30195 900,347M
30196 905,347M
30197 901,28M
30198 901,28M
30199 900,347M
30200 900,347M
30202 900,347M
30203 901,28M
30204 900,347M
30205 900,347M
30206 900,347M
30207 901,28M
30208 900,347M
30209 904,556M
30212 900,347M
30213 927,347M
30214 909,18M
30215 900,347M
30216 901,28M
30217 900,347M
30218 905,347M
30219 901,28M
30220 927,347M
30221 927,347M
30222 927,347M
30224 900,347M
30226 900,347M
30227 901,28M
30228 901,28M
30230 900,347M
30231 903,580M
30232 900,347M
30234 900,347M
30235 901,28M
30240 900,347M
30241 900,347M
30242 927,347M
30243 927,347M
30244 900,347M
30245 900,347M
30246 904,556M
30247 927,347M
30252 900,347M
30254 900,347M
30255 901,28M
30256 900,347M
30257 900,347M
30259 900,347M
30260 901,28M
30261 900,347M
30262 900,347M
30263 900,347M
30264 900,347M
30265 900,347M
30266 900,347M
30269 900,347M
30270 927,347M
30271 900,347M
30272 900,347M
30273 900,347M
30274 900,347M
30275 900,347M
30277 900,347M
30278 904,556M
30279 900,347M
30280 900,347M
30281 927,347M
30282 926,347M
30283 900,347M
30285 900,347M
30286 900,347M
30287 927,347M
30288 901,28M
30290 901,28M
30292 901,347M
30294 900,347M
30297 900,347M
30299 900,347M
30300 909,18M
30301 909,18M
30302 904,556M
30304 909,18M
30305 900,347M
30308 901,28M
30309 927,347M
30310 904,556M
30311 927,347M
30312 900,347M
30313 905,347M
30314 900,347M
30315 905,347M
30316 900,347M
30317 927,347M
30318 901,28M
30320 900,347M
30321 900,347M
30322 900,347M
30324 926,347M
30326 909,18M
30327 900,347M
30328 906,28M
30329 906,28M
30330 900,347M
30331 900,347M
30334 927,347M
30335 927,347M
30337 900,347M
30338 927,347M
30339 927,347M
30340 927,347M
30341 901,28M
30342 901,28M
30343 901,28M
30344 901,28M
30346 900,347M
30347 900,347M
30348 900,347M
30349 926,347M
30350 900,347M
30352 927,347M
30353 901,28M
30354 900,347M
30355 900,347M
30357 900,347M
30359 900,347M
30360 904,556M
30361 900,347M
30362 904,556M
30364 900,347M
30366 900,347M
30368 900,347M
30370 900,347M
30372 900,347M
30374 927,347M
30375 900,347M
30377 900,347M
30378 900,347M
30379 900,347M
30380 909,18M
30382 909,18M
30383 900,347M
30386 900,347M
30387 900,347M
30389 900,347M
30390 900,347M
30391 904,556M
30392 927,347M
30393 900,347M
30394 900,347M
30395 900,347M
30396 900,347M
30397 901,28M
30398 900,347M
30399 927,347M
30400 900,347M
30402 900,347M
30403 900,347M
30404 900,347M
30405 900,347M
30406 909,18M
30407 927,347M
30408 905,347M
30409 900,347M
30410 900,347M
30411 900,347M
30412 900,347M
30416 900,347M
30417 904,556M
30418 900,347M
30421 909,18M
30422 900,347M
30423 900,347M
30424 909,18M
30425 900,347M
30427 909,18M
30429 900,347M
30431 900,347M
30432 904,556M
30433 900,347M
30434 927,347M
30437 927,347M
30438 905,347M
30439 904,556M
30440 905,347M
30441 900,347M
30442 900,347M
30443 909,18M
30445 905,347M
30446 900,347M
30447 900,347M
30450 901,28M
30451 900,347M
30452 900,347M
30453 900,347M
30454 927,347M
30455 900,347M
30456 900,347M
30457 900,347M
30458 900,347M
30459 900,347M
30460 909,18M
30461 900,347M
30462 900,347M
30463 900,347M
30464 909,18M
30465 909,18M
30467 905,347M
30468 901,28M
30469 909,18M
30470 927,347M
30471 900,347M
30473 900,347M
30474 904,556M
30475 901,28M
30476 927,347M
30477 900,347M
30478 905,347M
30479 927,347M
30480 900,347M
30483 927,347M
30487 905,347M
30489 900,347M
30492 900,347M
30494 900,347M
30495 927,347M
30498 900,347M
30499 900,347M
30500 900,347M
30501 900,347M
30502 901,28M
30503 909,18M
30504 926,347M
30507 904,556M
30508 904,556M
30510 900,347M
30511 900,347M
30512 900,347M
30513 900,347M
30514 900,347M
30516 900,347M
30518 900,347M
30519 900,347M
30521 927,347M
30522 901,28M
30523 900,347M
30524 900,347M
30526 909,18M
30529 900,347M
30530 900,347M
30531 900,347M
30532 904,556M
30535 926,347M
30536 900,347M
30537 900,347M
30542 900,347M
30543 900,347M
30545 904,556M
30551 900,347M
30552 900,347M
30553 900,347M
30554 900,347M
30557 904,556M
30558 900,347M
30559 900,347M
30560 900,347M
30561 900,347M
30562 900,347M
30563 909,18M
30564 905,347M
30565 900,347M
30566 900,347M
30567 900,347M
30568 900,347M
30569 900,347M
30570 927,347M
30571 905,347M
30573 900,347M
30574 900,347M
30576 900,347M
30577 904,556M
30578 905,347M
30579 900,347M
30580 900,347M
30581 900,347M
30583 900,347M
30584 900,347M
30585 900,347M
30586 900,347M
30587 900,347M
30588 900,347M
30589 927,347M
30591 900,347M
30592 900,347M
30595 927,347M
30597 900,347M
30598 909,18M
30599 927,347M
30601 900,347M
30602 900,347M
30605 900,347M
30606 900,347M
30607 900,347M
30608 900,347M
30609 900,347M
30610 900,347M
30611 900,347M
30612 900,347M
30613 900,347M
30614 900,347M
30615 926,347M
30616 905,347M
30617 905,347M
30618 900,347M
30619 900,347M
30620 900,347M
30621 900,347M
30623 900,347M
30624 900,347M
30625 900,347M
30818 901,28M