|
|
Posted by Justin Koivisto on 10/26/05 20:26
smorrey@gmail.com wrote:
> Hello all, this might better be suited for the MySQL newsgroup, but I
> figured I'ld post here and see if anyone can help me.
>
> I'm trying to create a simple transaction handling system where users
> pay eachother via points of a sort.
> I have a table where each transaction is stored, in a single row.
> It looks like this
>
> transid,from_user,from_amount,to_user,to_amount
>
> Given the above, I just want a simple single SQL statement that can
> return a full balance.
> Now originally I did this in PHP by querying the DB with the following.
>
> $query[0] = "SELECT SUM(to_amount) WHERE to_user = '$user->uid'";
> $query[1] = "SELECT SUM(from_amount) WHERE from_user = '$user->uid'";
> $deposits = mysql_result(mysql_query($query[0]);
> $withdrawls = mysql_result(mysql_query($query[1]);
> $balance = $deopsits - $withdrawls;
>
> This works great, so I populated the DB with over 100,000 records and
> now page loads take 75-80 seconds for the total balance sheet page (I
> just loop through each user, and there are 100 users), and upwards of
> 10 seconds on a single user query.
>
> After analyzing the code in the profiler (I'm using Zend), I found the
> largest optimization I could make would be to cut out the 2 queries
> variable assignment and simple math (basically all of the code above),
> and move it into a single SQL statement that does all the math.
>
> This SHOULD be elementary, but unfortunately, MySQL doesn't like my
> solution, and searching everywhere I could, I can't seem to find where
> anything similar has every been tried. But come on, I know I can't be
> the first person in history with a similar need for a query like this.
>
> Here is the most elegant solution I could come up with, in all it's raw
> SQL glory.
>
> SELECT SUM(to_amount - from_amount) as balance FROM
> ((SELECT from_amount as withdrawls FROM transactions WHERE from_user =
> '1')
> (SELECT to_amount as deposits FROM transactions WHERE to_user = '1'));
>
> Sadly, even though by every measure I can find it SHOULD work, it just
> doesn't.
> I keep getting
>
> #1064 - You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near '(SELECT to_amount as deposits FROM transactions WHERE to_user =
> '1'))' at line 3
>
> I'm just not seeing it, as far as I can tell this should work.
> Any Ideas?
>
check your mysql version, sub-queries (select inside select) are still
relatively new (v4.1+)...
SELECT
(deposits - withdrawls) as balance
FROM
(
SELECT
(
SELECT sum(amount) as amount
FROM transactions
WHERE to_user = '1'
GROUP BY to_user
) as deposits,
(
SELECT sum(amount) as amount
FROM transactions
WHERE from_user = '1'
GROUP BY from_user
) as withdrawls
) as result_table
You may need to edit a little as I didn't use the from_amount since I
was assuming that the amount of the transaction would be equal for the
to and from sides... transactions table would have the following fields:
id, from_user, to_user, amount
HTH
--
Justin Koivisto, ZCE - justin@koivi.com
http://koivi.com
Navigation:
[Reply to this message]
|