Need to optimize Query

[deepak.fugo]

Hi All,

I 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 accross 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 2

I 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.sc_id AND 
sub.category_id = mc.category_id

GROUP BY sub.category_id

[AleMonteiro]

I'm not sure, but try like this (it may have syntax issues):

SELECT  SUM( ct.num_prod ), mc.catagory_name
FROM (
    SELECT  COUNT(`product_id`) as num_prod, sub.category_id as cat_id
    FROM  `product_table_1` as p
    INNER JOIN fm_product_sub_category as sub
        ON ( p.sub_cat_id = sub.sc_id )
    GROUP BY
        sub.category_id

    UNION ALL

    SELECT  COUNT(`product_id`) as num_prod, sub.category_id as cat_id
    FROM  `product_table_2` as p
    INNER JOIN fm_product_sub_category as sub
        ON ( p.sub_cat_id = sub.sc_id )
    GROUP BY
        sub.category_id

    UNION ALL

    SELECT  COUNT(`product_id`) as num_prod, sub.category_id as cat_id
    FROM  `product_table_3` as p
    INNER JOIN fm_product_sub_category as sub
        ON ( p.sub_cat_id = sub.sc_id )
    GROUP BY
        sub.category_id

    UNION ALL

    SELECT  COUNT(`product_id`) as num_prod, sub.category_id as cat_id
    FROM  `product_table_4` as p
    INNER JOIN fm_product_sub_category as sub
        ON ( p.sub_cat_id = sub.sc_id )
    GROUP BY
        sub.category_id

    UNION ALL

    SELECT  COUNT(`product_id`) as num_prod, sub.category_id as cat_id
    FROM  `product_table_5` as p
    INNER JOIN fm_product_sub_category as sub
        ON ( p.sub_cat_id = sub.sc_id )
    GROUP BY
        sub.category_id
) 
AS ct , 
fm_product_main_category AS mc

WHERE 
    ct.cat_id = mc.category_id

GROUP BY 
    ct.cat_id

The main difference between our approches, is that yours select all product from all tables and then cont them. My approach count the products from each table and then sum them.