SELECT 
  DISTINCT pc.category_id, 
  count(DISTINCT pc.product_id) as total_products 
FROM 
  cscart_products AS products 
  LEFT JOIN cscart_products_categories AS pc ON products.product_id = pc.product_id 
WHERE 
  1 
  AND products.product_id IN (
    7338, 
    19956, 
    19957, 
    19958, 
    19959, 
    19960, 
    19965, 
    19967, 
    19968, 
    19969, 
    19970, 
    19972, 
    19973, 
    19974, 
    19975, 
    19976, 
    20002, 
    20004, 
    20005, 
    20006, 
    20008, 
    20009, 
    20033, 
    20042, 
    20067, 
    20174, 
    20175, 
    20376, 
    20398, 
    20399, 
    20400, 
    20401, 
    20402, 
    20403, 
    20404, 
    20405, 
    20617, 
    20618, 
    20619, 
    21057, 
    21058, 
    21059, 
    21060, 
    21061, 
    21062, 
    21063, 
    21064, 
    21140, 
    21141, 
    21142, 
    21143, 
    21144, 
    21145, 
    21146, 
    21147, 
    21148, 
    21149, 
    21150, 
    21151, 
    21152, 
    21153, 
    21154, 
    21155, 
    21156, 
    21157, 
    21158, 
    21159, 
    21160, 
    21161, 
    21162, 
    21163, 
    21164, 
    21165, 
    21166, 
    21167, 
    21168, 
    21169, 
    21170, 
    21171, 
    21172, 
    21173, 
    21174, 
    21175, 
    21176, 
    21177, 
    21178, 
    21179, 
    21180, 
    21181, 
    21182, 
    21183, 
    21184, 
    21185, 
    21186, 
    21187, 
    21188, 
    21189, 
    21190, 
    21191, 
    21192, 
    21193, 
    21194, 
    21195, 
    21196, 
    21197, 
    21198, 
    21199, 
    21200, 
    21201, 
    21202, 
    21203, 
    21204, 
    21205, 
    21206, 
    21207, 
    21208, 
    21209, 
    21210, 
    21211, 
    21212, 
    21213, 
    21214, 
    21215, 
    21216, 
    21217, 
    21218, 
    21219, 
    21229, 
    21230, 
    21231, 
    21232, 
    21233, 
    21234, 
    21323, 
    21324, 
    21325, 
    21326, 
    21327, 
    21328, 
    21329, 
    21330, 
    21331, 
    21332, 
    21333, 
    21334, 
    21335, 
    21535, 
    21536, 
    21579, 
    21580, 
    21581, 
    21582, 
    21593, 
    21594, 
    21595, 
    21596, 
    21597, 
    21598, 
    21599, 
    21600, 
    21601, 
    21602, 
    21603, 
    21605, 
    21606, 
    21607, 
    21608, 
    21609, 
    21610, 
    21611, 
    21612, 
    21613, 
    21614, 
    21615, 
    21616, 
    21617, 
    21618, 
    21619, 
    21620, 
    21621, 
    21622, 
    21961, 
    21974, 
    21975, 
    21976, 
    21977, 
    21978, 
    21979, 
    21981, 
    21982, 
    21983, 
    21984, 
    22102, 
    22103, 
    22104, 
    22105, 
    22106, 
    22107, 
    22108, 
    22109, 
    22341, 
    22345, 
    22346, 
    22347, 
    22348, 
    22349, 
    22351, 
    22352, 
    22353, 
    22508, 
    22509, 
    22700, 
    22701, 
    22702, 
    22703, 
    22705, 
    22706, 
    23132, 
    23515, 
    23610, 
    23611, 
    23612, 
    23828, 
    23829, 
    23830, 
    23831, 
    23832, 
    23833, 
    23834, 
    23835, 
    23836, 
    23837, 
    23838, 
    23839, 
    23840, 
    23841, 
    23842, 
    23843, 
    23844, 
    23845, 
    23846, 
    23847, 
    23848, 
    23849, 
    23850, 
    23851, 
    23852, 
    23853, 
    23854, 
    23855, 
    8298, 
    9833, 
    9972, 
    12129, 
    19961, 
    19962, 
    19963, 
    19964, 
    19966, 
    19971, 
    20010, 
    20043, 
    20106, 
    20163, 
    20374, 
    20375, 
    21053, 
    21054, 
    21055, 
    21134, 
    21135, 
    21843, 
    21958, 
    21959, 
    21960, 
    21962, 
    21964, 
    21965, 
    21966, 
    21967, 
    21968, 
    21969, 
    21970, 
    21971, 
    21972, 
    21973, 
    21980, 
    22223, 
    22268, 
    22269, 
    22270, 
    22271, 
    22350, 
    22354, 
    22355, 
    22356, 
    22467, 
    22510, 
    22511, 
    22513, 
    22553, 
    22678, 
    22679, 
    22698, 
    22699, 
    22704, 
    22841, 
    23133, 
    23188, 
    23516, 
    23827, 
    23856, 
    23857, 
    23858
  ) 
