[RESOLVED] Cron being efficient?

fr8

$cron_sql = mysql_query("SELECT oid, uid, amount FROM type") or die(mysql_error());
        while($user = mysql_fetch_array($cron_sql)){
                $amount_tt = (($user["amount"] * 35) + 2500);
                $uid = $user["uid"];
                $amount_sql = mysql_query("UPDATE statistics SET total=total+$amount_tt WHERE uid='$uid' LIMIT 1") or die(mysql_error());
                }

For every row in "Type" it has a matching record in statistics that is updated. Thats alot of Queries that happens every half hour.

Is this efficient? The Database "Type" has about 75,000 rows and growing.

tomhath

That's very inefficient. You should be able to do it all in one query.

Not tested, but should be close:

UPDATE statistics SET total = total +  (type.amount * 35) + 2500
FROM statistics 
INNER JOIN type 
WHERE statistics.uid = type.uid

fr8

tomhath wrote:
That's very inefficient. You should be able to do it all in one query.

Not tested, but should be close:
UPDATE statistics SET total = total +  (type.amount * 35) + 2500
FROM statistics 
INNER JOIN type 
WHERE statistics.uid = type.uid 

get this error.

#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 'FROM statistics
INNER JOIN type
WHERE statistics.uid = type.uid

Plasma

Try a slight variation:

 UPDATE statistics SET total = total +  (type.amount * 35) + 2500
FROM statistics, type
WHERE statistics.uid = type.uid

fr8

Plasma wrote:

Try a slight variation:

 UPDATE statistics SET total = total +  (type.amount * 35) + 2500
FROM statistics, type
WHERE statistics.uid = type.uid

same error. just shows the second db now. ive even take the math out of it. just add " 0 " to it and it still errors out.

tomhath

"WHERE" should be "ON", sorry. You might need something in a WHERE clause too:

UPDATE statistics SET total = total +  (type.amount * 35) + 2500
FROM statistics, type
ON statistics.uid = type.uid

fr8

tomhath wrote:
"WHERE" should be "ON", sorry. You might need something in a WHERE clause too:
UPDATE statistics SET total = total +  (type.amount * 35) + 2500
FROM statistics, type
ON statistics.uid = type.uid 

same error. 🙁

also MySQL 4.0.25 if that might change anything.

Sxooter

wouldn't that just be "from type"

Sxooter

If that doesn't work, try a subquery with in()

update table1 set somefield=somefield+35 where id in (select id from someothertable);

4.0 has subqueries, doesn't it? If not, then at least create a big honking in list by hand:

select id from table1;
(use php to build a big in list)
$list = "1,2,3,4,5";
update table2 where id in ($list) <- list from first query...

fr8

Sxooter wrote:
If that doesn't work, try a subquery with in()

update table1 set somefield=somefield+35 where id in (select id from someothertable);

4.0 has subqueries, doesn't it? If not, then at least create a big honking in list by hand:

select id from table1;
(use php to build a big in list)
$list = "1,2,3,4,5";
update table2 where id in ($list) <- list from first query...

change it to "FROM type" as your first reply sugest. same error.

As for the second post. I lose the "type.amount" field that im using for the math.

tomhath

Hmmm, I'm used to SQLServer (TransactSQL). MySQL's UPDATE is different. This should work (I even tested it this time!) :o

UPDATE statistics,type 
SET statistics.total = statistics.total +  (type.amount * 35) + 2500
WHERE statistics.uid = type.uid

fr8

nice. plus knowing this i can apply this to similar crons i have running.

Plasma

'type' is probably a mysql codeword.

try using the word type backticked.

fr8

Plasma wrote:
'type' is probably a mysql codeword.

try using the word type backticked.

the names used were not the actual names of what i have in my database. so that wasnt an issue. as for the last post by tomhath, his sql statement fixed the problem and makes the cron work. thanks again tomhath.