Table size 45 million rows
Schema: item name, categories and subcategories, order quantity and price.
What is the fastest way to put each item into an appropriate bucket?
For example: price range given will be: 500- 600
We have 10 buckets: 500-510, 510-520, ...590-600
We also have filters on particular category and subcategory, i.e. specific category and subcategory have to be only considered in a query.
By bucketing, it means that all items which falls in a said range, and belongs to the particular category and subcategory, there order quantity will be summed.
What will be an optimal way to construct the query? We have a query which does all the processing in one go using
when. However, this takes around 18 sec or more mostly.
This has to be optimized. I have tried adding indexes, which is not helping much.