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 (
    4, 
    6, 
    7, 
    8, 
    9, 
    10, 
    12, 
    13, 
    14, 
    15, 
    16, 
    19, 
    20, 
    23, 
    27, 
    32, 
    33, 
    34, 
    35, 
    36, 
    42, 
    52, 
    54, 
    59, 
    60, 
    61, 
    62, 
    63, 
    84, 
    85, 
    86, 
    89, 
    93, 
    94, 
    95, 
    107, 
    108, 
    109, 
    110, 
    111, 
    113, 
    114, 
    118, 
    120, 
    121, 
    123, 
    127, 
    128, 
    130, 
    131, 
    134, 
    136, 
    141, 
    142, 
    144, 
    145, 
    146, 
    147, 
    50984, 
    50985, 
    50986, 
    50988, 
    50992, 
    50993, 
    51106, 
    51107, 
    51108, 
    51109, 
    51110, 
    51111, 
    51112, 
    51113, 
    51114, 
    51115, 
    51116, 
    51117, 
    51118, 
    51161, 
    51162, 
    51163, 
    51164, 
    51165, 
    51177, 
    51178, 
    51179, 
    51180, 
    51182, 
    51183, 
    51184, 
    51185, 
    51186, 
    51187, 
    51188, 
    51189, 
    51190, 
    51191, 
    51192, 
    51193, 
    51194, 
    51195, 
    51196, 
    51197, 
    51198, 
    51199, 
    51200, 
    51201, 
    51212, 
    51213, 
    51214, 
    51215, 
    51216, 
    51217, 
    51218, 
    51219, 
    51220, 
    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, 
    51310, 
    51311, 
    51312, 
    51313, 
    51314, 
    51325, 
    51327, 
    51328, 
    51329, 
    51330, 
    51331, 
    51332, 
    51336, 
    51337, 
    51338, 
    51339, 
    51344, 
    51346, 
    51347, 
    51348, 
    51350, 
    51374, 
    51375, 
    51376, 
    51377, 
    51378, 
    51379, 
    51380, 
    51381, 
    51382, 
    51390, 
    51391, 
    51392, 
    51393, 
    51394, 
    51444, 
    51445, 
    51446, 
    51447, 
    51448, 
    51449, 
    51450, 
    51451
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00835

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "204.85"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "66.72"
      },
      "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": 66,
            "filtered": "24.19",
            "index_condition": "(`test_uchur_k`.`cscart_products_categories`.`product_id` in (4,6,7,8,9,10,12,13,14,15,16,19,20,23,27,32,33,34,35,36,42,52,54,59,60,61,62,63,84,85,86,89,93,94,95,107,108,109,110,111,113,114,118,120,121,123,127,128,130,131,134,136,141,142,144,145,146,147,50984,50985,50986,50988,50992,50993,51106,51107,51108,51109,51110,51111,51112,51113,51114,51115,51116,51117,51118,51161,51162,51163,51164,51165,51177,51178,51179,51180,51182,51183,51184,51185,51186,51187,51188,51189,51190,51191,51192,51193,51194,51195,51196,51197,51198,51199,51200,51201,51212,51213,51214,51215,51216,51217,51218,51219,51220,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,51310,51311,51312,51313,51314,51325,51327,51328,51329,51330,51331,51332,51336,51337,51338,51339,51344,51346,51347,51348,51350,51374,51375,51376,51377,51378,51379,51380,51381,51382,51390,51391,51392,51393,51394,51444,51445,51446,51447,51448,51449,51450,51451))",
            "cost_info": {
              "read_cost": "68.94",
              "eval_cost": "6.67",
              "prefix_cost": "138.14",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id	category_ids
4	6,40M,42
6	6,40M,42
7	7,40M,42
8	7,40M,42,390
9	6,40M,42
10	6,40M,42
12	7,40M,42,390
13	6,40M,42
14	6,40M,42
15	6,40M,43
16	6,40M,44
19	9,40M,44
20	9,40M,43
23	6,40M,42
27	6,40M,42
32	7,40M,42
33	7,40M,42
34	7,40M,42
35	7,40M,44
36	7,40M,43
42	18M,45,391
52	9,40M,42
54	24,40M,44
59	6,40M,42
60	6,40M,42
61	6,40M,42
62	6,40M,42
63	8M,45,394
84	28,40M,42,394
85	28,40M,42,394
86	29,40M,43,394
89	25,40M,42
93	9,40M,42
94	7,40M,43
95	31,40M,43
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
118	6,40M,42
120	6,40M,42
121	6,40M,42
123	6,40M,42
127	34M,45
128	10,40M,43
130	10,40M,42
131	9,40M,42
134	10,45M
136	37M,45,394
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
50984	10M,45
50985	10M
50986	10M
50988	10M
50992	25M
50993	25M
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
51161	9,40M,42
51162	9,40M,42
51163	9,40M,42
51164	9,40M,42
51165	9,40M,42
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
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
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
51310	24,40M,44
51311	24,40M,44
51312	24,40M,44
51313	24,40M,44
51314	24,40M,44
51325	34M,45
51327	10M
51328	10M
51329	10M
51330	10M
51331	10M
51332	10M
51336	10M
51337	10M
51338	10M
51339	10M
51344	6,40M,42
51346	6,40M,42
51347	28,40M,42,394
51348	6,40M,42
51350	6,40M,42
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
51390	25,40M,42
51391	25,40M,42
51392	25,40M,42
51393	25,40M,42
51394	25,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