Sandbox

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 (
    5, 
    11, 
    17, 
    18, 
    21, 
    22, 
    27, 
    30, 
    31, 
    37, 
    38, 
    39, 
    40, 
    41, 
    46, 
    47, 
    48, 
    49, 
    50, 
    51, 
    53, 
    56, 
    57, 
    58, 
    65, 
    81, 
    82, 
    88, 
    90, 
    91, 
    92, 
    105, 
    106, 
    115, 
    116, 
    117, 
    122, 
    124, 
    125, 
    126, 
    129, 
    135, 
    137, 
    138, 
    139, 
    140, 
    21567, 
    22710, 
    22711, 
    22712, 
    22713, 
    22714, 
    22715, 
    22716, 
    50989, 
    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, 
    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, 
    51166, 
    51167, 
    51168, 
    51169, 
    51170, 
    51171, 
    51172, 
    51173, 
    51174, 
    51176, 
    51202, 
    51203, 
    51204, 
    51205, 
    51206, 
    51207, 
    51208, 
    51209, 
    51221, 
    51223, 
    51224, 
    51295, 
    51296, 
    51297, 
    51298, 
    51299, 
    51300, 
    51301, 
    51302, 
    51303, 
    51304, 
    51305, 
    51306, 
    51307, 
    51308, 
    51309, 
    51316, 
    51317, 
    51318, 
    51319, 
    51320, 
    51321, 
    51322, 
    51323, 
    51324, 
    51326, 
    51340, 
    51341, 
    51342, 
    51343, 
    51349, 
    51357, 
    51358, 
    51359, 
    51360, 
    51361, 
    51362, 
    51363, 
    51364, 
    51365, 
    51366, 
    51367, 
    51368, 
    51369, 
    51370, 
    51371, 
    51372, 
    51373, 
    51385, 
    51386, 
    51387, 
    51388, 
    51389, 
    51395, 
    51396, 
    51397, 
    51443, 
    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.00438

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "206.63"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "68.50"
      },
      "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": 68,
            "filtered": "24.84",
            "index_condition": "(`test_uchur_k`.`cscart_products_categories`.`product_id` in (5,11,17,18,21,22,27,30,31,37,38,39,40,41,46,47,48,49,50,51,53,56,57,58,65,81,82,88,90,91,92,105,106,115,116,117,122,124,125,126,129,135,137,138,139,140,21567,22710,22711,22712,22713,22714,22715,22716,50989,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,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,51166,51167,51168,51169,51170,51171,51172,51173,51174,51176,51202,51203,51204,51205,51206,51207,51208,51209,51221,51223,51224,51295,51296,51297,51298,51299,51300,51301,51302,51303,51304,51305,51306,51307,51308,51309,51316,51317,51318,51319,51320,51321,51322,51323,51324,51326,51340,51341,51342,51343,51349,51357,51358,51359,51360,51361,51362,51363,51364,51365,51366,51367,51368,51369,51370,51371,51372,51373,51385,51386,51387,51388,51389,51395,51396,51397,51443,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": "6.85",
              "prefix_cost": "138.14",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id	category_ids
5	6,40M,42
11	8,40M,42,394
17	6,40M,44
18	6,40M,43
21	10,40M,43
22	6,40M,42
27	6,40M,42
30	18,40M,44,391
31	18,40M,43,391
37	10,40M,42
38	10,40M,42
39	10,40M,42
40	10,40M,44
41	10,40M,43
46	18,40M,42,391
47	23M,45
48	10,40M,44
49	10,40M,43
50	23,40M,44
51	23,40M,43
53	10,40M,44
56	8,40M,42,394
57	6,40M,44
58	6,40M,43
65	25M,45
81	8,40M,43,394
82	8,40M,44,394
88	40M,42,235
90	9,40M,43
91	9,40M,44
92	10,40M,42
105	6,40M,44,394
106	6,40M,43,394
115	8,40M,42,394
116	8,40M,42,394
117	8,40M,42,394
122	6,40M,42
124	7,40M,42
125	7,40M,42
126	8,40M,42,394
129	24,40M,42
135	36M,45,394
137	7,40M,42
138	24,40M,42
139	24,40M,42
140	25,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
50989	29M,40,42,394
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
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
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
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
51221	7,40M,42
51223	7,40M,42
51224	7,40M,42
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
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
51326	36M,45,394
51340	23M,45
51341	23M,45
51342	23M,45
51343	23M,45
51349	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
51385	25,40M,42
51386	25,40M,42
51387	25,40M,42
51388	25,40M,42
51389	25,40M,42
51395	40M,42,235
51396	40M,42,235
51397	40M,42,235
51443	24,40M,42
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