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 (
    908, 
    134, 
    919, 
    918, 
    924, 
    922, 
    921, 
    925, 
    525, 
    917, 
    916, 
    955, 
    942, 
    941, 
    446, 
    953, 
    952, 
    951, 
    949, 
    948, 
    947, 
    946, 
    945, 
    944, 
    982, 
    980, 
    979, 
    978, 
    981, 
    478, 
    977, 
    975, 
    974, 
    973, 
    972, 
    971, 
    970, 
    994, 
    996, 
    493, 
    985, 
    986, 
    988, 
    990, 
    991, 
    993, 
    1254, 
    1253, 
    247, 
    1274, 
    1483, 
    1484, 
    1485, 
    1486, 
    1487, 
    2478, 
    2483, 
    2482, 
    2484, 
    2485, 
    2486, 
    2487, 
    2488, 
    2489, 
    8422, 
    2574, 
    3754, 
    3755, 
    3756, 
    3757, 
    3758, 
    3759, 
    3760, 
    3761, 
    3762, 
    3764, 
    3765, 
    1930, 
    3815, 
    3817, 
    3818, 
    4432, 
    4438, 
    4440, 
    4441, 
    4442, 
    4443, 
    4444, 
    4448, 
    4445, 
    4446, 
    4447, 
    6149, 
    5685, 
    6148, 
    6150, 
    6151, 
    5727, 
    5729, 
    5730, 
    5731, 
    5732, 
    5733, 
    5734, 
    5735, 
    5736, 
    5739, 
    5741, 
    5742, 
    5743, 
    5744, 
    5745, 
    5746, 
    5747, 
    5748, 
    5749, 
    5750, 
    5752, 
    5753, 
    5754, 
    5755, 
    5756, 
    5757, 
    5758, 
    5759, 
    5760, 
    5761, 
    5762, 
    7004, 
    7005, 
    7006, 
    7007, 
    7008, 
    7009, 
    7010, 
    7011, 
    7012, 
    7013, 
    7014, 
    7015, 
    10284, 
    10285, 
    10286, 
    10287, 
    10288, 
    10289, 
    10290, 
    10291, 
    10292, 
    10293
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00520

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "291.11"
    },
    "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": 317,
            "rows_produced_per_join": 317,
            "filtered": "100.00",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_products_categories`.`product_id` in (908,134,919,918,924,922,921,925,525,917,916,955,942,941,446,953,952,951,949,948,947,946,945,944,982,980,979,978,981,478,977,975,974,973,972,971,970,994,996,493,985,986,988,990,991,993,1254,1253,247,1274,1483,1484,1485,1486,1487,2478,2483,2482,2484,2485,2486,2487,2488,2489,8422,2574,3754,3755,3756,3757,3758,3759,3760,3761,3762,3764,3765,1930,3815,3817,3818,4432,4438,4440,4441,4442,4443,4444,4448,4445,4446,4447,6149,5685,6148,6150,6151,5727,5729,5730,5731,5732,5733,5734,5735,5736,5739,5741,5742,5743,5744,5745,5746,5747,5748,5749,5750,5752,5753,5754,5755,5756,5757,5758,5759,5760,5761,5762,7004,7005,7006,7007,7008,7009,7010,7011,7012,7013,7014,7015,10284,10285,10286,10287,10288,10289,10290,10291,10292,10293))",
            "cost_info": {
              "read_cost": "148.46",
              "eval_cost": "31.70",
              "prefix_cost": "180.16",
              "data_read_per_join": "4K"
            },
            "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": [
              "softwarepirmam_hewadelivard_cscart_4.cscart_products_categories.category_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 15,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "79.25",
              "eval_cost": "1.59",
              "prefix_cost": "291.11",
              "data_read_per_join": "49K"
            },
            "used_columns": [
              "category_id",
              "storefront_id",
              "usergroup_ids",
              "status"
            ],
            "attached_condition": "((`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`storefront_id` in (0,1)) and ((`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`usergroup_ids`))) and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`status` in ('A','H')))"
          }
        }
      ]
    }
  }
}

Result

product_id category_ids
134 166,329,344M
247 322,166,340M
446 234,166,235M
478 166,234,235M
493 234,166,235M
525 234,166,235M
908 329,166,344M
916 234,166,235M
917 166,234,235M
918 234,166,235M
919 166,234,235M
921 166,234,235M
922 234,166,235M
924 234,166,235M
925 234,166,235M
941 234,166,235M
942 234,166,235M
944 166,234,235M
945 234,166,235M
946 166,234,235M
947 234,166,235M
948 234,166,235M
949 234,166,235M
951 234,166,235M
952 234,166,235M
953 234,166,235M
955 234,166,235M
970 166,234,235M
971 166,234,235M
972 234,166,235M
973 166,234,235M
974 166,234,235M
975 234,166,235M
977 166,234,235M
978 166,234,235M
979 166,234,235M
980 234,166,235M
981 234,166,235M
982 166,234,235M
985 166,234,235M
986 166,234,235M
988 234,166,235M
990 166,234,235M
991 234,166,235M
993 166,234,235M
994 234,166,235M
996 166,234,235M
1253 166,322,340M
1254 166,322,340M
1274 320,166,325M
1483 320,166,325M
1484 320,166,325M
1485 320,166,325M
1486 166,320,325M
1487 166,320,325M
1930 167,166,356M
2478 166,234,236M
2482 166,234,236M
2483 166,234,236M
2484 166,234,236M
2485 166,234,236M
2486 166,234,236M
2487 166,234,236M
2488 166,234,236M
2489 166,234,236M
2574 320,166,339M
3754 167,166,356M
3755 167,166,356M
3756 167,166,356M
3757 166,167,356M
3758 166,167,356M
3759 167,166,356M
3760 167,166,356M
3761 167,166,356M
3762 167,166,356M
3764 167,166,356M
3765 167,166,356M
3815 166,167,356M
3817 166,167,356M
3818 166,167,356M
4432 320,166,321M
4438 320,166,321M
4440 166,320,321M
4441 320,166,321M
4442 320,166,321M
4443 166,320,321M
4444 320,166,321M
4445 320,166,321M
4446 166,320,321M
4447 320,166,321M
4448 320,166,321M
5685 423,585,166M
5727 234,166,235M
5729 234,166,235M
5730 234,166,235M
5731 234,166,235M
5732 234,166,235M
5733 234,166,235M
5734 234,166,235M
5735 234,166,235M
5736 234,166,235M
5739 234,166,235M
5741 234,166,235M
5742 234,166,235M
5743 234,166,235M
5744 234,166,235M
5745 234,166,235M
5746 234,166,235M
5747 234,166,235M
5748 234,166,235M
5749 234,166,235M
5750 235M
5752 235M
5753 235M
5754 235M
5755 235M
5756 235M
5757 235M
5758 235M
5759 235M
5760 235M
5761 235M
5762 235M
6148 423,585,166M
6149 423,585,166M
6150 423,585,166M
6151 423,585,166M
7004 235M
7005 235M
7006 235M
7007 235M
7008 235M
7009 235M
7010 235M
7011 235M
7012 235M
7013 235M
7014 235M
7015 235M
8422 166,320,339M
10284 235M
10285 235M
10286 235M
10287 235M
10288 235M
10289 235M
10290 235M
10291 235M
10292 235M
10293 235M