Mysql - most efficient way to retrieve data based on multiple selects and wheres

Question

I'm having trouble finding the most efficient way of retrieving various different sumed values from a Mysql table.

Let's say I've got 4 columns - userid, amount, paid, referral.

I'd like to retrieve the following based on a user id:

1 - the sum of amount that is paid (marked as 1)
2 - the sum of amount that is unpaid (marked as 0)
3 - the sum of amount that is paid and referral (marked as 1 on both paid and referral columns)
4 - the sum of amount that unpaid and referral (marked as 0 on paid and 1 on referral columns)

I've tried an embedded select statement like this:

SELECT  (
    SELECT sum(payout)
    FROM   table1
    WHERE ispaid = 0 and userid = '100'
) AS unpaid
(
    SELECT sum(payout)
    FROM   table1
    WHERE ispaid = 1 and userid = '100'
) AS paid,
(
SELECT sum(payout)
    FROM   table1
    WHERE ispaid = 0 and isreferral = 1 and userid = '100'
) AS refpending,
(
SELECT sum(payout)
    FROM   table1
    WHERE ispaid = 1 and isreferral = 1 and userid = '100'
) AS refpaid

This works, but its slow (or at least feels like it could be quicker) on my server, around 1.5 seconds.

I'm sure there is a better way of doing this with a group statement but can't get my head around it!

Any help is much appreciated.

Thanks

Answer 1

You can use conditional expressions inside SUM():

SELECT
  SUM(CASE WHEN ispaid=0 THEN payout END) AS unpaid,
  SUM(CASE WHEN ispaid=1 THEN payout END) AS paid,
  SUM(CASE WHEN ispaid=0 AND isreferral=1 THEN payout END) AS refpending,
  SUM(CASE WHEN ispaid=0 AND isreferral=1 THEN payout END) AS refpaid
FROM table1
WHERE userid = '100'

If a given row is not matched by any CASE...WHEN clause, then the value of the expression is NULL, and SUM() ignores NULLs. You could also have an ELSE 0 clause in there if you want to be more explicit, since SUM() will not be increased by a 0.

Also make sure you have an index on userid in this table to select only the rows you need.