wordpress | Geek Tank

Improve WordPress Comments Load time when using Akismet: Adding an index to wp_comments

byJordan
July 22, 2019
1 minute read

A customer of mine was seeing a 15 second load time on their WordPress site when clicking on “Comments”.

I installed Query Monitor and saw that the Akismet plugin was running the following SQL command on each comment.

SELECT COUNT(*) FROM wp_comments WHERE user_id = 1820 AND comment_approved = 1;

This query was to show how many comments the user had previously had approved. The query was taking 0.5 seconds per comment listed when you visit the “Comments” page. The page was taking around 15 seconds to load at times. Now if you had this page set to load 100 comments per page, then you’re looking at 60 seconds.

I decided to run an explain on the query to figure out what was going on.

explain SELECT COUNT(*) FROM wp_comments WHERE user_id = 778 AND comment_approved = 1;

Which resulted in the following. (Sorry for the screenshot, copy/pasting and reformatting was not something I wanted to do. Also this references the wp_bspr_comments. This is due to the database having a wp_bspr for it’s table prefix.

As you can see, the query is having to go through 19866 rows within the wp_comments table which is the total amount in the table. Since they’re using count(*) all rows are scanned.

So I decided to add an index for the user_id and comment_approved columns.

create index wp_comments_askismet ON wp_comments (user_id,comment_approved);

No when running an explain, there are only 9 rows scanned.

Load times were reduced dramatically. However, this can all be mitigated if you simply turn of the Akismet feature.

Akismet option to show number of approved comments beside each comment author.

DDrafts

WordPress Speed Up Analysis 2019-06-03

byJordan
June 4, 2019
3 minute read

The following was a post I made on the WordPress Speed Up FaceBook Group. I thought it was worth posting here in-case someone else might be looking for something similar.

Olly Bee Excellent! First load for me was a white page without CSS being applied. Which is weird, I haven’t gone to the site before. So you might want to watch out for that, or maybe you were just making changes.

You’re not loading many assets which is great, mostly due to breeze combining css and js (assumption here since I see breeze.css). Just to note minification isn’t required for a great experience for customers. It’ll get you get you good scores on GTMetrix/PageSpeed but you’ll have to way usability for great gtmetrix scores.

Ue the built in Dev Tools in Chrome and look specifically at the Network page and waterfalls. Find items that are taking a long time to load, and see if they have any cache headers. Reload the page and see if they’re loading faster and if the cache headers appear.

When looking at all the items loading, the document items are usually html or PHP. This is where server resources come into play. If you’re getting higher load times on documents, it’s most likely due to caching not being in-play. Or it’s just a dynamic page (PHP) that can’t be cached (WooCommerce checkout, but even then LiteSpeed can use ESI blocks which makes it easier). If you can’t cache a page, then the server resources come into play. Specifically CPU, PHP its a CPU hog. Facebook tried to fix this by implementing compiled PHP using HHVM, but ditched PHP all together. Each time you have to load a PHP page, it has to be compiled by the server. You want to limit this as much as possible. WordPress deals with this fine as a vanilla installation. Once you add plugins, load times start to increase.

When I first loaded your portfolio page, I noticed it took about 3ms to load all assets. I looked at all the headers of each request and found that the main request for /portfolio had a x-cache miss and a high load time. Once I refreshed the page this load time went down. So it’s possible caching was put into play here either through Varnish or NGiNX fastcgi cache. Hard to tell, would have to look at your stack more.

If there’s caching in play, a header should appear. Most of the time this is configured, but not always.

Once your cache is filled, it looks like your pages load faster. So you might need to tweak your cache TTL. Which just tells the cache how long to keep things. This is great for static pages, but for anything dynamic it might cause issues. Most caching plugins deal with this automatically. Even CloudFlare does well with this if you setup the proper page rules. CloudFlare by default doesn’t do well with a WordPress site.

Aside from that, I see that .tff files aren’t being cached and have 400ms load tims where all your images are loading within 20ms which is great. That’s because most of them are from your StackPath CDN. And they’re small, which helps!

Another issue I noted is that your query stringed static assets aren’t being cached. Which is a common issue, so you can either enable this in CloudFlare via a rule. Or you can look at a plugin like Clearfy or Perf Matters. If you want to save money, there is code out there to simply just do this specific task.

Also, with the above plugins you can remove the wp-emoji-release.min.js and dashicons.min.css from loading. Again you most likely aren’t using them, but test anyways.

Hope that helps, Cheers!

DDrafts

Searching WordPress MySQL Dump Files

byJordan
May 30, 2019
1 minute read

From time to time, you’ll need to look at data within MySQL dump files, specifically I do it mostly with WordPress. There isn’t an easy way to search the dump unless you use grep. Here are some useful grep commands.

Pull out a table.

[enlighter linenumbers=”true”] grep ‘INSERT INTO `wp_bspr_users`’ dump.sql > /tmp/users.sql [/enlighter]

Format a table in a readable format.

[enlighter linenumbers=”true”]cat dump.sql | grep ‘INSERT INTO `wp_users`’ | sed ‘s/),/’$’n/g’ [/enlighter]

Now if you run a popular shell like csh, you can add the following function.

