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 (
    32019, 
    32119, 
    32177, 
    32142, 
    32153, 
    32036, 
    32195, 
    32199, 
    32164, 
    32121, 
    32257, 
    32116, 
    32127, 
    32002, 
    32151, 
    32000, 
    32175, 
    32230, 
    32236, 
    32176, 
    31951, 
    32220, 
    31974, 
    32024, 
    32173, 
    32251, 
    32183, 
    31954, 
    32135, 
    32004, 
    32137, 
    31985, 
    32191, 
    32240, 
    32005, 
    32238, 
    32034, 
    32088, 
    31934, 
    32250, 
    32032, 
    32237, 
    32201, 
    32033, 
    32131, 
    32009, 
    32185, 
    32197, 
    32204, 
    32027, 
    32294, 
    31984, 
    32120, 
    32205, 
    32182, 
    31927, 
    32110, 
    32208, 
    31944, 
    31950, 
    32155, 
    32156, 
    32162, 
    32161, 
    32295, 
    32262, 
    31962, 
    32087, 
    32228, 
    31976, 
    32192, 
    32165, 
    32108, 
    32296, 
    31948, 
    32130, 
    32020, 
    32150, 
    32022, 
    32030, 
    32303, 
    32167, 
    31981, 
    32227, 
    31983, 
    31940, 
    32293, 
    32095, 
    32041, 
    32255, 
    32082, 
    32217, 
    31972, 
    31942, 
    32073, 
    31994, 
    32013, 
    32169, 
    31953, 
    32070, 
    32076, 
    32099, 
    32122, 
    31932, 
    31957, 
    31971, 
    32168, 
    32180, 
    31959, 
    32016, 
    32212, 
    32051, 
    32247, 
    31947, 
    31931, 
    32113, 
    32149, 
    31928, 
    32198, 
    32049, 
    32299, 
    32145, 
    31930, 
    32279, 
    32297, 
    31977, 
    32112, 
    31929, 
    31961, 
    32174, 
    32301, 
    32115, 
    32055, 
    32053, 
    32179, 
    32181, 
    31926, 
    31992, 
    32071, 
    32081, 
    32097, 
    31956, 
    31975, 
    32042, 
    32152, 
    32235, 
    32107, 
    32109, 
    32264, 
    31941, 
    31964, 
    31966, 
    31970, 
    31982, 
    31986, 
    31999, 
    32011, 
    32023, 
    32038, 
    32047, 
    32050, 
    32080, 
    32083, 
    32084, 
    32093, 
    32100, 
    32114, 
    32124, 
    32190, 
    32219, 
    32252, 
    32218, 
    31922, 
    31923, 
    31924, 
    31936, 
    31938, 
    31939, 
    31955, 
    31969, 
    31989, 
    31998, 
    32015, 
    32018, 
    32025, 
    32029, 
    32039, 
    32040, 
    32056, 
    32057, 
    32063, 
    32065, 
    32072, 
    32074, 
    32079, 
    32090, 
    32091, 
    32094, 
    32096, 
    32103, 
    32126, 
    32143, 
    32147, 
    32160, 
    32186, 
    32203, 
    32221, 
    32223, 
    32232, 
    32234, 
    32243, 
    32246, 
    32258, 
    32263, 
    31925, 
    31933, 
    31949, 
    31963, 
    31965, 
    31968, 
    31973, 
    31979, 
    31997, 
    32007, 
    32008, 
    32021, 
    32048, 
    32052, 
    32058, 
    32059, 
    32062, 
    32064, 
    32066, 
    32067, 
    32068, 
    32075, 
    32077, 
    32078, 
    32085, 
    32089, 
    32092, 
    32101, 
    32104, 
    32106, 
    32111, 
    32118, 
    32123, 
    32134, 
    32141, 
    32194, 
    32200, 
    32207, 
    32209, 
    32210, 
    32211, 
    32215, 
    32224, 
    32225, 
    32229, 
    32242, 
    32253, 
    31921, 
    31937, 
    31952, 
    32001, 
    32014, 
    32069, 
    32086, 
    32102, 
    32105, 
    32128, 
    32132, 
    32154, 
    32184, 
    32213, 
    32239, 
    32244, 
    32292, 
    31990, 
    31991, 
    31996, 
    32054, 
    32117, 
    32133, 
    32226, 
    32163, 
    32010
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00761

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "530.96"
    },
    "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": 574,
            "rows_produced_per_join": 574,
            "filtered": "100.00",
            "index_condition": "(`cscartdb`.`cscart_products_categories`.`product_id` in (32019,32119,32177,32142,32153,32036,32195,32199,32164,32121,32257,32116,32127,32002,32151,32000,32175,32230,32236,32176,31951,32220,31974,32024,32173,32251,32183,31954,32135,32004,32137,31985,32191,32240,32005,32238,32034,32088,31934,32250,32032,32237,32201,32033,32131,32009,32185,32197,32204,32027,32294,31984,32120,32205,32182,31927,32110,32208,31944,31950,32155,32156,32162,32161,32295,32262,31962,32087,32228,31976,32192,32165,32108,32296,31948,32130,32020,32150,32022,32030,32303,32167,31981,32227,31983,31940,32293,32095,32041,32255,32082,32217,31972,31942,32073,31994,32013,32169,31953,32070,32076,32099,32122,31932,31957,31971,32168,32180,31959,32016,32212,32051,32247,31947,31931,32113,32149,31928,32198,32049,32299,32145,31930,32279,32297,31977,32112,31929,31961,32174,32301,32115,32055,32053,32179,32181,31926,31992,32071,32081,32097,31956,31975,32042,32152,32235,32107,32109,32264,31941,31964,31966,31970,31982,31986,31999,32011,32023,32038,32047,32050,32080,32083,32084,32093,32100,32114,32124,32190,32219,32252,32218,31922,31923,31924,31936,31938,31939,31955,31969,31989,31998,32015,32018,32025,32029,32039,32040,32056,32057,32063,32065,32072,32074,32079,32090,32091,32094,32096,32103,32126,32143,32147,32160,32186,32203,32221,32223,32232,32234,32243,32246,32258,32263,31925,31933,31949,31963,31965,31968,31973,31979,31997,32007,32008,32021,32048,32052,32058,32059,32062,32064,32066,32067,32068,32075,32077,32078,32085,32089,32092,32101,32104,32106,32111,32118,32123,32134,32141,32194,32200,32207,32209,32210,32211,32215,32224,32225,32229,32242,32253,31921,31937,31952,32001,32014,32069,32086,32102,32105,32128,32132,32154,32184,32213,32239,32244,32292,31990,31991,31996,32054,32117,32133,32226,32163,32010))",
            "cost_info": {
              "read_cost": "272.66",
              "eval_cost": "57.40",
              "prefix_cost": "330.06",
              "data_read_per_join": "8K"
            },
            "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": 28,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "143.50",
              "eval_cost": "2.87",
              "prefix_cost": "530.96",
              "data_read_per_join": "96K"
            },
            "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
