Your WordPress website is about to undergo extremely high traffic conditions and you want to be prepared. You have done everything you can on the server side, but the database is increasingly becoming a bottleneck.
Table Engine Type
Make sure your tables have the InnoDB engine type. MyISAM puts table locks on when writing – InnoDB uses row locks and can read and write different rows at the same time. This is one of many reasons to use InnoDB rather than MyISAM.
Table Column Indexes
Indexes are fantastic for fast selects and absolutely terrible for insert/delete/update queries. Each time an insert/delete/update occurs, the entire index needs to be rebuilt. Remove column indexes on tables which perform lots of these types of queries.
User Registration Clogging with _transient_random_seed
Deep in the bowels of WordPress, there is a function which makes passwords slightly more random. This would be great if it didn’t cause the database to bog down to a halt. Search for “random_seed” in file wp-includes/pluggable.php and comment these two lines:
if ( ! defined( 'WP_SETUP_CONFIG' ) )
Disable All Unused Plugins
This goes without saying, but it should still be on the checklist. Whatever developer plugins were installed, preferably delete them, but definitely disable them. Delete/disable anything and everything that you can.
Enjoy your new squeaky clean, high load WordPress database!