select 30 random rows where sum amount = x

Question

I have a table

items
id int unsigned auto_increment primary key,
name varchar(255)
price DECIMAL(6,2)

I want to get at least 30 random items from this table where the total sum of price equals 500, what's the best approach to accomplish this?

I have seen this solution which looks have a similar issue MySQL Select 3 random rows where sum of three rows is less than value

And I am wondering if there are any other solutions that are easier to implement and/or more efficient

Answer 1

The closest answer I can provide is this

set @cnt = 0;
set @cursum = 0;
set @cntchanged = 0;
set @uqid = 1;
set @maxsumid = 1;
set @maxsum = 0;
select 
    t.id,
    t.name,
    t.cnt
from (
    select 
        id + 0 * if(@cnt = 30, (if(@cursum > @maxsum, (@maxsum := @cursum) + (@maxsumid := @uqid), 0)) + (@cnt := 0) + (@cursum := 0) + (@uqid := @uqid + 1), 0) id, 
        name,  
        @uqid uniq_id,
        @cursum := if(@cursum + price <= 500, @cursum + price + 0 * (@cntchanged := 1) + 0 * (@cnt := @cnt + 1), @cursum + 0 * (@cntchanged := 0)) as cursum, if(@cntchanged, @cnt, 0) as cnt  
    from (select id, name, price from items order by rand() limit 10000) as orig
) as t

where t.cnt > 0 and t.uniq_id = @maxsumid
;

So how it works? At first we select 10k randomly ordered rows from items. After it we sum prices of items until we reach 30 items with sum less than 500. When we find 30 items we repeat the process until we walk through all the 10k selected items. While finding these 30 items we save maximum found sum. So at the end we select 30 items with greatest sum (meaning the closest to the target 500). Not sure if that's what you originally wanted, but finding the exact sum of 500 would require too much effort on DB side.

Answer 2

If you want to be efficient stop wasting your time and go for eventual consitency. Create console script that does what you want to accomplish by any means necessary, then run this script in CRON or with any scheduling software once in a while.

Having 100, 1000 visitors would you want your query to be executed every time? This is time and resource consuming. Randomly ordered queries cannot be cached by DBMS's too. Go for eventual consistency: create a table to hold that records and purge it each time, lock for writing, then load with new set, every 5 minutes for instance.

At least this is how I do it in heavily loaded applications. In the code it's matter of running plain SELECT query.