Need to optimize the query

[deepakfugo]

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 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.sub_cat_id AND 
sub.category_id = mc.category_id
GROUP BY sub.category_id

Edited March 12, 2013 by ignace
Please, use code tags and properly format your texts dont just copy paste them.

[davidannis]

I assume that ct.sub_cat_id, sub.sub_cat_id AND mc.category_id are all INDEX fields. If not that's a place to start

[shlumph]

The first thing I would do is remove the cross join on the SUB CATEGORY and CATEGORY tables.

 

... INNER JOIN fm_product_sub_category AS sub ON ct.sub_cat_id = sub.sub_cat_id
INNER JOIN fm_product_main_category AS mc ON mc.category_id = sub.category_id
GROUP BY sub.category_id

 

Then you can create indexes on ct.sub_cat_id, sub.sub_cat_id, sub.category_id, and mc.category_id.

Edited March 12, 2013 by ignace
Same here, use code tags

[mikosiko]

Why do you have 5 products tables?

 

post the EXPLAIN plan for your query

Edited March 12, 2013 by mikosiko