How to retrieve all rows from up to down in MySQL after sum to some specific value?

Question

Let's say I have these type of data in a database table. How can I perform a query like when I pass some value it should sum from up to down rows and select all the rows which are required to sum up the value.

1. When I pass value 11 it should sum from up to down rows and sum rows with id 1 2 3 i.e (3+2+7) as 11 falls in third row range and select all rows that equals or have greater) the value.

2. Let's say if I pass 20 value it should sum row 1 2 3 4 as 20 falls in (3+2+7+10) 4th column and select all 4 rows.

ID, TABLE_COLUMN
1,2
2,3
3,7
4,10
5,11
6,15

I have tried doing this and it works but it seems so so slow. When I query in 20K data table, it takes about 20 seconds and more to retrieve 10 rows of data.

SELECT t1.ID, t1.TABLE_COLUMN
FROM yourTable t1
WHERE (SELECT COALESCE(SUM(t2.TABLE_COLUMN), 0)
       FROM yourTable t2
       WHERE t2.ID < t1.ID) < 20;

MySQL version is 5.7.12. (Amazon AWS MySQL Aurora; that means I cannot upgrade the version of MySQL).

Answer 1

If you are running MySQL 8.0, you can just do a window sum and filter by that:

select id, table_column
from (
    select 
        t.*,
        sum(table_column) over(order by id) wsm
    from mytable t
) t
where wsm >= 20 and wsm - table_column < 20
order by id 

In earlier versions, your query seems like a reasonable option to me. For performance, you want an index on (id, table_column).

Alternatively, you could try and emulate the window sum with a user variable:

select id, table_column
from (
    select 
        t.*,
        @wsm := @wsm + table_colum wsm
    from (select * from mytable order by id) t
    cross join (select @wsm := 0) v
) t
where wsm >= 20 and wsm - table_column < 20
order by id