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 (
    20775, 
    20824, 
    20773, 
    20809, 
    20789, 
    20840, 
    20778, 
    20730, 
    20735, 
    20720, 
    20731, 
    20793, 
    20748, 
    20970, 
    20760, 
    20790, 
    20712, 
    20713, 
    20954, 
    20743, 
    20765, 
    20758, 
    21008, 
    20801, 
    20953, 
    20882, 
    20745, 
    20929, 
    20979, 
    21068, 
    21012, 
    20932, 
    20812, 
    20852, 
    20725, 
    21010, 
    21071, 
    20823, 
    20923, 
    20826, 
    20797, 
    20853, 
    20802, 
    20884, 
    20815, 
    20774, 
    20723, 
    20832, 
    20900, 
    20858, 
    20871, 
    21067, 
    20718, 
    20766, 
    20780, 
    20752, 
    20784, 
    20709, 
    21066, 
    21076, 
    20901, 
    20930, 
    20955, 
    20678, 
    20796, 
    20948, 
    20989, 
    20862, 
    20800, 
    21002, 
    20828, 
    20861, 
    20781, 
    20835, 
    20744, 
    20977, 
    20902, 
    20927, 
    20833, 
    20995, 
    21007, 
    20679, 
    20818, 
    20831, 
    20837, 
    20866, 
    20740, 
    20750, 
    21018, 
    20728, 
    20761, 
    20938, 
    21029, 
    20808, 
    20847, 
    20844, 
    20918, 
    20947, 
    20963, 
    20985, 
    20806, 
    20859, 
    20719, 
    20950, 
    20867, 
    21016, 
    20946, 
    20868, 
    20978, 
    20881, 
    20822, 
    20855, 
    20960, 
    20742, 
    20907, 
    20937, 
    21077, 
    20749, 
    20753, 
    20928, 
    21033, 
    20958, 
    20959, 
    20924, 
    20869, 
    21025, 
    20785, 
    20872, 
    20777, 
    20983, 
    20726, 
    20994, 
    20878, 
    20945, 
    20908, 
    20899, 
    20830, 
    20920, 
    21041, 
    20717, 
    21034, 
    21027, 
    21039, 
    20879, 
    20910, 
    20734, 
    21011, 
    20851, 
    21040, 
    20998, 
    21030, 
    21055, 
    21054
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00371

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "266.26"
    },
    "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": 285,
            "rows_produced_per_join": 285,
            "filtered": "100.00",
            "index_condition": "(`cscartdb`.`cscart_products_categories`.`product_id` in (20775,20824,20773,20809,20789,20840,20778,20730,20735,20720,20731,20793,20748,20970,20760,20790,20712,20713,20954,20743,20765,20758,21008,20801,20953,20882,20745,20929,20979,21068,21012,20932,20812,20852,20725,21010,21071,20823,20923,20826,20797,20853,20802,20884,20815,20774,20723,20832,20900,20858,20871,21067,20718,20766,20780,20752,20784,20709,21066,21076,20901,20930,20955,20678,20796,20948,20989,20862,20800,21002,20828,20861,20781,20835,20744,20977,20902,20927,20833,20995,21007,20679,20818,20831,20837,20866,20740,20750,21018,20728,20761,20938,21029,20808,20847,20844,20918,20947,20963,20985,20806,20859,20719,20950,20867,21016,20946,20868,20978,20881,20822,20855,20960,20742,20907,20937,21077,20749,20753,20928,21033,20958,20959,20924,20869,21025,20785,20872,20777,20983,20726,20994,20878,20945,20908,20899,20830,20920,21041,20717,21034,21027,21039,20879,20910,20734,21011,20851,21040,20998,21030,21055,21054))",
            "cost_info": {
              "read_cost": "138.01",
              "eval_cost": "28.50",
              "prefix_cost": "166.51",
              "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": [
              "cscartdb.cscart_products_categories.category_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 14,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "71.25",
              "eval_cost": "1.43",
              "prefix_cost": "266.26",
              "data_read_per_join": "47K"
            },
            "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')))"
          }
        }
      ]
    }
  }
}