MySQL is making me nuts!

[smorrey@gmail.com]

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_us er,from_amount, to_user,to_amou nt

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(my sql_query($quer y[0]);
$withdrawls = mysql_result(my sql_query($quer y[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?

[smorrey@gmail.com]

Re: MySQL is making me nuts!

Just a note I'ld thought I'ld mention.
The other logical solution is this.

SELECT deposits - withdrawls as balance FROM(
(SELECT sum(from_amount ) as withdrawls FROM transactions WHERE
from_user = '1' UNION
SELECT sum(to_amount) as deposits FROM transactions WHERE to_user =
'1' )

Which produces the following error

#1248 - Every derived table must have its own alias

I'm going into a corner to cry now.

smorrey@gmail.c om wrote:[color=blue]
> 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_us er,from_amount, to_user,to_amou nt
>
> 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(my sql_query($quer y[0]);
> $withdrawls = mysql_result(my sql_query($quer y[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?[/color]

[Steve]

Re: MySQL is making me nuts!

[color=blue]
> 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.[/color]

Forget the SQL, the syntax is all over the place. Go back to the
original queries, but create indexes on columns `from_user` and
`to_user`.

---
Steve

[Justin Koivisto]

Re: MySQL is making me nuts!

smorrey@gmail.c om wrote:[color=blue]
> 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_us er,from_amount, to_user,to_amou nt
>
> 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(my sql_query($quer y[0]);
> $withdrawls = mysql_result(my sql_query($quer y[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?
>[/color]

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.co m

http://koivi.com

[Justin Koivisto]

Re: MySQL is making me nuts!

smorrey@gmail.c om wrote:[color=blue]
> Just a note I'ld thought I'ld mention.
> The other logical solution is this.
>
> SELECT deposits - withdrawls as balance FROM(
> (SELECT sum(from_amount ) as withdrawls FROM transactions WHERE
> from_user = '1' UNION
> SELECT sum(to_amount) as deposits FROM transactions WHERE to_user =
> '1' )
>
> Which produces the following error
>
> #1248 - Every derived table must have its own alias
>
> I'm going into a corner to cry now.[/color]

at the very end of that statement, simply add " as table1" and you
should be good.

--
Justin Koivisto, ZCE - justin@koivi.co m

http://koivi.com

[smorrey@gmail.com]

Re: MySQL is making me nuts!

Woohoo! That almost worked, but this actually does!

SELECT (
deposits - withdrawls
) AS balance
FROM (

SELECT (

SELECT sum( to_amount ) AS amount
FROM transactions
WHERE to_user = '1'
GROUP BY to_user

) AS deposits, (

SELECT sum( from_amount ) AS amount
FROM transactions
WHERE from_user = '1'
GROUP BY from_user
) AS withdrawls
) AS result_table

Thank you VERY much, the difference is that the from_amount and
to_amount can and will tend to be very different, due to a fee which is
extracted in some cases, in some types of transactions. But no bother,
once I seen how to do it, it made all the difference.
Thank you!

Justin Koivisto wrote:[color=blue]
> smorrey@gmail.c om wrote:[color=green]
> > 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_us er,from_amount, to_user,to_amou nt
> >
> > 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(my sql_query($quer y[0]);
> > $withdrawls = mysql_result(my sql_query($quer y[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?
> >[/color]
>
> 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.co m
> http://koivi.com[/color]

[smorrey@gmail.com]

Re: MySQL is making me nuts!

Ok, thanks that helps quite a bit, the single user page load was cut by
25% by using phpmyadmin to create indexes on from_user and to_user,
which I assume is what you meant, since I have no clue how to create an
index any other way.

But the page load times are still spectacularly high.
It seems to me, that this should still be something handled via a
simple DB query.
I mean really it SHOULD be as simple as SELECT SUM(to_amount) WHERE
to_user = 1 - SUM(from_amount ) WHERE from_user = 1

However that doesn't appear to work either :(

Steve wrote:[color=blue][color=green]
> > 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.[/color]
>
> Forget the SQL, the syntax is all over the place. Go back to the
> original queries, but create indexes on columns `from_user` and
> `to_user`.
>
> ---
> Steve[/color]

[smorrey@gmail.com]

Re: MySQL is making me nuts!

Ok, I don't know how but this got places in the wrong order in the News
Group.
Anyways problem is solved.

smorrey@gmail.c om wrote:[color=blue]
> Ok, thanks that helps quite a bit, the single user page load was cut by
> 25% by using phpmyadmin to create indexes on from_user and to_user,
> which I assume is what you meant, since I have no clue how to create an
> index any other way.
>
> But the page load times are still spectacularly high.
> It seems to me, that this should still be something handled via a
> simple DB query.
> I mean really it SHOULD be as simple as SELECT SUM(to_amount) WHERE
> to_user = 1 - SUM(from_amount ) WHERE from_user = 1
>
> However that doesn't appear to work either :(
>
> Steve wrote:[color=green][color=darkred]
> > > 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.[/color]
> >
> > Forget the SQL, the syntax is all over the place. Go back to the
> > original queries, but create indexes on columns `from_user` and
> > `to_user`.
> >
> > ---
> > Steve[/color][/color]

[NC]

Re: MySQL is making me nuts!

smorrey@gmail.c om wrote:[color=blue]
>
> 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_us er,from_amount, to_user,to_amou nt[/color]

Why do you have from_amount and to_amount? Aren't they supposed
to be equal? Or is there a "house take" of some kind?
[color=blue]
> Given the above, I just want a simple single SQL statement that can
> return a full balance.[/color]

Bad idea. If you want a transaction system, create a transaction
system. You need to add a `balance` column to the table where
user data is stored. Both the `users` table and `transactions`
table must be InnoDB. Then you can actually do transaction
processing:

BEGIN;
INSERT INTO `transactions`
SET from_user = [from_user],
from_amount = [from_amount],
to_user = [to_user],
to_amount = [to_amount];
UPDATE `users`
SET `balance` = `balance` - [from_amount]
WHERE `id` = [from_user];
UPDATE `users`
SET `balance` = `balance` + [to_amount]
WHERE `id` = [to_user];
COMMIT;

This way, balance is readily available as a single number;
there's no need to query records from the beginning of the
universe to look up balances...

Cheers,
NC

[smorrey@gmail.com]

Re: MySQL is making me nuts!

Yes there is a house take, but there is also a percieved value here as
well.
The easiest way to understand what I am doing would be to consider it
like a currency conversion system or a market making system (in reality
this is for a role playing game with multiple regional currencies but I
think you catch my drift here.

In this instance we have USER 1 who is converting Gold to Clamshells
because USER 2 would like to be paid in ClamShells.
The transaction would look something like this.

assuming status is one of 3 values, proposed, accepted or rejected.

INSERT INTO transactions from_user, from_amount, from_cur, to_user,
to_amount,to_cu r, fee, status

And actually is more like

INSERT INTO tranactions '1', '100','gold','2 ','900','clamsh ells','100',
'proposed'

Since a user can have "accounts" in anything tradeable in game which
amounts to over 200 regional currencies, I made a decision to not have
a "balance" table which seemed to be very exploitable.

On the other hand I never considered, updating the balance table on
each transaction for each currency type involved, and will have to look
closer at it.

In the final analysis, the only purpose for doing this on the website,
is to give weary travelers a place buy and sell thier wares w/o using
an auction channel or a forum that could get crowded like in EQ.

Anyways, thanks for the advice, I'll look into it and see how it works
out.

NC wrote:[color=blue]
> smorrey@gmail.c om wrote:[color=green]
> >
> > 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_us er,from_amount, to_user,to_amou nt[/color]
>
> Why do you have from_amount and to_amount? Aren't they supposed
> to be equal? Or is there a "house take" of some kind?
>[color=green]
> > Given the above, I just want a simple single SQL statement that can
> > return a full balance.[/color]
>
> Bad idea. If you want a transaction system, create a transaction
> system. You need to add a `balance` column to the table where
> user data is stored. Both the `users` table and `transactions`
> table must be InnoDB. Then you can actually do transaction
> processing:
>
> BEGIN;
> INSERT INTO `transactions`
> SET from_user = [from_user],
> from_amount = [from_amount],
> to_user = [to_user],
> to_amount = [to_amount];
> UPDATE `users`
> SET `balance` = `balance` - [from_amount]
> WHERE `id` = [from_user];
> UPDATE `users`
> SET `balance` = `balance` + [to_amount]
> WHERE `id` = [to_user];
> COMMIT;
>
> This way, balance is readily available as a single number;
> there's no need to query records from the beginning of the
> universe to look up balances...
>
> Cheers,
> NC[/color]

[Gary L. Burnore]

Re: MySQL is making me nuts!

On 26 Oct 2005 11:40:09 -0700, smorrey@gmail.c om top posted like a
newbie and wrote:
[color=blue]
>Ok, I don't know how but this got places in the wrong order in the News
>Group.
>Anyways problem is solved.[/color]

You might also try the new comp.databases. mysql newsgroup for further
mysql questions.[color=blue]
>
>smorrey@gmail. com wrote:[color=green]
>> Ok, thanks that helps quite a bit, the single user page load was cut by
>> 25% by using phpmyadmin to create indexes on from_user and to_user,
>> which I assume is what you meant, since I have no clue how to create an
>> index any other way.
>>
>> But the page load times are still spectacularly high.
>> It seems to me, that this should still be something handled via a
>> simple DB query.
>> I mean really it SHOULD be as simple as SELECT SUM(to_amount) WHERE
>> to_user = 1 - SUM(from_amount ) WHERE from_user = 1
>>
>> However that doesn't appear to work either :(
>>
>> Steve wrote:[color=darkred]
>> > > 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.
>> >
>> > Forget the SQL, the syntax is all over the place. Go back to the
>> > original queries, but create indexes on columns `from_user` and
>> > `to_user`.
>> >
>> > ---
>> > Steve[/color][/color][/color]
--
gburnore at DataBasix dot Com
---------------------------------------------------------------------------
How you look depends on where you go.
---------------------------------------------------------------------------
Gary L. Burnore | ÝÛ³ºÝ³Þ³ºÝ³³ÝÛº ݳ޳ºÝ³Ý³Þ³ºÝ³Ý ÝÛ³
| ÝÛ³ºÝ³Þ³ºÝ³³ÝÛº ݳ޳ºÝ³Ý³Þ³ºÝ³Ý ÝÛ³
Official .sig, Accept no substitutes. | ÝÛ³ºÝ³Þ³ºÝ³³ÝÛº ݳ޳ºÝ³Ý³Þ³ºÝ³Ý ÝÛ³
| ÝÛ 0 1 7 2 3 / Ý³Þ 3 7 4 9 3 0 Û³
Black Helicopter Repair Services, Ltd.| Official Proof of Purchase
=============== =============== =============== =============== ===============