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 (
    6682, 
    6683, 
    6684, 
    6685, 
    6686, 
    6687, 
    6688, 
    6689, 
    6690, 
    6691, 
    6692, 
    6693, 
    6694, 
    6695, 
    6696, 
    6697, 
    6698, 
    6699, 
    6700, 
    6701, 
    6702, 
    6703, 
    6704, 
    6705, 
    6706, 
    6707, 
    6708, 
    6709, 
    6710, 
    6711, 
    6712, 
    6713, 
    6714, 
    6715, 
    6716, 
    6717, 
    6718, 
    6719, 
    6720, 
    6721, 
    6722, 
    6723, 
    6724, 
    6725, 
    6726, 
    6727, 
    6728, 
    6729, 
    6730, 
    6731, 
    6732, 
    6733, 
    6734, 
    6735, 
    6736, 
    6737, 
    6738, 
    6739, 
    6740, 
    6741, 
    6742, 
    6743, 
    6744, 
    6745, 
    6746, 
    6747, 
    6748, 
    6749, 
    6750, 
    6751, 
    6752, 
    6753, 
    6754, 
    6755, 
    6756, 
    6757, 
    6758, 
    6759, 
    6760, 
    6761, 
    6762, 
    6763, 
    6764, 
    6765, 
    6766, 
    6767, 
    6768, 
    6769, 
    6770, 
    6771, 
    6772, 
    6773, 
    6774, 
    6775, 
    6776, 
    6777, 
    6778, 
    6779, 
    6780, 
    6781, 
    6782, 
    6783, 
    6784, 
    6785, 
    6786, 
    6787, 
    6788, 
    6789, 
    6790, 
    6791, 
    6793, 
    6794, 
    6795, 
    6796, 
    6797, 
    6798, 
    6799, 
    6800, 
    6801, 
    6802, 
    6803, 
    6804, 
    6805, 
    6806, 
    6807, 
    6808, 
    6809, 
    6810, 
    6811, 
    6812, 
    6813, 
    6814, 
    6912, 
    6913, 
    6914, 
    6915, 
    6916, 
    6917, 
    6918, 
    6919, 
    6920, 
    6921, 
    6922, 
    6923, 
    6924, 
    7084, 
    7172, 
    7411, 
    7914, 
    8000, 
    8008, 
    8241, 
    8680, 
    8688, 
    8742, 
    8743, 
    8801, 
    8802, 
    8865, 
    9495, 
    10195, 
    10246, 
    10640, 
    10778, 
    10992, 
    11052, 
    11059, 
    11103, 
    11104, 
    11211, 
    11375, 
    11378, 
    20369, 
    20370, 
    20371, 
    21995, 
    21996, 
    22344, 
    12797, 
    12811, 
    20788, 
    12121
  ) 
GROUP BY 
  pc.category_id

Query time 0.00142

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": 182,
              "filtered": 100,
              "attached_condition": "products.product_id in (6682,6683,6684,6685,6686,6687,6688,6689,6690,6691,6692,6693,6694,6695,6696,6697,6698,6699,6700,6701,6702,6703,6704,6705,6706,6707,6708,6709,6710,6711,6712,6713,6714,6715,6716,6717,6718,6719,6720,6721,6722,6723,6724,6725,6726,6727,6728,6729,6730,6731,6732,6733,6734,6735,6736,6737,6738,6739,6740,6741,6742,6743,6744,6745,6746,6747,6748,6749,6750,6751,6752,6753,6754,6755,6756,6757,6758,6759,6760,6761,6762,6763,6764,6765,6766,6767,6768,6769,6770,6771,6772,6773,6774,6775,6776,6777,6778,6779,6780,6781,6782,6783,6784,6785,6786,6787,6788,6789,6790,6791,6793,6794,6795,6796,6797,6798,6799,6800,6801,6802,6803,6804,6805,6806,6807,6808,6809,6810,6811,6812,6813,6814,6912,6913,6914,6915,6916,6917,6918,6919,6920,6921,6922,6923,6924,7084,7172,7411,7914,8000,8008,8241,8680,8688,8742,8743,8801,8802,8865,9495,10195,10246,10640,10778,10992,11052,11059,11103,11104,11211,11375,11378,20369,20370,20371,21995,21996,22344,12797,12811,20788,12121)",
              "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
435 178
693 2
744 2