get all the count of sub category from the product table in single query with mysql optimization Table structure:

    CREATE TABLE some_table (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(255) NOT NULL,
  `Product_model` varchar(255) NOT NULL,
  `product_discount` double(10,2) NOT NULL,
  `product_price` double(10,2) NOT NULL,
  `product_price_with_discount` double(10,2) NOT NULL,
  `product_quantity` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `subcat_id` int(11) NOT NULL,
  `product_color` varchar(255) NOT NULL,
  `product_short_description` text NOT NULL,
  `product_large_description` text NOT NULL,
  `meta_keyword` varchar(255) NOT NULL,
  `meta_description` text NOT NULL,
  `status` tinyint(1) NOT NULL COMMENT '0=&gt;inactive,1=&gt;active,2=&gt;deleted',
  `publish` tinyint(11) NOT NULL COMMENT '1=published,0=unpbulished',
  `product_feature_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '1=&gt;set_feature,0=&gt;unset_feature',
  `product_pre_order` tinyint(4) NOT NULL COMMENT '0=&gt;no,1=?yes',
  `pre_order_description` text NOT NULL,
  `availability` tinyint(4) NOT NULL COMMENT '0=&gt;out of stock,1=&gt;in stock',
  `childcat_id` varchar(100) DEFAULT NULL,
  `title` varchar(256) NOT NULL,
  `sold_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`product_id`),
  KEY `Product_model_3` (`Product_model`),
  KEY `product_name_7` (`product_name`),
  FULLTEXT KEY `product_name` (`product_name`),
  FULLTEXT KEY `product_name_2` (`product_name`,`Product_model`,`product_short_description`,`product_large_description`),
  FULLTEXT KEY `product_name_3` (`product_name`),
  FULLTEXT KEY `product_name_4` (`product_name`,`Product_model`,`product_short_description`,`product_large_description`),
  FULLTEXT KEY `Product_model_2` (`Product_model`),
  FULLTEXT KEY `Product_model_4` (`Product_model`),
  FULLTEXT KEY `product_name_5` (`product_name`),
  FULLTEXT KEY `Product_model_5` (`Product_model`),
  FULLTEXT KEY `Product_model_6` (`Product_model`),
  FULLTEXT KEY `Product_model_7` (`Product_model`),
  FULLTEXT KEY `product_name_6` (`product_name`,`product_short_description`,`product_large_description`),
  FULLTEXT KEY `Product_model` (`Product_model`),
  FULLTEXT KEY `product_short_description` (`product_short_description`,`product_large_description`)
) ENGINE=InnoDB 


Query executing :

SELECT * FROM some_table where status = 1 and publish = 1 and subcat_id in(1,2,3,4,33,47)


Desired result: To get all the count of product per sub category.

Trying to avoid: 


Joining the table itself as the number of rows are too much to join as execution time is too much.


Edit:

SELECT count(product_id) FROM some_table where status = 1 and publish = 1 and subcat_id in(1)




Output 
114

This query works properly for the single subcat_id but how to implement for multiple subcat_id
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.