[patch][6908] faster setting online=0 at startup

[balrok]

who has written the patch?

balrok

what should this patch do?

speed up server starting

for what revision?

rev @ 13.12.

i compared with phpmyadmin:

0 row(s) affected. ( Query took 61.5304 sec )

UPDATE characters SET online =0

0 row(s) affected. ( Query took 0.0012 sec )

UPDATE characters SET online =0 WHERE online <>0

edit:

when i run every update several time the first one goes down to 1.x seconds

and the second command goes down to 0.000x seconds

mysql version:5.0.51a

so i made this fix

diff --git a/src/mangosd/Master.cpp b/src/mangosd/Master.cpp
index 1b69292..f5ef907 100644
--- a/src/mangosd/Master.cpp
+++ b/src/mangosd/Master.cpp
@@ -459,7 +459,7 @@ void Master::clearOnlineAccounts()
        "AND id IN (SELECT acctid FROM realmcharacters WHERE realmid = '%d')",realmID);


-    CharacterDatabase.Execute("UPDATE characters SET online = 0");
+    CharacterDatabase.Execute("UPDATE characters SET online = 0 WHERE online<>0");
}

/// Handle termination signals

after that, i searched the code for other similar queries with

grep -i "execute.*UPDATE" game/WorldSocket.cpp | grep -vi "WHERE"

and only found those queries:

Level2.cpp:3140:        WorldDatabase.PExecuteLog("UPDATE creature_movement SET wpguid = '0'");
Level3.cpp:4567:    CharacterDatabase.PExecute("UPDATE characters SET at_login = at_login | '%u'",atLogin);

[Edoz]

i like optimizations

[charlie2025]

Yes, big difference - 61.5304 and 0.0012 s .. can't belive

[VladimirMangos]

In [6908]. Thank you!

[Edoz]

by the way, shouldn't MySQL do this by default? i mean, it's a pretty obvious optimization not to write something that is already written...

[Tassader2]

by the way, shouldn't MySQL do this by default? i mean, it's a pretty obvious optimization not to write something that is already written...

SQL should chose most optimal algorithm to process query, but in many cases it does not (compare subquery vs. join). Maybe post on MySQL forum?