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 (
    7608, 
    7279, 
    7609, 
    7610, 
    7611, 
    7261, 
    7612, 
    7614, 
    7613, 
    7248, 
    7615, 
    7281, 
    7283, 
    7616, 
    7285, 
    7620, 
    7619, 
    7618, 
    7617, 
    7290, 
    7622, 
    7621, 
    7624, 
    7277, 
    7623, 
    7293, 
    7625, 
    5985, 
    8264, 
    5986, 
    8265, 
    5971, 
    8266, 
    8267, 
    8474, 
    8475, 
    2441, 
    2465, 
    8478, 
    2432, 
    8485, 
    8484, 
    8483, 
    2455, 
    8488, 
    8487, 
    8486, 
    8490, 
    8489, 
    2438, 
    2459, 
    8492, 
    8491, 
    8493, 
    6590, 
    8494, 
    1538, 
    8496, 
    1517, 
    8498, 
    8497, 
    1532, 
    8502, 
    8501, 
    8499, 
    1528, 
    8505, 
    8503, 
    6336, 
    8506, 
    6373, 
    8509, 
    8508, 
    8507, 
    4949, 
    8510, 
    8511, 
    8513, 
    8512, 
    6459, 
    8712, 
    8711, 
    6447, 
    8715, 
    8714, 
    8713, 
    6451, 
    8717, 
    8716, 
    6440, 
    8720, 
    8719, 
    8718, 
    6444, 
    8722, 
    8721, 
    8947, 
    8946, 
    10122, 
    10127, 
    10128, 
    10133, 
    10135, 
    10136, 
    10137, 
    10138, 
    10198, 
    10199, 
    10200, 
    10201, 
    10217, 
    10216, 
    10309, 
    10311, 
    10310, 
    10312, 
    10315, 
    10317, 
    10313, 
    10316, 
    10318, 
    10322, 
    10314, 
    10319, 
    10320, 
    10321
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00629

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "433.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": 502,
            "rows_produced_per_join": 502,
            "filtered": "100.00",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_products_categories`.`product_id` in (7608,7279,7609,7610,7611,7261,7612,7614,7613,7248,7615,7281,7283,7616,7285,7620,7619,7618,7617,7290,7622,7621,7624,7277,7623,7293,7625,5985,8264,5986,8265,5971,8266,8267,8474,8475,2441,2465,8478,2432,8485,8484,8483,2455,8488,8487,8486,8490,8489,2438,2459,8492,8491,8493,6590,8494,1538,8496,1517,8498,8497,1532,8502,8501,8499,1528,8505,8503,6336,8506,6373,8509,8508,8507,4949,8510,8511,8513,8512,6459,8712,8711,6447,8715,8714,8713,6451,8717,8716,6440,8720,8719,8718,6444,8722,8721,8947,8946,10122,10127,10128,10133,10135,10136,10137,10138,10198,10199,10200,10201,10217,10216,10309,10311,10310,10312,10315,10317,10313,10316,10318,10322,10314,10319,10320,10321))",
            "cost_info": {
              "read_cost": "207.21",
              "eval_cost": "50.20",
              "prefix_cost": "257.41",
              "data_read_per_join": "7K"
            },
            "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": 25,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "125.50",
              "eval_cost": "2.51",
              "prefix_cost": "433.11",
              "data_read_per_join": "78K"
            },
            "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')))"
          }
        }
      ]
    }
  }
}