get all the count of sub category from the product table in single query with mysql optimization
Seems you want a simple GROUP BY:

SELECT subcat_id, COUNT(*) AS number_of_products 
FROM some_table 
WHERE status = 1 
  AND publish = 1 
  AND subcat_id IN (1,2,3,4,33,47)
GROUP BY subcat_id ;


An index on (status, publish, subcat_id) would improve efficiency of the query.