Lithium Posted March 9, 2009 Share Posted March 9, 2009 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 Quote Link to comment Share on other sites More sharing options...
John99 Posted March 9, 2009 Share Posted March 9, 2009 Re: What would be better to use? Meh thinks the second one Quote Link to comment Share on other sites More sharing options...
Haunted Dawg Posted March 9, 2009 Share Posted March 9, 2009 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 Quote Link to comment Share on other sites More sharing options...
Lithium Posted March 9, 2009 Author Share Posted March 9, 2009 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) Quote Link to comment Share on other sites More sharing options...
Floydian Posted March 9, 2009 Share Posted March 9, 2009 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. Quote Link to comment Share on other sites More sharing options...
Lithium Posted March 9, 2009 Author Share Posted March 9, 2009 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! Quote Link to comment Share on other sites More sharing options...
Haunted Dawg Posted March 9, 2009 Share Posted March 9, 2009 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 Quote Link to comment Share on other sites More sharing options...
Karlos Posted March 14, 2009 Share Posted March 14, 2009 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? Quote Link to comment Share on other sites More sharing options...
Vali Posted March 14, 2009 Share Posted March 14, 2009 Re: What would be better to use? Test it out: >> http://dev.mysql.com/doc/refman/5.0/en/explain.html Quote Link to comment Share on other sites More sharing options...
Floydian Posted March 14, 2009 Share Posted March 14, 2009 Re: What would be better to use? hey hey hey, let's not neglect the old RIGHT JOIN ;) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.