As part of a moving my online services between two Windows Azure subscriptions last week, I did some upgrades to the blog including moving the database to Windows Azure SQL (SQL Azure). To facilitate this, I’m using the WP DB Abstraction plugin for WordPress available from http://wordpress.org/plugins/wordpress-database-abstraction/. Using this plugin does take a bit of guts I hasten to add as it hasn’t been updated in over two years and it will prevent some plugins from functioning but for core WordPress it’s great.
After migrating the site to the new subscription I was doing some validation checking in the SQL Azure Management portal. I was querying the database for various things and I noticed that there were no indexes on any of the tables, a byproduct of the WP DB Abstraction plugin translating the native WordPress MySQL syntax into MSSQL I suspect. Luckily for me, WordPress have a great in-depth article on their Codex for the database schema, mappings for all of the primary and foreign keys and most importantly, all of the indexes.
Using the SQL Azure Management Designer, I was able to create the indexes in SQL Azure to match the WordPress MySQL specification. If you are using WP DB Abstraction for your Widows Azure Web Sites WordPress installation with SQL Azure, I strongly recommend you take a look at your own indexes to see if any exist and if not, look at all of the details on the WordPress Codex article at http://codex.wordpress.org/Database_Description for what indexes should exist.
If I get a chance in the coming days, I’ll update this post with a T-SQL snippet which you can dump into SQL Server Management Studio to create the indexes for you.