[enlighter linenumbers=”true”]msd () { grep “INSERT INTO \`$2\`” $1 | sed “s/),/\’$’\n/g” }[/enlighter] [enlighter linenumbers=”true”]Then run the following[/enlighter]

msd dump.sql wp_users

This might work, don’t know!

UUncategorized

CloudFlare Cache Purge Plugin Logs to Posts

byJordan
February 7, 2015
1 minute read

You might have seen the following Posts in your WordPress blog after installing the CloudFlare Cache Plugin.

SUCCESS : automatic purge url cache for wordpress plugin

This is actually something the plugin is suppose to do, but there is no option to turn it off. And it looks like the plugin hasn’t been updated in months.

https://wordpress.org/support/topic/option-to-turn-off-logging?replies=3#post-6538553

You can disable this by commenting out the following line as per the support article above

I had to comment the line 86 (//wp_insert_post( $log_entry );) in cloudflare_cachepurge.php.

UUncategorized

Remove iThemes Security Lockouts Script

byJordan
December 9, 2014
2 minute read

I decided to create a command line PHP script that would list the current iThemes Security Logs and also allow you to remove them based on IP Address. Here is the script which I’ve called “ithemes-clearip.php” and it’s Github link, its dirty and really was made in like 15 minutes.

https://github.com/jordantrizz/wordpress-scripts

<?
// List and remove iThemes Security Log entries based on IP Address
// Options
$shortopts ="d:";
$shortopts .="l";
$options = getopt($shortopts);
//Database Connect
require_once("wp-config.php");
$link = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$database_name = DB_NAME;
if ($conn->connect_error) {
  die("Cant connect to database using wp-config.php details - Connection failed: " . $conn->connect_error);
}
if(!$options) {
  print "There was a problem reading in the options.\n\n";
  exit(1);
}
if(array_key_exists("d",$options)) {
  $ip = $options["d"];
  if(filter_var($ip, FILTER_VALIDATE_IP)){
    echo "Deleting all entries for IP Address ". $options["d"] ." in $database_name\n\n";
    $sql="delete from ".$database_name.".wp_itsec_log where log_host = \"".$ip."\"\n\n";
    echo $sql;
    if($link->query($sql) === TRUE) {
      echo "Deleted ". mysqli_affected_rows($link)." rows\n\n";
    } else {
      echo "Error deleting record: " . $conn->error;
    }
  } else {
    print "You didn't specify a correct IP Address.\n\n";
    exit(1);
  }
} elseif(empty($options["l"])) {
  $sql = 'SELECT log_host,log_date from wp_itsec_log';
  $result = $link->query($sql) or die(mysql_error());
  if (!$result) {
    print 'Could not run query: ' . mysql_error();
    exit;
  }
  print "\nLog Host\tLog Date";
  print "\n-----------------------------------";
  while($row = mysqli_fetch_row($result)) {
    print "\n".$row['0']."\t".$row['1'];
  }
  print "\n";
  mysqli_close($link);
}
?>

UUncategorized

How to Disable the Displet Pop WordPress Plugin on Specific Pages

byJordan
November 19, 2013
1 minute read

You won’t find much within the support forums in-regards to how to stop the Displet Pop plugin from loading on specific pages. However, I was able to find some pre-created code that does accomplish the task and just modified it for the Displet Pop plugin.

function remove_displet_pop() {
$ids = array(1358,1226); // ID's of the pages you don't want displet pop to run on
if(is_page($ids)):
remove_action('wp_print_styles', 'displetpop_scripts');
remove_action('wp_head', 'displetpop_action');
wp_dequeue_script( 'displetpop_scripts' );
endif;
}
add_action('wp_head', 'remove_displet_pop', 1);

UUncategorized

WordPress and Protecting Directories with .htaccess

byJordan
May 29, 2012
1 minute read

You’ll find sometime that when you deploy WordPress in order for permalinks to work correctly you need a proper .htaccess file. This .htaccess also affects anything else you place within your web root when WordPress lives.

For instance if you need to password protect a directory within the your webroot where WordPress lives. You’ll find that when you do so, and try to visit that folder in a web browser that a 404 WordPress page will be displayed. To fix this, you simply need to add the following to your main .htaccess file that has your WordPress rules.

ErrorDocument 401 default

This should then allow you to use your password protected directories as you normally would if you didn’t have WordPress installed.

Source: http://www.andrewrollins.com/2008/01/22/wordpress-and-htaccess-password-protected-directories/

UUncategorized

WordPress Permalink Sub-Directory 404 Error Weirdness

byJordan
August 14, 2011
1 minute read

I didn’t really know what to put for the title of this post, but hopefully the content makes more sense and gets indexed properly and found by people looking for this specific information.

The problem is when you install WordPress and setup Permalinks which uses a .htaccess file for Apache. The .htaccess make cause problems with other applications or HTML files you have in sub-directories in your document root.

The fix? It’s simple, just view this blog post. It explains to fix the problem you simply place “ErrorDocument 401 default” at the top of your .htaccess file and everything is resolved. Your WordPress installation still generates 404 pages, but now sub-directories work correctly.

http://www.geoffblog.com/2009/04/wordpress-htaccess-clashes-with-sub.html

UUncategorized

Forget TwitterUpdater Use WordTwit!

byJordan
June 25, 2010
1 minute read

Alright, I was stumbling around looking for some things and found this wonderful plugin called WordTwit

This plugin is awesome! Not only is it really clean and done well, but it has multiple URL Shortening services. No Bit.ly login support at the moment. However there is one great thing about it that I love. The fact that each post has this little box under tags, that tells you whether the post has been tweet’d or not.

Now that’s really awesome. I love this plugin!