31921 958,347M
31922 958,347M
31923 958,347M
31924 958,347M
31925 958,347M
31926 951,547M
31927 953,556M
31928 951,547M
31929 951,547M
31930 951,547M
31931 951,547M
31932 954,67M
31933 958,347M
31934 956,63M
31936 958,347M
31937 958,347M
31938 958,347M
31939 958,347M
31940 956,63M
31941 958,347M
31942 953,556M
31944 954,67M
31947 959,28M
31948 959,28M
31949 958,347M
31950 956,63M
31951 955,65M
31952 958,347M
31953 954,67M
31954 954,67M
31955 958,347M
31956 958,347M
31957 959,28M
31959 951,547M
31961 951,547M
31962 956,63M
31963 958,347M
31964 958,347M
31965 958,347M
31966 958,347M
31968 958,347M
31969 958,347M
31970 958,347M
31971 958,347M
31972 951,547M
31973 958,347M
31974 959,56M
31975 958,347M
31976 954,67M
31977 958,347M
31979 958,347M
31981 956,63M
31982 958,347M
31983 953,556M
31984 953,556M
31985 953,556M
31986 958,347M
31989 958,347M
31990 958,347M
31991 958,347M
31992 958,347M
31994 956,63M
31996 958,347M
31997 958,347M
31998 958,347M
31999 958,347M
32000 958,347M
32001 958,347M
32002 953,556M
32004 954,67M
32005 959,28M
32007 958,347M
32008 958,347M
32009 953,556M
32010 958,347M
32011 958,347M
32013 954,67M
32014 958,347M
32015 958,347M
32016 951,547M
32018 958,347M
32019 959,28M
32020 959,56M
32021 958,347M
32022 953,556M
32023 958,347M
32024 953,556M
32025 958,347M
32027 954,67M
32029 958,347M
32030 953,556M
32032 953,556M
32033 953,556M
32034 955,65M
32036 959,27M
32038 958,347M
32039 958,347M
32040 958,347M
32041 956,63M
32042 958,347M
32047 958,347M
32048 958,347M
32049 951,547M
32050 958,347M
32051 951,547M
32052 958,347M
32053 958,347M
32054 958,347M
32055 951,547M
32056 958,347M
32057 958,347M
32058 958,347M
32059 958,347M
32062 958,347M
32063 958,347M
32064 958,347M
32065 958,347M
32066 958,347M
32067 958,347M
32068 958,347M
32069 958,347M
32070 956,63M
32071 958,347M
32072 958,347M
32073 951,547M
32074 958,347M
32075 958,347M
32076 956,63M
32077 958,347M
32078 958,347M
32079 958,347M
32080 958,347M
32081 958,347M
32082 953,556M
32083 958,347M
32084 958,347M
32085 958,347M
32086 958,347M
32087 956,63M
32088 953,556M
32089 958,347M
32090 958,347M
32091 958,347M
32092 958,347M
32093 958,347M
32094 958,347M
32095 953,556M
32096 958,347M
32097 958,347M
32099 956,63M
32100 958,347M
32101 958,347M
32102 958,347M
32103 958,347M
32104 958,347M
32105 958,347M
32106 958,347M
32107 958,347M
32108 954,67M
32109 958,347M
32110 954,67M
32111 958,347M
32112 958,347M
32113 958,347M
32114 958,347M
32115 951,547M
32116 955,65M
32117 958,347M
32118 958,347M
32119 959,27M
32120 953,556M
32121 959,56M
32122 956,63M
32123 958,347M
32124 958,347M
32126 958,347M
32127 957,535M
32128 958,347M
32130 954,67M
32131 954,67M
32132 958,347M
32133 958,347M
32134 958,347M
32135 957,535M
32137 959,56M
32141 958,347M
32142 959,26M
32143 958,347M
32145 951,547M
32147 958,347M
32149 958,347M
32150 959,28M
32151 959,56M
32152 958,347M
32153 959,26M
32154 958,347M
32155 956,63M
32156 955,65M
32160 958,347M
32161 954,67M
32162 954,67M
32163 958,347M
32164 959,535M
32165 956,63M
32167 959,56M
32168 954,67M
32169 959,56M
32173 952,29M
32174 951,547M
32175 953,556M
32176 955,65M
32177 959,27M
32179 958,347M
32180 958,347M
32181 958,347M
32182 959,56M
32183 959,56M
32184 958,347M
32185 954,67M
32186 958,347M
32190 958,347M
32191 953,556M
32192 953,556M
32194 958,347M
32195 959,27M
32197 955,65M
32198 951,547M
32199 959,27M
32200 958,347M
32201 956,63M
32203 958,347M
32204 953,556M
32205 953,556M
32207 958,347M
32208 953,556M
32209 958,347M
32210 958,347M
32211 958,347M
32212 951,547M
32213 958,347M
32215 958,347M
32217 954,67M
32218 958,347M
32219 958,347M
32220 954,67M
32221 958,347M
32223 958,347M
32224 958,347M
32225 958,347M
32226 958,347M
32227 956,63M
32228 956,63M
32229 958,347M
32230 959,56M
32232 958,347M
32234 958,347M
32235 958,347M
32236 955,65M
32237 959,56M
32238 953,556M
32239 958,347M
32240 955,65M
32242 958,347M
32243 958,347M
32244 958,347M
32246 958,347M
32247 951,547M
32250 959,56M
32251 959,56M
32252 958,347M
32253 958,347M
32255 956,63M
32257 959,56M
32258 958,347M
32262 957,535M
32263 958,347M
32264 958,347M
32279 951,547M
32292 958,347M
32293 951,547M
32294 953,556M
32295 954,67M
32296 954,67M
32297 951,547M
32299 951,547M
32301 951,547M
32303 959,535M