GROUP BY 
  pc.category_id

Query time 0.00191

JSON explain

{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "pc.category_id",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "products",
              "access_type": "range",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "3",
              "used_key_parts": ["product_id"],
              "rows": 314,
              "filtered": 100,
              "attached_condition": "products.product_id in (7338,19956,19957,19958,19959,19960,19965,19967,19968,19969,19970,19972,19973,19974,19975,19976,20002,20004,20005,20006,20008,20009,20033,20042,20067,20174,20175,20376,20398,20399,20400,20401,20402,20403,20404,20405,20617,20618,20619,21057,21058,21059,21060,21061,21062,21063,21064,21140,21141,21142,21143,21144,21145,21146,21147,21148,21149,21150,21151,21152,21153,21154,21155,21156,21157,21158,21159,21160,21161,21162,21163,21164,21165,21166,21167,21168,21169,21170,21171,21172,21173,21174,21175,21176,21177,21178,21179,21180,21181,21182,21183,21184,21185,21186,21187,21188,21189,21190,21191,21192,21193,21194,21195,21196,21197,21198,21199,21200,21201,21202,21203,21204,21205,21206,21207,21208,21209,21210,21211,21212,21213,21214,21215,21216,21217,21218,21219,21229,21230,21231,21232,21233,21234,21323,21324,21325,21326,21327,21328,21329,21330,21331,21332,21333,21334,21335,21535,21536,21579,21580,21581,21582,21593,21594,21595,21596,21597,21598,21599,21600,21601,21602,21603,21605,21606,21607,21608,21609,21610,21611,21612,21613,21614,21615,21616,21617,21618,21619,21620,21621,21622,21961,21974,21975,21976,21977,21978,21979,21981,21982,21983,21984,22102,22103,22104,22105,22106,22107,22108,22109,22341,22345,22346,22347,22348,22349,22351,22352,22353,22508,22509,22700,22701,22702,22703,22705,22706,23132,23515,23610,23611,23612,23828,23829,23830,23831,23832,23833,23834,23835,23836,23837,23838,23839,23840,23841,23842,23843,23844,23845,23846,23847,23848,23849,23850,23851,23852,23853,23854,23855,8298,9833,9972,12129,19961,19962,19963,19964,19966,19971,20010,20043,20106,20163,20374,20375,21053,21054,21055,21134,21135,21843,21958,21959,21960,21962,21964,21965,21966,21967,21968,21969,21970,21971,21972,21973,21980,22223,22268,22269,22270,22271,22350,22354,22355,22356,22467,22510,22511,22513,22553,22678,22679,22698,22699,22704,22841,23133,23188,23516,23827,23856,23857,23858)",
              "using_index": true
            }
          },
          {
            "table": {
              "table_name": "pc",
              "access_type": "ref",
              "possible_keys": ["pt"],
              "key": "pt",
              "key_length": "3",
              "used_key_parts": ["product_id"],
              "ref": ["lillyshop_lillyshop.products.product_id"],
              "rows": 1,
              "filtered": 100
            }
          }
        ]
      }
    }
  }
}

Result

category_id total_products
440 1
442 4
459 4
463 4
540 2
548 1
552 1
603 1
854 1
941 1
960 64
1016 90
1018 53
1033 59
1034 22
1094 6