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 (
    19134, 
    19300, 
    19139, 
    19079, 
    19151, 
    19294, 
    18489, 
    19220, 
    19187, 
    19069, 
    18910, 
    19203, 
    18825, 
    18997, 
    19254, 
    19233, 
    19102, 
    18896, 
    19065, 
    18840, 
    18638, 
    19105, 
    18995, 
    18506, 
    18660, 
    18845, 
    18882, 
    19111, 
    19330, 
    18835, 
    19373, 
    18990, 
    18570, 
    18643, 
    18634, 
    18960, 
    19342, 
    19214, 
    18899, 
    19189, 
    18942, 
    19175, 
    18967, 
    19211, 
    19033, 
    19040, 
    18587, 
    18876, 
    18696, 
    19185, 
    19083, 
    18936, 
    18934, 
    18781, 
    18832, 
    19397, 
    19341, 
    19382, 
    19252, 
    19277, 
    19331, 
    19086, 
    19171, 
    19167, 
    18888, 
    19030, 
    19399, 
    18972, 
    19113, 
    19328, 
    19148, 
    19289, 
    19060, 
    18984, 
    18938, 
    19353, 
    19346, 
    18884, 
    19183, 
    19094, 
    19325, 
    18564, 
    19169, 
    18518, 
    19351, 
    18779, 
    18850, 
    19058, 
    18861, 
    19359, 
    19292, 
    19261, 
    19141, 
    18805, 
    18951, 
    19074, 
    19302, 
    18599, 
    19027, 
    18836, 
    19315, 
    18787, 
    19024, 
    18798, 
    19244, 
    18903, 
    19192, 
    19032, 
    19072, 
    19303, 
    19051, 
    19163, 
    18962, 
    18878, 
    18911, 
    19082, 
    19298, 
    19339, 
    19223, 
    19275, 
    18855, 
    18496, 
    18565, 
    18793, 
    18954, 
    19020, 
    18927, 
    18958, 
    19055, 
    18843, 
    18906, 
    19123, 
    19311, 
    19119, 
    19200, 
    18947, 
    18588, 
    19372, 
    18821, 
    18873, 
    19198, 
    18875, 
    19272, 
    19168, 
    18823, 
    18901, 
    18555, 
    19129, 
    19202, 
    18837, 
    18956, 
    18886, 
    18905, 
    18869, 
    19282, 
    19159, 
    19264, 
    18671, 
    19237, 
    19259, 
    19251, 
    18512, 
    19078, 
    19047, 
    19266, 
    18999, 
    18784, 
    18790, 
    18930, 
    19310, 
    18913, 
    18791, 
    18894, 
    18914, 
    18964, 
    18918, 
    18827, 
    19242, 
    18987, 
    19004
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00450

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "328.21"
    },
    "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": 354,
            "rows_produced_per_join": 354,
            "filtered": "100.00",
            "index_condition": "(`chocoramo`.`cscart_products_categories`.`product_id` in (19134,19300,19139,19079,19151,19294,18489,19220,19187,19069,18910,19203,18825,18997,19254,19233,19102,18896,19065,18840,18638,19105,18995,18506,18660,18845,18882,19111,19330,18835,19373,18990,18570,18643,18634,18960,19342,19214,18899,19189,18942,19175,18967,19211,19033,19040,18587,18876,18696,19185,19083,18936,18934,18781,18832,19397,19341,19382,19252,19277,19331,19086,19171,19167,18888,19030,19399,18972,19113,19328,19148,19289,19060,18984,18938,19353,19346,18884,19183,19094,19325,18564,19169,18518,19351,18779,18850,19058,18861,19359,19292,19261,19141,18805,18951,19074,19302,18599,19027,18836,19315,18787,19024,18798,19244,18903,19192,19032,19072,19303,19051,19163,18962,18878,18911,19082,19298,19339,19223,19275,18855,18496,18565,18793,18954,19020,18927,18958,19055,18843,18906,19123,19311,19119,19200,18947,18588,19372,18821,18873,19198,18875,19272,19168,18823,18901,18555,19129,19202,18837,18956,18886,18905,18869,19282,19159,19264,18671,19237,19259,19251,18512,19078,19047,19266,18999,18784,18790,18930,19310,18913,18791,18894,18914,18964,18918,18827,19242,18987,19004))",
            "cost_info": {
              "read_cost": "168.91",
              "eval_cost": "35.40",
              "prefix_cost": "204.31",
              "data_read_per_join": "5K"
            },
            "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": [
              "chocoramo.cscart_products_categories.category_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 17,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "88.50",
              "eval_cost": "1.77",
              "prefix_cost": "328.21",
              "data_read_per_join": "59K"
            },
            "used_columns": [
              "category_id",
              "storefront_id",
              "usergroup_ids",
              "status"
            ],
            "attached_condition": "((`chocoramo`.`cscart_categories`.`storefront_id` in (0,1)) and ((`chocoramo`.`cscart_categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`chocoramo`.`cscart_categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`chocoramo`.`cscart_categories`.`usergroup_ids`))) and (`chocoramo`.`cscart_categories`.`status` in ('A','H')))"
          }
        }
      ]
    }
  }
}