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 (
    1274, 1406, 1266, 1428, 1263, 1256, 1319, 
    1273, 4526, 1532, 4796, 4562, 8499, 
    8502, 8501, 4798, 1284, 1528, 4767, 
    1517, 1542, 4744, 8498, 8497, 1540, 
    1544, 4571, 4812, 1538, 8503, 8505, 
    8496, 4565, 4570, 4567, 4568, 10136, 
    10137, 4569, 5774
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00256

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "116.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": 133,
            "rows_produced_per_join": 133,
            "filtered": "100.00",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_products_categories`.`product_id` in (1274,1406,1266,1428,1263,1256,1319,1273,4526,1532,4796,4562,8499,8502,8501,4798,1284,1528,4767,1517,1542,4744,8498,8497,1540,1544,4571,4812,1538,8503,8505,8496,4565,4570,4567,4568,10136,10137,4569,5774))",
            "cost_info": {
              "read_cost": "56.56",
              "eval_cost": "13.30",
              "prefix_cost": "69.86",
              "data_read_per_join": "2K"
            },
            "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": 6,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "33.25",
              "eval_cost": "0.67",
              "prefix_cost": "116.41",
              "data_read_per_join": "20K"
            },
            "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
1256 234,166,326M
1263 234,166,326M
1266 234,166,326M
1273 234,166,326M
1274 320,166,325M
1284 322,166,324M
1319 166,234,326M
1406 234,166,326M
1428 234,166,326M
1517 454,453,372,174,166,190M
1528 453,454,372,174,166,190M
1532 454,453,372,174,166,190M
1538 454,453,372,174,166,190M
1540 329,166,344M
1542 329,166,344M
1544 329,166,344M
4526 234,166,326M
4562 170,166,526M
4565 170,166,526M
4567 170,166,526M
4568 170,166,526M
4569 170,166,526M
4570 170,166,526M
4571 170,166,526M
4744 234,166,326M
4767 234,166,326M
4796 320,166,325M
4798 320,166,325M
4812 322,166,324M
5774 572,372,582M
8496 372,453,454,166,174,190M
8497 174,454,372,166,453,190M
8498 372,166,453,174,454,190M
8499 174,454,166,453,372,190M
8501 372,453,166,174,454,190M
8502 372,174,454,453,166,190M
8503 372,166,453,174,454,190M
8505 174,454,372,166,453,190M
10136 372,454,453,190,174,166M
10137 372,454,453,190,174,166M