What would be better to use?

[Lithium]

When i ask better, mainly speed concern as both queries should do the exact same thing.

 

UPDATE users u LEFT JOIN userstats us ON u.userid = us.userid
SET u.money = u.money + 1, u.crystals = u.crystals + 1, us.IQ = us.IQ + 1
WHERE u.userid = $userid

 

or this one...

 

UPDATE users, userstats
SET users.money = users.money + 1, users.crystals = users.crystals +1, userstats.IQ = userstats.IQ + 1
WHERE users.userid = $userid AND userstats.userid = $userid

[John99]

Re: What would be better to use?

Meh thinks the second one

[Haunted Dawg]

Re: What would be better to use?

I ran a test on those.

My one:

UPDATE `users` `u`,`userstats` `us` SET `money` = (`money` + 1),`crystals` = (`crystals` + 1), `IQ` = (`IQ` + 1) WHERE `u`.`userid` = 3 AND `us`.`userid` = 3

Result: Rows Affected: 2 (Took 0.0009 seconds)

Your first one:

UPDATE users u LEFT JOIN userstats us ON u.userid = us.userid
SET u.money = u.money + 1, u.crystals = u.crystals + 1, us.IQ = us.IQ + 1
WHERE u.userid = 3

Result: Rows Affected: 2 (Took 0.0011 seconds)

Your Second One:

UPDATE users, userstats
SET users.money = users.money + 1, users.crystals = users.crystals +1, userstats.IQ = userstats.IQ + 1
WHERE users.userid = 3 AND userstats.userid = 3

Result: Rows Affected: 2 (Took 0.0010 seconds)

Seems like each one has got different speeds to them.

Then again ran a test on all 3 again and results ended up as follows:

1st => Mine. 5 Seconds

2nd => Yours First. 7 Seconds

3rd => Yours Second. 9 Seconds

[Lithium]

Re: What would be better to use?

heh, that is why i was wondering...

i got for the first...

Affected rows: 2 (Query took 0.0010 sec)

and for the second

Affected rows: 2 (Query took 0.0006 sec)

[Floydian]

Re: What would be better to use?

Unless you're planning on running that query 10 million times, the difference between either of those doesn't amount to a hill of beans.

[Lithium]

Re: What would be better to use?

The queries are just an example, though i will need to run similar queries on tables with a few thousand entries. I made a few tests, and even second query is faster for lower amount of queries, as they grow... second one becomes painfully slow!

[Haunted Dawg]

Re: What would be better to use?

Asigning which is which is very good.

For example:

UPDATE `users` `u`, `userstats` `us`

Is something similar to

SELECT u.blah,us.blah FROM `users` `u`, `userstats` `us

[Karlos]

Re: What would be better to use?

How would

 

UPDATE `users` u LEFT OUTER JOIN `userstats` us ON u.`userid`=us.`userid`
SET u.`money`=u.`money`+'1', u.`crystals`=u.`crystals`+'1', us.`IQ`=us.`IQ`+'1'
WHERE u.`userid`='1'

or even

UPDATE `users` u INNER JOIN `userstats` us ON u.`userid`=us.`userid`
SET u.`money`=u.`money`+'1', u.`crystals`=u.`crystals`+'1', us.`IQ`=us.`IQ`+'1'
WHERE u.`userid`='1'

 

perform?

[Vali]

Re: What would be better to use?

Test it out:

>> http://dev.mysql.com/doc/refman/5.0/en/explain.html

[Floydian]

Re: What would be better to use?

hey hey hey, let's not neglect the old RIGHT JOIN ;)