#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    17
    Rep Power
    0

    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.

    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
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,150
    Rep Power
    4274
    right off the bat, this query won't even run due to obvious syntax errors revealed by cursory inspection

    please post real tables and real query

    also, please post results of EXPLAIN on the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo