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
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 |
+-------------+---------------+--------------------+-----------------+
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With