Ben Lobaugh Online

I think, therefore I am. I am, therefore I sail

Category: MySQL (Page 1 of 4)

Find total number of post records on a WordPress Multisite in MySQL

I was recently asked for the total number of posts on a WordPress website. Normally as simple as looking at the number in the dashboard, however in this instance the WordPress install was a Multisite with nearly 100 sites. That would have taken far too long to do manually, but what about having MySQL do the heavy lifting for me?

The information_schema database contains data about every database and table on the server. One of the metrics it stores is the count of rows in a table. We can use that to extrapolate roughly how many entries are in all the posts tables combined. This is not an exact science, however it will get you close to the real numbers. I argue that it will typically be “close enough” on larger installs, such as the one I was looking at.

In the following query, just replace the database name with your database and run it.

SELECT SUM( table_rows )
FROM information_schema.TABLES 
WHERE 
	table_schema = "{{YOUR_DATABASE_NAME}}" 
	AND table_name LIKE '%_posts';

Find and update WordPress posts with comments older than NNN days

I have a use case where I need to disable comments on posts in WordPress where the last comment was more than one year, or 365 days, ago.

Posts can be found with this query:

SELECT p.ID, p.post_title, p.post_date, p.comment_status, p.`ping_status`
FROM wp_posts AS p
WHERE p.ID IN (
     SELECT c.comment_post_ID
     FROM wp_comments AS c
     WHERE DATEDIFF( curdate(), c.comment_date ) <= 365
  );

The outer query takes the inner query as the search parameter. The inner query is finding the latest comment for each post based on the number in the WHERE condition. 365 represents 365 days, or 1 year.

Therefore, this query is getting the latest comment for post where the comment is less than 1 year old.

Now let’s say you need to update the posts to disable comments. The goal is to disable commenting for all posts that have not had a comment within the last year. It can be accomplished with the following:

UPDATE wp_posts
SET comment_status='closed', ping_status='closed'
WHERE ID IN (
		SELECT c.comment_post_ID
		FROM wp_comments AS c
		WHERE DATEDIFF( curdate(), c.comment_date ) >= 365
);

Here again the number 365 represents the number of days.

Check the posts and you should see the comments have been closed.

MySQL: Count number of tables in database

If you need to count the number of tables that exist in a MySQL database you can do so with the following query. Just remember to swap out the database name!

SELECT count(*) 
FROM information_schema.tables 
WHERE table_schema = 'YOUR_DB_NAME'

MySQL: Find the size of a database

MySQL has the ability to show you size of the information contained in your database. You can find this information per database or per table in a database pretty easily.

Size of databases

To list the size of all the databases on the server run the following:

SELECT table_schema "Database", sum( data_length + index_length) / 1024 / 1024 
"Size in MB" FROM information_schema.TABLES GROUP BY table_schema;

Size of tables in database

To list the size of all the tables in an individual database run:

SELECT table_name, table_rows, data_length, index_length, 
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES where table_schema = "{{NAME OF YOUR DATABASE}}";

MySQL who: See who is connected to your MySQL server with this script

Need to determine who is connected to your MySQL server and how many connections they have open? I tossed together this little script to display the information.

#!/usr/bin/perl

use DBI;
use 5.010;
use Getopt::Long;

# Set defaults
my $host = 'localhost';
my $db = 'information_schema';
my $user = ''; 
my $pass = ''; 

# Get commandline options
GetOptions (
    "host=s"    => \$host,
    "db=s"      => \$db,
    "user=s"    => \$user,
    "pass=s"    => \$pass,
) or die ( "Valid options\n--host\n--user\n--password\n--db database" );

# Create database connection
my $dbh = DBI->connect( "DBI:mysql:$db:$host", $user, $pass, {'PrintError'=>0} )
        or die "** Connection error!\nTry different options:\n--host\n--user\n--password\n--db database\n\n $DBI::         +++errstr\n";

# Get the current processes to check user
my $sql = "show processlist";
my $sth = $dbh->prepare($sql);
$sth->execute() or die "SQL error: $DBI::errstr\n";

# Create an array of all the users
my %user;
while (my @row = $sth->fetchrow_array()) {
    $user{$row[1]}++;
}

# Display User details
print "MySQL - Logged in users\n";
print "=" x 15, "\n";

for my $x (keys %user) {
    print "$x - $user{$x} connections\n";
}


print "\n";

Page 1 of 4

Powered by WordPress & Drip City Coffee