|
|
Posted by smorrey on 10/26/05 19:55
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?
Navigation:
[Reply to this message]
|