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 (
    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, 
    1003, 
    1001, 
    1000, 
    508, 
    1322, 
    1262, 
    1317, 
    1318, 
    1319, 
    1320, 
    1321, 
    1256, 
    1395, 
    1396, 
    1397, 
    1399, 
    1406, 
    1407, 
    1408, 
    1409, 
    1410, 
    1411, 
    1412, 
    1273, 
    1425, 
    1427, 
    1428, 
    1429, 
    1431, 
    1266, 
    1442, 
    1447, 
    1263, 
    1458, 
    2467, 
    2468, 
    2469, 
    2470, 
    2471, 
    2472, 
    2473, 
    2475, 
    2476, 
    2477, 
    2478, 
    2483, 
    2482, 
    2484, 
    2485, 
    2486, 
    2487, 
    2488, 
    2489, 
    2490, 
    2491, 
    2492, 
    2493, 
    2494, 
    2495, 
    2496, 
    2497, 
    2499, 
    2500, 
    2503, 
    2504, 
    2505, 
    2517, 
    2518, 
    2519, 
    2524, 
    2525, 
    2526, 
    2527, 
    2528, 
    2529, 
    2530, 
    2531, 
    2538, 
    2540, 
    2541, 
    2550, 
    2549, 
    2548, 
    2544, 
    2551, 
    2552, 
    2534, 
    2564, 
    2565, 
    2576, 
    2577, 
    2578, 
    2579, 
    2580, 
    2581, 
    2585, 
    2586, 
    2596, 
    2597, 
    2598, 
    2599, 
    2600, 
    2601, 
    2602, 
    2604, 
    2605, 
    2606, 
    2607, 
    2608, 
    2615, 
    2620, 
    2621, 
    2623, 
    2624, 
    2625, 
    2626, 
    2631, 
    2632, 
    2633, 
    2685, 
    2686, 
    2687, 
    3048, 
    3047, 
    3046, 
    3049, 
    3050, 
    3051, 
    3053, 
    3054, 
    3055, 
    3058, 
    3059, 
    2502, 
    3639, 
    4526, 
    4527, 
    4528, 
    4529, 
    4530, 
    4532, 
    4533, 
    4744, 
    4745, 
    4746, 
    4747, 
    4750, 
    4751, 
    4776, 
    4778, 
    4780, 
    4771, 
    4773, 
    4767, 
    4788, 
    4789, 
    4790, 
    5031, 
    5027, 
    5041, 
    5037, 
    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, 
    8417, 
    8418, 
    8419, 
    8420, 
    8398, 
    8415, 
    8416, 
    8421, 
    8399, 
    8476, 
    8477, 
    10140, 
    10141, 
    10142, 
    10143, 
    10144, 
    10145, 
    10224, 
    10223, 
    10222, 
    10228, 
    10229, 
    10230, 
    10233, 
    10234, 
    10284, 
    10285, 
    10286, 
    10287, 
    10288, 
    10289, 
    10290, 
    10291, 
    10292, 
    10293, 
    10395, 
    10396, 
    10435, 
    10860, 
    10861
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00650

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "532.81"
    },
    "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": 576,
            "rows_produced_per_join": 576,
            "filtered": "100.00",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_products_categories`.`product_id` in (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,1003,1001,1000,508,1322,1262,1317,1318,1319,1320,1321,1256,1395,1396,1397,1399,1406,1407,1408,1409,1410,1411,1412,1273,1425,1427,1428,1429,1431,1266,1442,1447,1263,1458,2467,2468,2469,2470,2471,2472,2473,2475,2476,2477,2478,2483,2482,2484,2485,2486,2487,2488,2489,2490,2491,2492,2493,2494,2495,2496,2497,2499,2500,2503,2504,2505,2517,2518,2519,2524,2525,2526,2527,2528,2529,2530,2531,2538,2540,2541,2550,2549,2548,2544,2551,2552,2534,2564,2565,2576,2577,2578,2579,2580,2581,2585,2586,2596,2597,2598,2599,2600,2601,2602,2604,2605,2606,2607,2608,2615,2620,2621,2623,2624,2625,2626,2631,2632,2633,2685,2686,2687,3048,3047,3046,3049,3050,3051,3053,3054,3055,3058,3059,2502,3639,4526,4527,4528,4529,4530,4532,4533,4744,4745,4746,4747,4750,4751,4776,4778,4780,4771,4773,4767,4788,4789,4790,5031,5027,5041,5037,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,8417,8418,8419,8420,8398,8415,8416,8421,8399,8476,8477,10140,10141,10142,10143,10144,10145,10224,10223,10222,10228,10229,10230,10233,10234,10284,10285,10286,10287,10288,10289,10290,10291,10292,10293,10395,10396,10435,10860,10861))",
            "cost_info": {
              "read_cost": "273.61",
              "eval_cost": "57.60",
              "prefix_cost": "331.21",
              "data_read_per_join": "9K"
            },
            "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": 28,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "144.00",
              "eval_cost": "2.88",
              "prefix_cost": "532.81",
              "data_read_per_join": "90K"
            },
            "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
446 234,166,235M
478 166,234,235M
493 234,166,235M
508 234,166,235M
525 234,166,235M
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
1000 166,234,235M
1001 234,166,235M
1003 166,234,235M
1256 234,166,326M
1262 166,234,326M
1263 234,166,326M
1266 234,166,326M
1273 234,166,326M
1317 166,234,326M
1318 166,234,326M
1319 166,234,326M
1320 166,234,326M
1321 166,234,326M
1322 166,234,326M
1395 234,166,326M
1396 234,166,326M
1397 234,166,326M
1399 234,166,326M
1406 234,166,326M
1407 234,166,326M
1408 234,166,326M
1409 234,166,326M
1410 234,166,326M
1411 234,166,326M
1412 234,166,326M
1425 234,166,326M
1427 234,166,326M
1428 234,166,326M
1429 234,166,326M
1431 234,166,326M
1442 234,166,326M
1447 234,166,326M
1458 234,166,326M
2467 166,234,236M
2468 234,166,236M
2469 234,166,236M
2470 234,166,236M
2471 234,166,236M
2472 234,166,236M
2473 166,234,236M
2475 166,234,236M
2476 166,234,236M
2477 166,234,236M
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
2490 166,234,236M
2491 166,234,236M
2492 166,234,236M
2493 166,234,236M
2494 166,234,236M
2495 166,234,236M
2496 166,234,236M
2497 166,234,236M
2499 166,234,236M
2500 166,234,236M
2502 166,234,236M
2503 166,234,236M
2504 166,234,236M
2505 166,234,236M
2517 234,166,236M
2518 166,234,236M
2519 166,234,236M
2524 166,234,236M
2525 234,166,236M
2526 234,166,236M
2527 234,166,236M
2528 234,166,236M
2529 234,166,236M
2530 234,166,236M
2531 234,166,236M
2534 234,166,236M
2538 234,166,236M
2540 166,234,236M
2541 166,234,236M
2544 234,166,236M
2548 166,234,236M
2549 166,234,236M
2550 166,234,236M
2551 166,234,236M
2552 166,234,236M
2564 166,234,236M
2565 166,234,236M
2576 234,166,236M
2577 234,166,236M
2578 234,166,236M
2579 234,166,236M
2580 166,234,236M
2581 166,234,236M
2585 166,234,236M
2586 166,234,236M
2596 234,166,236M
2597 166,234,236M
2598 166,234,236M
2599 166,234,236M
2600 166,234,236M
2601 166,234,236M
2602 166,234,236M
2604 166,234,236M
2605 166,234,236M
2606 166,234,236M
2607 166,234,236M
2608 166,234,236M
2615 234,166,236M
2620 166,234,236M
2621 166,234,236M
2623 166,234,236M
2624 166,234,236M
2625 166,234,236M
2626 166,234,236M
2631 166,234,236M
2632 166,234,236M
2633 166,234,236M
2685 234,166,236M
2686 166,234,236M
2687 166,234,236M
3046 234,166,470M
3047 166,234,470M
3048 166,234,470M
3049 166,234,470M
3050 166,234,470M
3051 166,234,470M
3053 234,166,470M
3054 234,166,470M
3055 166,234,470M
3058 166,234,470M
3059 166,234,470M
3639 234,166,236M
4526 234,166,326M
4527 234,166,326M
4528 234,166,326M
4529 234,166,326M
4530 234,166,326M
4532 234,166,326M
4533 234,166,326M
4744 234,166,326M
4745 166,234,326M
4746 234,166,326M
4747 166,234,326M
4750 166,234,326M
4751 234,166,326M
4767 234,166,326M
4771 166,234,326M
4773 234,166,326M
4776 166,234,326M
4778 166,234,326M
4780 166,234,326M
4788 166,234,326M
4789 234,166,326M
4790 166,234,326M
5027 166,234,559M
5031 166,234,559M
5037 234,166,559M
5041 234,166,559M
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
7004 235M
7005 235M
7006 235M
7007 235M
7008 235M
7009 235M
7010 235M
7011 235M
7012 235M
7013 235M
7014 235M
7015 235M
8398 236M
8399 236M
8415 236M
8416 236M
8417 236M
8418 236M
8419 236M
8420 236M
8421 236M
8476 236M
8477 236M
10140 234,166,236M
10141 234,166,236M
10142 234,166,236M
10143 234,166,236M
10144 234,166,236M
10145 234,166,236M
10222 236M
10223 236M
10224 236M
10228 236M
10229 236M
10230 236M
10233 236M
10234 236M
10284 235M
10285 235M
10286 235M
10287 235M
10288 235M
10289 235M
10290 235M
10291 235M
10292 235M
10293 235M
10395 234M
10396 234M
10435 234M
10860 234M
10861 234M