I couldn't figure out the problem as the site loaded normally for me and the server wasn't overloaded, however somehow I ended up hammering on the refresh button in the server tab to check what MySQL processes were running because it was fun and interesting.
My assumption turned out to be correct. This query is used to determine when the visitor last visited the website, and it took 0.3 seconds to execute. It happens to be run on every page visit on of my websites.
So, being already in phpMyAdmin, I head over to the table in question and add an index for this field.
ALTER TABLE `cc_counter` ADD INDEX ( `ip` )
Now lets run this query again:
Well that sounds much better doesn't it! From 300ms to 0.8ms. Around 375 times faster now. While we're at it, we might as well write this query correctly.
As we only require the newest visit it is enough find MAX(time) of the visitor in question. Another 40% faster, which results in a speedup of around 525 times compared to the original.
So we not only saved ~0.3 seconds of loading time per visit but also reduced the required CPU time allowing greater workload on the server.
If you are running whatever self written code on your servers or webspace, get the new phpMyAdmin 3.5, check out your database load and optimize your queries. It's very gratifying work ;-)
P.S.: I'm aware that this might be an exceptional case, since a well designed database layout would have proper indexes from the very start. Nonetheless the Status Monitor is a great tool for surgical improvements on bad code like this one. Also in projects that slowly grow over time the chances are not too low to miss out on creating an index where it would be needed.
