Need to optimize Query

Hi All,

I am using MYSQL database and have 5 product tables(product_table_1,product_table_2 and so on), category table and sub category table.
I need to get number of products mapped for each category. i.e count of products across categories.
Below are the table structure and the query which i have written, I need to optimize the query so that it will take minimum time for fetching records.
Product table contains around 2,000,000 records each. category table having 175 records and sub category table having 6000 records.