优化几个MySQL（子）查询

Question

I have some big problems to find the best way for getting one big Table with my users plays:

The functionality works this way: People can play two different games and earn playmoney with this. Additional, they have referred user, from which they also gain amounts to their balance. My current query takes forever and looks like this:

$sql = $mysqli->query("SELECT 
    n.id, n.account, n.email, n.lastpayout, n.referupnumber, 
    (SELECT sum(r.win) FROM rolls r WHERE r.konto = n.id AND r.zeit > n.lastpayout) as sumroll,
    (SELECT sum(m.gewinn) FROM multi m WHERE m.account = n.id AND m.zeit > n.lastpayout) as summulti,
    (SELECT count(nx.referupnumber) FROM nxt_account nx WHERE nx.referupnumber = n.id) as amountref
FROM
    nxt_account n");

$amountuser = $sql->num_rows;
$c = 1;  
while($row = $sql->fetch_array()) {

    $id_thistime = $row['id'];
    $sql_ref = $mysqli->query("SELECT 
    nn.id,
    (SELECT 
            sum(rr.win)
        FROM
            rolls rr
        WHERE
            rr.zeit >= nn.lastpayout AND rr.konto = n.id) as refamount
FROM
    nxt_account nn
WHERE
    nn.referupnumber = '".$id_thistime."'");
    $total_ref = 0;

while($row_ref = $sql_ref->fetch_array()) {

    $total_ref = $total_ref + $row_ref['refamount'];

}

$total_amount = $row['sumroll'] + $row['summulti'] + $total_ref;   }

Thank you very much if you can understand this and give me any hint how to optimize these querys.

Answer 1

I suggest you change your main query to this and make all calculations from one database request:

    SELECT 
        n.id, n.account, n.email, n.lastpayout, n.referupnumber, 
        (SELECT sum(r.win) 
         FROM rolls r 
         WHERE r.konto = n.id AND r.zeit > n.lastpayout) as sumroll,
        (SELECT sum(m.gewinn) 
         FROM multi m 
         WHERE m.account = n.id AND m.zeit > n.lastpayout) as summulti,
        (SELECT count(nx.referupnumber) 
         FROM nxt_account nx 
         WHERE nx.referupnumber = n.id) as amountref,
        (SELECT sum(rr.win)
         FROM  rolls rr INNER JOIN nxt_account nn ON
            rr.zeit >= nn.lastpayout AND rr.konto = n.id
         WHERE nn.referupnumber =n.id) as refamount
    FROM
        nxt_account n

The way it is done you have as many requests as there are records in the nxt_account table. This is probably the main reason why calculation is slow.

If you just need one number of totals for all accounts, you should do that calculation in SQL also.