Reducing latency in read operations

Question

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 case and 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.

Answer 1

I would compute the buckets by FLOOR(price/10). Should 510 be in the 50 bucket? Or the 51 bucket? Or both?

"put each item into an appropriate bucket" sounds like an INSERT statement. But "will be summed" sounds like a SELECT statement. Which do you mean?

Assuming you are selecting all 45M rows, here is the optimal query:

SELECT  10 * FLOOR(price/10) AS 'price bucket',
        category,
        subcategory,
        SUM(order_quantity) AS 'quantity'
    FROM tbl
    GROUP BY 1,2,3

(Or do you need item in the GROUP BY?)

The real performance problem is that you need to read all 45M rows.

There might be a way to speed it up. What is the data flow? Do you insert rows and then never change or delete them? If so a Summary Table that you build and maintain (daily?) would be much smaller and faster.