Today one of our wordpress sites had very high server load and it was being caused by MySQL
So I went to the mysql console, and looked up the process list:
So this guy is appearing a lot
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';
Let’s see how it’s behaving with explain
explain SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';
It’s scanning 226k rows to get its search results!
Probably some moronic plugin is doing this and wordpress does not add an index on that table. The solution is simple, let’s add an index!
ALTER TABLE wp_options ADD INDEX (`autoload`);
Now let’s run explain again
From scanning 226k it went down to 408!, 3 orders of magnitude drop.
And now the CPU load went below 4%, crisis averted.
