Scat, I think we're going to have to come up with something different for server stats than REPLACE INTO. A World just sitting idle even with no players, the stats threads are running, updating. Well, what I had forgotten about REPLACE INTO is that the function doesn't update the existing record in-place, but deletes the old one and replaces it with a new insert.
http://dev.mysql.com/doc/refman/5.0/en/replace.html
Look at dev's `statistics` table sometime. We've had stats on Dev for maybe a few weeks, and the PK for the table is already over 41,000. I just see no reason why that ID should ever grow to the millions, which it will on long-running servers. Maybe it's just my quirky dislike of things that make no useful sense...
So I'll see if I can come up with an alternative. Maybe if it exists, hit an UPDATE query, otherwise INSERT. Not sure if there's an UPDATE INTO, but I'll check. Either way, toggling between UPDATE and INSERT shouldn't really be a problem.
Performance considerations:
Please note that REPLACE INTO is a much slower performer than an UPDATE statement. Keep in mind that a REPLACE INTO requires a test on the keys, and if a matching unique key is found on any or all columns, a DELETE FROM is executed, then an INSERT is executed. There's a lot of management of rows involved in this, and if you're doing it frequently, you'll hurt your performance unless you simply cannot do with any other syntax.
The only time when I can see where you'd actually need a REPLACE INTO is when you have multiple unique constraints on a table, and need to drop any rows that would match any of the constraints. Then REPLACE INTO becomes more efficient from DELETE FROM... INSERT INTO...
If you're looking at a single unique column table (Primary Key), please use UPDATE, or INSERT. Also, check out INSERT ... ON DUPLICATE KEY UPDATE... as an alternative if you're willing to stick to MySQL 4.1+
I tested this query in query editor, and it seems to do what I want.
Code: Select all
INSERT INTO statistics (char_id, guild_id, stat_id, stat_value, stat_date) VALUES (1, 0, 1000, 18, 1243792314)
ON DUPLICATE KEY UPDATE char_id = 1, guild_id = 0, stat_id = 1000, stat_value = 17, stat_date = 1243792314;
I'll change dev and give this a try, see what happens
and can't figure out why.
and it is exactly how SOE does it. Each of their computers holds x amounts of zones. I remember when we would have x8 vs x8 battles in Kylong plains, the people raiding Veeshan's Peak would lag out because the developers had to actually intervene and tell us that the two zones were on the same server 