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 (
    16666, 16941, 16927, 16665, 16930, 16799, 
    16685, 16660, 16928, 16709, 16798, 
    16929, 16745, 16694, 16931, 16961, 
    16712, 16655, 16729, 16768, 16845, 
    16743, 16967, 16663, 16719, 16805, 
    16746, 16702, 16657, 16682, 16784, 
    16710, 16779, 16684, 16718, 16711, 
    16948, 16739, 16744, 16683, 16722, 
    16952, 16720, 16672, 16781, 16675, 
    16724, 16740, 16697, 16662, 16753, 
    16707, 16748, 16723, 16755, 16700, 
    16803, 16734, 16687, 16677, 16721, 
    16681, 16667, 16670, 16695, 16725, 
    16674, 16691, 16661, 16708, 16759, 
    16733, 16730, 16659, 16732, 16690, 
    16766, 16761, 16750, 16688, 16756, 
    16656, 16763, 16780, 16752, 16788, 
    16689, 16726, 16705, 16738, 16706, 
    16671, 16698, 16787, 16735, 16773, 
    16760, 16778, 16679, 16764, 16680, 
    16699
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00276

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "176.71"
    },
    "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": 189,
            "rows_produced_per_join": 189,
            "filtered": "100.00",
            "index_condition": "(`cscartdb`.`cscart_products_categories`.`product_id` in (16666,16941,16927,16665,16930,16799,16685,16660,16928,16709,16798,16929,16745,16694,16931,16961,16712,16655,16729,16768,16845,16743,16967,16663,16719,16805,16746,16702,16657,16682,16784,16710,16779,16684,16718,16711,16948,16739,16744,16683,16722,16952,16720,16672,16781,16675,16724,16740,16697,16662,16753,16707,16748,16723,16755,16700,16803,16734,16687,16677,16721,16681,16667,16670,16695,16725,16674,16691,16661,16708,16759,16733,16730,16659,16732,16690,16766,16761,16750,16688,16756,16656,16763,16780,16752,16788,16689,16726,16705,16738,16706,16671,16698,16787,16735,16773,16760,16778,16679,16764,16680,16699))",
            "cost_info": {
              "read_cost": "91.66",
              "eval_cost": "18.90",
              "prefix_cost": "110.56",
              "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": [
              "cscartdb.cscart_products_categories.category_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 9,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "47.25",
              "eval_cost": "0.95",
              "prefix_cost": "176.71",
              "data_read_per_join": "31K"
            },
            "used_columns": [
              "category_id",
              "storefront_id",
              "usergroup_ids",
              "status"
            ],
            "attached_condition": "((`cscartdb`.`cscart_categories`.`storefront_id` in (0,1)) and ((`cscartdb`.`cscart_categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`cscartdb`.`cscart_categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`cscartdb`.`cscart_categories`.`usergroup_ids`))) and (`cscartdb`.`cscart_categories`.`status` in ('A','H')))"
          }
        }
      ]
    }
  }
}