WordPress performance problem with many posts

If you have a ton of posts in your WordPress blog (we have over 35K in one site at work), it turns out that the Previous and Next links on each post may be running a tough query on your database.

I wanted to know why MySQL was using so much CPU and wrongly assumed it was due to a bad tuning effort (it usually is). I googled “SELECT p.ID FROM wp_posts AS p INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id” which was in my output of MySQL’s show full processlist command. It led me to this StackExchange page which showed an alternative, more efficient version of the WP function calls that produce those previous and next links.

In our case, we just didn’t need those links and our theme let us turn them off from the admin. An instant and dramatic drop in CPU by MySQL ensued.

MySQL engines, constraints & keys

I wanted to see how I could improve the performance of a MySQL database with mixed table engines by converting all the MyISAM tables to InnoDB, as well as make the huge DB responsive while backing up by using mysqldump with the --single-transaction option. I used the following PHP script (I know, spare me):

After looking at the table status following the script run, there was one table which was still set to use MyISAM. In the mysql shell, I tried manually altering the one table to use InnoDB, and then needed a bunch of additional commands to smooth out the DDL problems before MySQL was happy.

Of course, I edited out a whole mess of trial and error here. The issue was that MySQL wants any column you mark as auto_increment to have its own key, it doesn’t need to be primary; but it can’t be an aggregate which is what it had originally. My solution was to simply add a non-primary key to that column, while keeping the aggregate key. Using a primary key was out of the question since there are duplicate values in the aid column.

EDIT:
Turns out, all I really needed to do was add the individual index, then alter the table engine. Oh well, it was educational. :-)