Originally Posted by St.Geezy
(having owned a database) it takes just as long for the database to change your avatar or signature.
Yeah.. so you're wrong. In a properly normalized database, you'd have a schema where the user and post table relationship is such that the post table (where posts live) keys (based on userid) off of the user table so that changing a username is as simple as updating a single row in the user table, and bam you're done.
However, due to the way vBulletin is designed, they chose to denormalize the database to avoid having to join on the user table each time you view a thread (to determine everyone's usernames). Therefore, they store a copy of the username in the post table in each row (post), as well as the user table.
Therefore, in order to change a user's username, you have to scan every row in the post table, to ensure that every post record's username field is updated to reflect the new username.
This sounds ok, until you realize that not only do we have nearly 62,000,000 posts to go through, but that we've split the post table into 10 tables for performance reasons.
Coupled with the fact that in order to do a name change, you must do a full table scan and update each post a user has, which requires locking the post table for writes, you end up hanging the entire site and inconveniencing everyone for the amount of time it takes to run this query (or 11 queries in our case). I haven't actually attempted it since the last time we did a hardware on the primary database server, but last time we tried it would effectively take the entire site down for 5-10 minutes.
..all to change a user's username.