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 (
    4, 
    5, 
    6, 
    7, 
    8, 
    9, 
    10, 
    11, 
    12, 
    13, 
    14, 
    15, 
    16, 
    17, 
    18, 
    19, 
    20, 
    21, 
    22, 
    23, 
    27, 
    30, 
    31, 
    32, 
    33, 
    34, 
    35, 
    36, 
    37, 
    38, 
    39, 
    40, 
    41, 
    42, 
    46, 
    47, 
    48, 
    49, 
    50, 
    51, 
    52, 
    53, 
    54, 
    56, 
    57, 
    58, 
    59, 
    60, 
    61, 
    62, 
    63, 
    65, 
    81, 
    82, 
    84, 
    85, 
    86, 
    88, 
    89, 
    90, 
    91, 
    92, 
    93, 
    94, 
    95, 
    105, 
    106, 
    107, 
    108, 
    109, 
    110, 
    111, 
    113, 
    114, 
    115, 
    116, 
    117, 
    118, 
    119, 
    120, 
    121, 
    122, 
    123, 
    124, 
    125, 
    126, 
    127, 
    128, 
    129, 
    130, 
    131, 
    134, 
    135, 
    136, 
    137, 
    138, 
    139, 
    140, 
    141, 
    142, 
    144, 
    145, 
    146, 
    147, 
    21567, 
    22710, 
    22711, 
    22712, 
    22713, 
    22714, 
    22715, 
    22716, 
    50984, 
    50985, 
    50986, 
    50988, 
    50989, 
    50992, 
    50993, 
    51072, 
    51073, 
    51074, 
    51075, 
    51076, 
    51077, 
    51078, 
    51079, 
    51080, 
    51081, 
    51082, 
    51083, 
    51085, 
    51086, 
    51087, 
    51088, 
    51089, 
    51090, 
    51091, 
    51092, 
    51093, 
    51095, 
    51096, 
    51097, 
    51098, 
    51099, 
    51100, 
    51101, 
    51102, 
    51103, 
    51105, 
    51106, 
    51107, 
    51108, 
    51109, 
    51110, 
    51111, 
    51112, 
    51113, 
    51114, 
    51115, 
    51116, 
    51117, 
    51118, 
    51121, 
    51122, 
    51123, 
    51125, 
    51126, 
    51127, 
    51128, 
    51129, 
    51130, 
    51131, 
    51132, 
    51133, 
    51134, 
    51135, 
    51136, 
    51137, 
    51138, 
    51139, 
    51140, 
    51141, 
    51142, 
    51143, 
    51144, 
    51145, 
    51146, 
    51147, 
    51148, 
    51149, 
    51150, 
    51152, 
    51153, 
    51154, 
    51155, 
    51156, 
    51157, 
    51158, 
    51159, 
    51160, 
    51161, 
    51162, 
    51163, 
    51164, 
    51165, 
    51166, 
    51167, 
    51168, 
    51169, 
    51170, 
    51171, 
    51172, 
    51173, 
    51174, 
    51176, 
    51177, 
    51178, 
    51179, 
    51180, 
    51182, 
    51183, 
    51184, 
    51185, 
    51186, 
    51187, 
    51188, 
    51189, 
    51190, 
    51191, 
    51192, 
    51193, 
    51194, 
    51195, 
    51196, 
    51197, 
    51198, 
    51199, 
    51200, 
    51201, 
    51202, 
    51203, 
    51204, 
    51205, 
    51206, 
    51207, 
    51208, 
    51209, 
    51212, 
    51213, 
    51214, 
    51215, 
    51216, 
    51217, 
    51218, 
    51219, 
    51220, 
    51221, 
    51223, 
    51224, 
    51225, 
    51226, 
    51227, 
    51228, 
    51229, 
    51230, 
    51231, 
    51232, 
    51233, 
    51234, 
    51235, 
    51236, 
    51237, 
    51238, 
    51239, 
    51240, 
    51241, 
    51242, 
    51243, 
    51244, 
    51245, 
    51246, 
    51247, 
    51248, 
    51249, 
    51250, 
    51251, 
    51252, 
    51253, 
    51254, 
    51255, 
    51256, 
    51259, 
    51260, 
    51261, 
    51262, 
    51263, 
    51264, 
    51265, 
    51266, 
    51267, 
    51268, 
    51269, 
    51270, 
    51271, 
    51272, 
    51273, 
    51274, 
    51275, 
    51276, 
    51277, 
    51278, 
    51279, 
    51280, 
    51281, 
    51282, 
    51283, 
    51284, 
    51285, 
    51286, 
    51287, 
    51288, 
    51289, 
    51290, 
    51291, 
    51293, 
    51294, 
    51295, 
    51296, 
    51297, 
    51298, 
    51299, 
    51300, 
    51301, 
    51302, 
    51303, 
    51304, 
    51305, 
    51306, 
    51307, 
    51308, 
    51309, 
    51310, 
    51311, 
    51312, 
    51313, 
    51314, 
    51316, 
    51317, 
    51318, 
    51319, 
    51320, 
    51321, 
    51322, 
    51323, 
    51324, 
    51325, 
    51326, 
    51327, 
    51328, 
    51329, 
    51330, 
    51331, 
    51332, 
    51336, 
    51337, 
    51338, 
    51339, 
    51340, 
    51341, 
    51342, 
    51343, 
    51344, 
    51345, 
    51346, 
    51347, 
    51348, 
    51349, 
    51350, 
    51357, 
    51358, 
    51359, 
    51360, 
    51361, 
    51362, 
    51363, 
    51364, 
    51365, 
    51366, 
    51367, 
    51368, 
    51369, 
    51370, 
    51371, 
    51372, 
    51373, 
    51374, 
    51375, 
    51376, 
    51377, 
    51378, 
    51379, 
    51380, 
    51381, 
    51382, 
    51385, 
    51386, 
    51387, 
    51388, 
    51389, 
    51390, 
    51391, 
    51392, 
    51393, 
    51394, 
    51395, 
    51396, 
    51397, 
    51443, 
    51444, 
    51445, 
    51446, 
    51447, 
    51448, 
    51449, 
    51450, 
    51451, 
    51452, 
    51453, 
    51496, 
    51497, 
    51498, 
    51555, 
    51556, 
    51564, 
    51565, 
    51566, 
    51567, 
    51568, 
    51569, 
    51570, 
    51571, 
    51572, 
    51573, 
    51574, 
    51575, 
    51576, 
    51577, 
    51578, 
    51579, 
    51580, 
    51581, 
    51582, 
    51583, 
    51584, 
    51585, 
    51586
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00575

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "273.64"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "135.51"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_categories",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "p_category_id"
            ],
            "rows_examined_per_scan": 383,
            "rows_produced_per_join": 15,
            "filtered": "4.00",
            "cost_info": {
              "read_cost": "40.09",
              "eval_cost": "1.53",
              "prefix_cost": "41.62",
              "data_read_per_join": "81K"
            },
            "used_columns": [
              "category_id",
              "storefront_id",
              "usergroup_ids",
              "status"
            ],
            "attached_condition": "((`test_uchur_k`.`cscart_categories`.`storefront_id` in (0,1)) and ((`test_uchur_k`.`cscart_categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`test_uchur_k`.`cscart_categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`test_uchur_k`.`cscart_categories`.`usergroup_ids`))) and (`test_uchur_k`.`cscart_categories`.`status` in ('A','H')))"
          }
        },
        {
          "table": {
            "table_name": "cscart_products_categories",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "pt"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id"
            ],
            "key_length": "3",
            "ref": [
              "test_uchur_k.cscart_categories.category_id"
            ],
            "rows_examined_per_scan": 18,
            "rows_produced_per_join": 135,
            "filtered": "49.14",
            "index_condition": "(`test_uchur_k`.`cscart_products_categories`.`product_id` in (4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,27,30,31,32,33,34,35,36,37,38,39,40,41,42,46,47,48,49,50,51,52,53,54,56,57,58,59,60,61,62,63,65,81,82,84,85,86,88,89,90,91,92,93,94,95,105,106,107,108,109,110,111,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,134,135,136,137,138,139,140,141,142,144,145,146,147,21567,22710,22711,22712,22713,22714,22715,22716,50984,50985,50986,50988,50989,50992,50993,51072,51073,51074,51075,51076,51077,51078,51079,51080,51081,51082,51083,51085,51086,51087,51088,51089,51090,51091,51092,51093,51095,51096,51097,51098,51099,51100,51101,51102,51103,51105,51106,51107,51108,51109,51110,51111,51112,51113,51114,51115,51116,51117,51118,51121,51122,51123,51125,51126,51127,51128,51129,51130,51131,51132,51133,51134,51135,51136,51137,51138,51139,51140,51141,51142,51143,51144,51145,51146,51147,51148,51149,51150,51152,51153,51154,51155,51156,51157,51158,51159,51160,51161,51162,51163,51164,51165,51166,51167,51168,51169,51170,51171,51172,51173,51174,51176,51177,51178,51179,51180,51182,51183,51184,51185,51186,51187,51188,51189,51190,51191,51192,51193,51194,51195,51196,51197,51198,51199,51200,51201,51202,51203,51204,51205,51206,51207,51208,51209,51212,51213,51214,51215,51216,51217,51218,51219,51220,51221,51223,51224,51225,51226,51227,51228,51229,51230,51231,51232,51233,51234,51235,51236,51237,51238,51239,51240,51241,51242,51243,51244,51245,51246,51247,51248,51249,51250,51251,51252,51253,51254,51255,51256,51259,51260,51261,51262,51263,51264,51265,51266,51267,51268,51269,51270,51271,51272,51273,51274,51275,51276,51277,51278,51279,51280,51281,51282,51283,51284,51285,51286,51287,51288,51289,51290,51291,51293,51294,51295,51296,51297,51298,51299,51300,51301,51302,51303,51304,51305,51306,51307,51308,51309,51310,51311,51312,51313,51314,51316,51317,51318,51319,51320,51321,51322,51323,51324,51325,51326,51327,51328,51329,51330,51331,51332,51336,51337,51338,51339,51340,51341,51342,51343,51344,51345,51346,51347,51348,51349,51350,51357,51358,51359,51360,51361,51362,51363,51364,51365,51366,51367,51368,51369,51370,51371,51372,51373,51374,51375,51376,51377,51378,51379,51380,51381,51382,51385,51386,51387,51388,51389,51390,51391,51392,51393,51394,51395,51396,51397,51443,51444,51445,51446,51447,51448,51449,51450,51451,51452,51453,51496,51497,51498,51555,51556,51564,51565,51566,51567,51568,51569,51570,51571,51572,51573,51574,51575,51576,51577,51578,51579,51580,51581,51582,51583,51584,51585,51586))",
            "cost_info": {
              "read_cost": "68.94",
              "eval_cost": "13.55",
              "prefix_cost": "138.14",
              "data_read_per_join": "2K"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id category_ids
4 6,40M,42
5 6,40M,42
6 6,40M,42
7 7,40M,42
8 7,40M,42,390
9 6,40M,42
10 6,40M,42
11 8,40M,42,394
12 7,40M,42,390
13 6,40M,42
14 6,40M,42
15 6,40M,43
16 6,40M,44
17 6,40M,44
18 6,40M,43
19 9,40M,44
20 9,40M,43
21 10,40M,43
22 6,40M,42
23 6,40M,42
27 6,40M,42
30 18,40M,44,391
31 18,40M,43,391
32 7,40M,42
33 7,40M,42
34 7,40M,42
35 7,40M,44
36 7,40M,43
37 10,40M,42
38 10,40M,42
39 10,40M,42
40 10,40M,44
41 10,40M,43
42 18M,45,391
46 18,40M,42,391
47 23M,45
48 10,40M,44
49 10,40M,43
50 23,40M,44
51 23,40M,43
52 9,40M,42
53 10,40M,44
54 24,40M,44
56 8,40M,42,394
57 6,40M,44
58 6,40M,43
59 6,40M,42
60 6,40M,42
61 6,40M,42
62 6,40M,42
63 8M,45,394
65 25M,45
81 8,40M,43,394
82 8,40M,44,394
84 28,40M,42,394
85 28,40M,42,394
86 29,40M,43,394
88 40M,42,235
89 25,40M,42
90 9,40M,43
91 9,40M,44
92 10,40M,42
93 9,40M,42
94 7,40M,43
95 31,40M,43
105 6,40M,44,394
106 6,40M,43,394
107 33,40M,42
108 33,40M,42
109 7,40M,42
110 7,40M,42
111 7,40M,42
113 6,40M,42
114 6,40M,42
115 8,40M,42,394
116 8,40M,42,394
117 8,40M,42,394
118 6,40M,42
119 6,40M,42
120 6,40M,42
121 6,40M,42
122 6,40M,42
123 6,40M,42
124 7,40M,42
125 7,40M,42
126 8,40M,42,394
127 34M,45
128 10,40M,43
129 24,40M,42
130 10,40M,42
131 9,40M,42
134 10,45M
135 36M,45,394
136 37M,45,394
137 7,40M,42
138 24,40M,42
139 24,40M,42
140 25,40M,42
141 6,40M,42
142 6,40M,42
144 7,40M,42,390
145 7,40M,42,390
146 7,40M,42,390
147 7,40M,42
21567 29M,40,42,394
22710 18M,40,42,392
22711 18M,40,42,392
22712 7M,40,42
22713 7M,40,42
22714 7M,40,42
22715 7M,40,42
22716 7M,40,42
50984 10M,45
50985 10M
50986 10M
50988 10M
50989 29M,40,42,394
50992 25M
50993 25M
51072 18M,40,42,392
51073 18M,40,42,392
51074 18M,40,42,392
51075 18M,40,42,392
51076 18M,40,42,392
51077 18M,40,42,392
51078 18M,40,42,392
51079 18M,40,42,392
51080 18M,40,42,392
51081 7M,40,42
51082 7M,40,42
51083 7M,40,42
51085 7M,40,42
51086 7M,40,42
51087 7M,40,42
51088 7M,40,42
51089 7M,40,42
51090 7M,40,42
51091 7M,40,42
51092 7M,40,42
51093 7M,40,42
51095 7M,40,42
51096 7M,40,42
51097 7M,40,42
51098 7M,40,42
51099 7M,40,42
51100 7M,40,42
51101 7M,40,42
51102 7M,40,42
51103 7M,40,42
51105 7M,40,42
51106 10,40M,42
51107 10,40M,42
51108 10,40M,42
51109 10,40M,42
51110 10,40M,42
51111 10,40M,42
51112 10,40M,42
51113 10,40M,43
51114 10,40M,43
51115 10,40M,43
51116 10,40M,43
51117 10,40M,43
51118 10,40M,43
51121 10,40M,42
51122 10,40M,42
51123 10,40M,42
51125 10,40M,44
51126 10,40M,44
51127 10,40M,44
51128 10,40M,44
51129 10,40M,44
51130 10,40M,43
51131 10,40M,43
51132 10,40M,43
51133 10,40M,43
51134 10,40M,43
51135 10,40M,44
51136 10,40M,44
51137 10,40M,44
51138 10,40M,44
51139 10,40M,44
51140 10,40M,42
51141 10,40M,42
51142 10,40M,42
51143 10,40M,42
51144 10,40M,42
51145 10,40M,42
51146 10,40M,42
51147 10,40M,42
51148 10,40M,42
51149 10,40M,42
51150 10,40M,42
51152 10,40M,43
51153 10,40M,43
51154 10,40M,43
51155 10,40M,43
51156 10,40M,44
51157 10,40M,44
51158 10,40M,44
51159 10,40M,44
51160 10,40M,44
51161 9,40M,42
51162 9,40M,42
51163 9,40M,42
51164 9,40M,42
51165 9,40M,42
51166 9,40M,43
51167 9,40M,43
51168 9,40M,43
51169 9,40M,43
51170 9,40M,43
51171 9,40M,43
51172 9,40M,44
51173 9,40M,44
51174 9,40M,44
51176 9,40M,44
51177 9,40M,42
51178 9,40M,42
51179 9,40M,42
51180 9,40M,42
51182 9,40M,42
51183 9,40M,43
51184 9,40M,43
51185 9,40M,43
51186 9,40M,43
51187 9,40M,43
51188 9,40M,43
51189 9,40M,44
51190 9,40M,44
51191 9,40M,44
51192 9,40M,44
51193 9,40M,44
51194 9,40M,44
51195 9,40M,42
51196 9,40M,42
51197 9,40M,42
51198 9,40M,42
51199 9,40M,42
51200 9,40M,42
51201 9,40M,42
51202 7,40M,42
51203 7,40M,42
51204 7,40M,42
51205 7,40M,42
51206 7,40M,42
51207 7,40M,42
51208 7,40M,42
51209 7,40M,42
51212 7,40M,42
51213 7,40M,42,390
51214 7,40M,42,390
51215 7,40M,42,390
51216 7,40M,42,390
51217 7,40M,42,390
51218 7,40M,42,390
51219 7,40M,42,390
51220 7,40M,42,390
51221 7,40M,42
51223 7,40M,42
51224 7,40M,42
51225 7,40M,42
51226 7,40M,42
51227 7,40M,42
51228 7,40M,42
51229 7,40M,42
51230 7,40M,42
51231 7,40M,42
51232 7,40M,42,390
51233 7,40M,42,390
51234 7,40M,42,390
51235 7,40M,42,390
51236 7,40M,42,390
51237 7,40M,42,390
51238 7,40M,42,390
51239 7,40M,43
51240 7,40M,43
51241 7,40M,43
51242 7,40M,43
51243 7,40M,43
51244 7,40M,44
51245 7,40M,44
51246 7,40M,44
51247 7,40M,44
51248 7,40M,44
51249 7,40M,42
51250 7,40M,42
51251 7,40M,42
51252 7,40M,42
51253 7,40M,42
51254 7,40M,42
51255 7,40M,42
51256 7,40M,42
51259 7,40M,42
51260 7,40M,42
51261 7,40M,42
51262 7,40M,42
51263 7,40M,42
51264 7,40M,42
51265 7,40M,42
51266 7,40M,42,390
51267 7,40M,42,390
51268 7,40M,42,390
51269 7,40M,42,390
51270 7,40M,42,390
51271 7,40M,42,390
51272 7,40M,42,390
51273 7,40M,42
51274 7,40M,42
51275 7,40M,42
51276 7,40M,42
51277 7,40M,42
51278 7,40M,42
51279 7,40M,42
51280 7,40M,42
51281 7,40M,42
51282 7,40M,42
51283 7,40M,42,390
51284 7,40M,42,390
51285 7,40M,42,390
51286 7,40M,42,390
51287 7,40M,42,390
51288 7,40M,42,390
51289 7,40M,42,390
51290 7,40M,43
51291 7,40M,43
51293 7,40M,43
51294 7,40M,43
51295 8,40M,42,394
51296 24,40M,42
51297 24,40M,42
51298 24,40M,42
51299 24,40M,42
51300 24,40M,42
51301 24,40M,42
51302 24,40M,42
51303 24,40M,42
51304 24,40M,42
51305 24,40M,42
51306 24,40M,42
51307 24,40M,42
51308 24,40M,42
51309 24,40M,42
51310 24,40M,44
51311 24,40M,44
51312 24,40M,44
51313 24,40M,44
51314 24,40M,44
51316 23,40M,43
51317 23,40M,43
51318 23,40M,43
51319 23,40M,43
51320 23,40M,44
51321 23,40M,44
51322 23,40M,44
51323 23,40M,44
51324 23,40M,44
51325 34M,45
51326 36M,45,394
51327 10M
51328 10M
51329 10M
51330 10M
51331 10M
51332 10M
51336 10M
51337 10M
51338 10M
51339 10M
51340 23M,45
51341 23M,45
51342 23M,45
51343 23M,45
51344 6,40M,42
51345 6,40M,42
51346 6,40M,42
51347 28,40M,42,394
51348 6,40M,42
51349 6,40M,42
51350 6,40M,42
51357 18,40M,42,391
51358 18,40M,42,391
51359 18,40M,42,391
51360 18,40M,42,391
51361 18,40M,44,391
51362 18,40M,44,391
51363 18,40M,44,391
51364 18,40M,44,391
51365 18,40M,44,391
51366 18,40M,43,391
51367 18,40M,43,391
51368 18,40M,43,391
51369 18,40M,43,391
51370 18,40M,42,391
51371 18,40M,42,391
51372 18,40M,42,391
51373 18,40M,42,391
51374 25M
51375 25M
51376 18M,45,391
51377 18M,45,391
51378 18M,45,391
51379 18M,45,391
51380 18M,45,391
51381 25M
51382 25M
51385 25,40M,42
51386 25,40M,42
51387 25,40M,42
51388 25,40M,42
51389 25,40M,42
51390 25,40M,42
51391 25,40M,42
51392 25,40M,42
51393 25,40M,42
51394 25,40M,42
51395 40M,42,235
51396 40M,42,235
51397 40M,42,235
51443 24,40M,42
51444 7,40M,42,390
51445 7,40M,42,390
51446 7,40M,42,390
51447 7,40M,42,390
51448 7,40M,42,390
51449 7,40M,42,390
51450 10M,45
51451 10M,45
51452 18,40M,42,391
51453 18,40M,42,391
51496 33M,40
51497 33M,40
51498 33M,40
51555 40M,42,235
51556 40M,42,235
51564 9M,40,42
51565 10,40M,42
51566 9M,40,42
51567 10M,40,42
51568 9M,40,42
51569 9M,40,42
51570 9M,40,42
51571 9M,40,42
51572 9M,40,42
51573 9M,40,42
51574 10,40M,42
51575 10,40M,42
51576 10,40M,42
51577 10,40M,42
51578 9M,40,42
51579 9M,40,42
51580 9M,40,42
51581 9M,40,42
51582 10M,40,42
51583 10M,40,42
51584 10M,40,42
51585 10M,40,42
51586 10M,40,42