Search the Community

Hi All, I am using MYSQL/PHP 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. PRODUCT TABLE ----------------------- product_id BIGINT 10, product_name VARCHAR 128, sub_cat_id INT 8, approved TINY INT 2 CATEGORY TABLE ----------------------- category_id INT 8, category_name VARCHAR 128, approved TINY INT 2 SUB CATEGORY TABLE ----------------------- sub_cat_id BIGINT 10, subcategory_name VARCHAR 128, category_id INT 8, approved TINY INT 2I Need to optimize the below query.SELECT COUNT( ct.prod_id ), mc.catagory_name FROM ( SELECT `product_id` , `sub_cat_id` FROM `product_table_1` UNION ALL SELECT `product_id` , `sub_cat_id` FROM `product_table_2` UNION ALL SELECT `product_id` , `sub_cat_id` FROM `product_table_3` UNION ALL SELECT `product_id` , `sub_cat_id` FROM `product_table_4` UNION ALL SELECT `product_id` , `sub_cat_id` FROM `product_table_5` ) AS ct , fm_product_sub_category AS sub, fm_product_main_category AS mc WHERE ct.sub_cat_id = sub.sub_cat_id AND sub.category_id = mc.category_id GROUP BY sub.category_id