Igneous: this is a "zero day attack" mitigation. There is no specific known security threat with using wp_ as the prefix, but I think the idea is that through obfuscation of your table names it might save you from some hypothetical future attack that relies on the knowledge that everybody using WordPress has identical table names.
DebNCgal et al: Regarding the process for changing prefixes if you're on a hosted server:
Many hosting companies don't allow ALTER rights (including, apparently, Godaddy). This is probably good, based on my limited knowledge of MySQL security.
There are numerous websites that explain workarounds if this is the case for you. For me, a nice safe process with plenty of fallbacks in case I !@#$ed it all up was as follows:
- Backup your tables: http://codex.wordpress.org/Backing_Up_Your_Database (fallback #1: worst case, restore from this file)
- Open the ".sql" backup file that you've just created in the text editor of your liking, and replace all instances of "wp_" with whatever your desired new table prefix is; SAVE AS a new file (fallback #2, you still have the original ".sql" file from step 1 in case this modified SQL dump file doesn't work out)
- Now, create a NEW empty database on your hosted server. This process varies by hosting company. For Godaddy users, for example, you just login to your hosted account, select Databases > MySQL > Create Database. Once you have your new database created, upload the modified ".sql" file to this database; that is, the .sql file with the new prefixes in it. You can probably use these instructions to accomplish this keeping in mind that you're not *really* restoring from a backup, but populating a new database using your saved data: http://codex.wordpress.org/Restoring_Your_Database_From_Backup
- Locate your wp_config.php file. Save a copy as wp_config.php.backup (fallback #3, you can revert to the "old" wp_config file, which points to the "old" and untouched original database). Edit the wp_config file so that it contains your new database information including the new username, database name, password, and $table_prefix (~line 11).
- Now the fun part. Until now, you haven't changed anything on your blog. It's still using the original, unmodified database. Everything you've done until now has happened in the background with no effect on your website. Now you're going to upload the modified wp_config.php file into your blog's root directory. You're telling WordPress to use the new database with the shiny new prefixes in it. If something goes wrong, just upload the backup wp_config.php file and WordPress will use the old, untouched database.
Like you, I'm nervous about any automated database modifications, so this process should give you the peace of mind that should something disastrous happen, you have several immediate options to revert back to the way things were.
NOTE: If you encounter the "You do not have sufficient permissions to access this page" error, follow these instructions here.
Hope this helps!