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, 1254, 1253, 247, 2109, 2108, 
    2107, 2265, 2264, 2263, 2253, 2363, 
    2364, 2365, 2395, 2396, 2397, 2398, 
    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, 2503, 2504, 2505, 
    2508, 2512, 2509, 2524, 2525, 2526, 
    2527, 2528, 2529, 2530, 2531, 2538, 
    2540, 2541, 2550, 2549, 2548, 2544, 
    2551, 2552, 2534, 2564, 2565, 2571, 
    2572, 8422, 2574, 2576, 2577, 2578, 
    2579, 2580, 2596, 2597, 2598, 2599, 
    2600, 2601, 2602, 2604, 2605, 2606, 
    2607, 2608, 2615, 2620, 2621, 2637, 
    2638, 2502, 3639, 2432, 8485, 8484, 
    8483, 2459, 8492, 8491, 8493
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00514

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "265.41"
    },
    "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": 298,
            "rows_produced_per_join": 298,
            "filtered": "100.00",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_products_categories`.`product_id` in (908,134,1254,1253,247,2109,2108,2107,2265,2264,2263,2253,2363,2364,2365,2395,2396,2397,2398,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,2503,2504,2505,2508,2512,2509,2524,2525,2526,2527,2528,2529,2530,2531,2538,2540,2541,2550,2549,2548,2544,2551,2552,2534,2564,2565,2571,2572,8422,2574,2576,2577,2578,2579,2580,2596,2597,2598,2599,2600,2601,2602,2604,2605,2606,2607,2608,2615,2620,2621,2637,2638,2502,3639,2432,8485,8484,8483,2459,8492,8491,8493))",
            "cost_info": {
              "read_cost": "131.31",
              "eval_cost": "29.80",
              "prefix_cost": "161.11",
              "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": 14,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "74.50",
              "eval_cost": "1.49",
              "prefix_cost": "265.41",
              "data_read_per_join": "46K"
            },
            "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
908 329,166,344M
1253 166,322,340M
1254 166,322,340M
2107 322,166,340M
2108 322,166,340M
2109 322,166,340M
2253 329,166,344M
2263 329,166,344M
2264 329,166,344M
2265 329,166,344M
2363 166,329,347M
2364 329,166,347M
2365 329,166,347M
2395 308,350,386,250,166,382M
2396 250,386,350,308,166,382M
2397 250,386,350,308,166,382M
2398 250,386,350,308,166,382M
2432 372,174,166,453,454,190M
2459 372,166,453,454,174,190M
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
2502 166,234,236M
2503 166,234,236M
2504 166,234,236M
2505 166,234,236M
2508 320,166,339M
2509 166,320,339M
2512 166,320,339M
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
2571 166,320,339M
2572 320,166,339M
2574 320,166,339M
2576 234,166,236M
2577 234,166,236M
2578 234,166,236M
2579 234,166,236M
2580 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
2637 320,166,339M
2638 166,320,339M
3639 234,166,236M
8422 166,320,339M
8483 454,166,372,453,174,190M
8484 453,174,454,166,372,190M
8485 453,174,454,166,372,190M
8491 174,454,166,453,372,190M
8492 174,454,166,453,372,190M
8493 174,454,166,453,372,190M