how to optimize mysql query : counting category and subcategory with single Query from two tables

mysql, query-optimization

Solution

What's killing performance is the filesort triggered by one of the `GROUP BY`s and/or the `UNION`. You can rewrite the query without `UNION` as:

SELECT pcat.category_id, COUNT(*)
  FROM  tbl_category pcat
  LEFT JOIN tbl_category subcat 
    ON subcat.parent_category_id = pcat.category_id 
  INNER JOIN tbl_auction a 
    ON a.auction_category = IFNULL(subcat.category_id,pcat.category_id)
GROUP BY pcat.category_id;

This has the same effect (double-counting each auction, once under its subcat, once under the parent cat), and the `EXPLAIN` shows no filesort. If it does when the data get larger, you may try adding the `ORDER BY NULL` trick.

Also if real-time accuracy is not super important but the query is executed frequently consider caching the result.

Problem

I need help to optimize this MySQL query for better and fast performance. Here is the `SQL FIDDLE` with Query and table structure. basically I have two tables tbl_category ``` CREATE TABLE IF NOT EXISTS `tbl_category` ( `category_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `category_name` varchar(20) NOT NULL, `parent_category_id` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`category_id`), UNIQUE KEY `category_name` (`category_name`,`parent_category_id`), KEY `category_parent_id` (`parent_category_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- data for table `tbl_auction` -- +-------------+---------------+--------------------+-----------------+ | category_id | category_name | parent_category_id | category_status | +-------------+---------------+--------------------+-----------------+ | 1 | Boats | NULL | a | | 2 | Books | NULL | a | | 3 | Building | NULL | a | | 4 | Cars | NULL | a | | 5 | Electrical | 3 | a | | 6 | Hardware | 3 | a | | 7 | Heating | 3 | a | | 8 | Miscellaneous | 3 | a | | 9 | Plumbing | 3 | a | | 10 | Tools | 4 | a | | 11 | Lights | 4 | a | | 12 | Miscellaneous | 4 | a | +-------------+---------------+--------------------+-----------------+ ``` tbl_auction ``` CREATE TABLE IF NOT EXISTS `tbl_auction` ( `auction_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `auction_category` int(10) unsigned NOT NULL COMMENT 'either store subcategory OR main category if no subcategory', `auction_title` varchar(100) NOT NULL, PRIMARY KEY (`auction_id`), KEY `auction_category` (`auction_category`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- data for table `tbl_auction` -- +------------+------------------+---------------+ | auction_id | auction_category | auction_title | +------------+------------------+---------------+- | 1 | 1 | one | | 2 | 2 | two | | 3 | 5 | five | | 4 | 6 | six | | 5 | 5 | five2 | | 6 | 8 | eight | | 7 | 11 | eleven | | 8 | 11 | eleven2 | | 9 | 10 | ten | | 10 | 2 | two2 | | 11 | 12 | twelve | +------------+------------------+---------------+ ``` Now I want to count all auctions category wise as well as if some category have subcategory then sum that auction to main category I made below query ``` SELECT auction_category AS categoryID, COUNT(*) AS total FROM `tbl_auction` GROUP BY auction_category UNION ALL SELECT parent_category_id AS categoryID, COUNT( * ) AS total FROM `tbl_auction` ta INNER JOIN tbl_category tc ON tc.category_id = ta.auction_category WHERE parent_category_id IS NOT NULL GROUP BY parent_category_id ``` although above query returns expected results But I think there may be some optimized way to solve this. kindly guide me and suggest me optimized Query. Thanks

Original source