Tuning my wordpress plugin
I run this site on a smaller sized Accelerator (ah the beauty of root access). Because I have limited resources and memory, it’s essential to tune anything I can to use less. So last night I was taking a look at my MySQL slow query log to see what queries were taking the longest (most likely not using indexes properly). The only query that shows up really often is for my jVisitors plugin that tracks who visits the site:
select id, vs_ip, vs_session, vs_user_agent,
vs_country, vs_os, vs_os_ver, vs_browser, vs_browser_ver,
date_format(DATE_SUB(vs_date, INTERVAL 6 HOUR),’%a, %e/%m/%Y’) as vs_dte,
date_format(DATE_SUB(vs_date, INTERVAL 6 HOUR),’%h:%i:%s%p’) as vs_time
from joe_visitors
where upper(vs_browser) not like ‘%GOOGLE%’ and upper(vs_browser) not like ‘%GOOGLE BOT%’ and upper(vs_browser) not like ‘%BOT%’ and upper(vs_browser) not like ‘%MSN%’ and upper(vs_browser) not like ‘%MSNBOT%’ and upper(vs_browser) not like ‘%YAHOO%’ and upper(vs_browser) not like ‘%YAHOO! SLURP%’ and upper(vs_browser) not like ‘%ZYBORG%’ and upper(vs_browser) not like ‘%W3C%’ and upper(vs_browser) not like ‘%SURVEY BOT%’ and upper(vs_browser) not like ‘%ALEXA%’ and upper(vs_browser) not like ‘%BECOMEBOT%’ and upper(vs_browser) not like ‘%CRAWLER%’ and upper(vs_ip) not like ‘%127.0.0.1%’ and upper(vs_ip) not like ‘%82.69.92.174%’ and upper(vs_ip) not like ‘%24.83.205.219%’ and upper(vs_ip) not like ‘%60.34.15.16%’ and upper(vs_ip) not like ‘%64.158.138.48%’ and upper(vs_ip) not like ‘%64.210.196.198%’ and upper(vs_ip) not like ‘%64.246.161.30%’ and upper(vs_ip) not like ‘%65.214.36.60%’ and upper(vs_ip) not like ‘%65.88.178.10%’ and upper(vs_ip) not like ‘%66.147.154.3%’ and upper(vs_ip) not like ‘%66.151.189.7%’ and upper(vs_ip) not like ‘%66.180.233.4%’ and upper(vs_ip) not like ‘%66.196.90.%%’ and upper(vs_ip) not like ‘%66.196.91.%%’ and upper(vs_ip) not like ‘%66.249.64.79%’ and upper(vs_ip) not like ‘%66.250.128.131%’ and upper(vs_ip) not like ‘%66.98.170.93%’ and upper(vs_ip) not like ‘%68.142.249.160%’ and upper(vs_ip) not like ‘%68.142.249.201%’ and upper(vs_ip) not like ‘%68.142.250%’ and vs_os!=” and vs_browser!=” and 1 order by vs_date desc LIMIT 70110, 10;
That is one hell of a query. And I quickly found out that MySQL won’t use indexes that use database functions like upper(), or use indexes with ‘not like’ or anything with %’s around it. So that really throws this query out of the question, unless it can be re-written.
So the first thing I did was found out where in the PHP code this query was being executed, which turned out to be in the plugins/wp_jvisitors.php file. I searched for upper and found right where it was. What I noticed was it shouldn’t have to translate the vs_ip into an upper because vs_ip stores an IP address, it should be the same all the time, so I took that out. Then I started checking vs_browser for GOOGLE BOT, MSNBOT and all the matches in the query and realized it wasn’t matching anything. This query didn’t even need all the ‘not like’ matching and removing it would most likely speed the query up. Then a further look and the top of the file showed some configuration settings:
// Enter the IP you want to prevent from being listed
// Separate values with comma ‘,’. Set FALSE to disable blocking
$jBlockIP = “127.0.0.1,82.69.92.174,24.83.205.219,60.34.15.16,64.158.138.48,64.210.196.198,64.
246.161.30,65.214.36.60,65.88.178.10,66.147.154.3,66.151.189.7,66.180.233.4,66.196.90.%,66.196.91.%,66.249.64.79,66.
250.128.131,66.98.170.93,68.142.249.160,68.142.249.201,68.142.250″;// Enter the search engine bot names (identified as Browser in jVisitors) you want to prevent from being listed
// Separate values with comma ‘,’. Set FALSE to disable blocking
$jBlockBrowser = “Google,Google Bot,Bot,MSN,MSNBot,Yahoo,Yahoo! Slurp,ZyBorg,W3C,Survey Bot,Alexa,B
ecomeBot,Crawler”;
All I had to do was set these to false to not use those options and now I see no more slow queries. So if you ever click on the “Total Visitors” link at the bottom of my blog, you may notice it’s a bit snappier now 