Most Useful WordPress SQL Queries

WordPress is one on the most popular CMS around and for good reason. The ease at which it allows anybody to build and maintain websites can’t compare to other systems like Drupal or Joomla. Sometimes though, you’ll need to transfer a WordPress site to another URL or clean up your database to boost server performance. This can sometimes be tricky if you attempt to handle everything through the admin panel. To make life a little bit easier, I’ve put together a list of the most useful WordPress SQL queries.

WordPress is one on the most popular CMS around and for good reason. The ease at which it allows anybody to build and maintain websites can’t compare to other systems like Drupal or Joomla. Sometimes though, you’ll need to transfer a WordPress site to another URL or clean up your database to boost server performance. This can sometimes be tricky if you attempt to handle everything through the admin panel. To make life a little bit easier, I’ve put together a list of the most useful WordPress SQL queries.

Before you proceed…

It’s always good practice to backup your database before making any major changes. Should something go wrong, you’ll always be able to restore the old site. If you need help, try one of these useful WordPress plugins:

  • WP-DB-Backup
    WP-DB-Backup allows you easily to backup your core WordPress database tables. You may also backup other tables in the same database.

  • WP-DBManager
    Allows you to optimize database, repair database, backup database, restore database, delete backup database , drop/empty tables and run selected queries. Supports automatic scheduling of backing up, optimizing and repairing of database.

Alternatively, you can backup your database through phpMyAdmin manually.

The Most Useful WordPress SQL Queries

Running simple SQL queries like the ones listed below is easy. One of the easiest ways to accomplish it is through phpMyAdmin. If you don’t have phpMyAdmin installed or unsure how to access it, like with most things WordPress, there’s a plugin that can help you out. The WordPress SQL Executioner is a plugin that allows you to execute SQL queries through the admin panel.


Update the site URL & home URL

When you install WordPress for the first time, it stores the absolute path of the site URL and home URL in the database. This can make it a little tricky when you transfer your WordPress site from one URL to another. When you try, your site won’t load because the absolute URL is still set to the old URL. Fixing this is easy by updating the site URL and home URL in the database:

Update the GUID

One of the most crucial updates to make after a site transfer is the GUIDs. This is important because the GUID is used to translate post and page slugs to the correct absolute path. Updating this is just as easy as updating the site URL and home URL:

Update the URLs in your content

Like the site URL and home URL, WordPress uses absolute paths when embedding media and links into your posts or page content. When you transfer a WordPress site to a new URL, you’ll need to make sure to update those URLs so they don’t get broke. This can be a tedious and time-consuming process if you have a lot of content to update. Running the SQL query below will make it fast and easy:

Update image paths only

If you use a CDN or cloud service like Amazon CloudFront to serve images, you’ll need to update the image paths in your existing posts and pages so they get loaded by the service instead of your server. Use the query below to only update image paths:

Update your posts meta information

WordPress stores special post information in a separate table. If you’ve transferred your WordPress site, you’ll need to update that information should any of it point to your old URL. Use the query below to easily do this:

Change the default “admin” username

When you first install WordPress, it’s default account username is set to “admin”. Since this is widespread knowledge, it causes security concerns and should be changed. Changing it to a more unique name can boost your site’s security. Use the query below to change the default “admin” username:

Change your WordPress password

Need to change your WordPress password and can’t find where to do it in the admin panel? Use the query below to quickly make the change:

Assign posts from one author to another

Say you want to transfer articles from one author to another. This is another time consuming and tedious process if you’ve got a lot of posts on your site. You can easily and quickly make this change, but first need to obtain the author’s ID by going to your user page in the WordPress admin panel. Click the user’s name to view their profile and then look at the address bar taking note of the “user_id”. Then, use the query below to easily transfer ownership from articles assigned to one author to another:

Delete revisions

When you edit WordPress posts of pages, by default, it saves each revision so you can easily revert should something go wrong. This can cause use up your server resources when you have thousands of entries. Your database will grow significantly and will cause an increase in loop iterations, data retrieval and will affect page load times. To help optimize your WordPress database, delete old revisions that you don’t need anymore by using the query below:

Note: Bear in mind that all revisions from each post will be deleted, including all of its meta data.

Delete post meta

Installing or removing plugins is a very common task for WordPress. Some of the plugins make use of the post meta to store data pertaining to the plugin. After you have removed the plugin, those data are still left inside the post_meta table, which will no longer be needed. Run the following query to clean up the unused post meta value. This will help to speed up and reduce the size of your database.

Export all comment emails with no duplicates

Over a period of time, your blog will have received many comments. These comments will include the email addresses left by the commenter. You can retrieve all these emails for your mailing list without any duplicate.

Delete all pingbacks

Popular articles receive plenty of pingback. When this happens, the size of your database increases. In order to reduce size of the database, you can try removing all the pingbacks.

Delete all spam comments

If you have plenty of spam comments, going through each page to delete spam can be tedious and frustrating. With the following SQL query, even if you have to face deleting 500 over spam comments, it will be a breeze.

  • 0 = Comment Awaiting Moderation
  • 1 = Approved Comment
  • spam = Comment marked as Spam

Delete unused tags

In a WordPress database, if you run a query to delete old posts manually from MySQL, the old tags will remain and appear in your tag cloud/listing. This query allows you to delete the unused tags.

Find how many entries are spam

We look at the column comment_approved. Here WordPress stores the type of the comment. We collect the amount of the different values with COUNT(*) and put it into the variable amount.

comment_approved amount
spam 12118
1 104
post-trashed 1

The 1 represents approved, real comments. We are already seeing: only 0.85% of all comments are spam. Unfortunately, this is a normal value. The mean part is: MySQL must always go through the entire table to find the real comments for the output in the published articles. With an index, an optimized query structure, this goes faster.

Find which posts suffer from the most spam

The more popular a post is, the more spam it attracts. Some posts get almost never spam, others are downright overwhelmed. We get the 20 most spammed posts with this query:

comment_post_ID amount
1776 2299
2184 1840
143 1759
1366 1145
2377 960
2456 512
2536 497
683 486
1373 236
1402 200
1522 174
2458 161
1168 127
10473 121
647 116
2357 114
2831 111
2420 110
741 94

Now we could protect for these articles the comments with a registration or just close the comment form. Both solutions are not ideal.

Find where the most spam comes from

comment_author_IP amount
173.242.120.58 450
198.200.37.51 386
198.200.37.59 287
192.74.240.170 268
109.163.236.87 218
192.74.230.131 181
212.59.28.172 157
198.200.37.83 153
91.236.74.117 148
212.59.28.221 147
142.0.138.77 140
142.0.136.9 117
192.74.228.242 109
142.4.117.43 91
137.175.1.235 88
142.4.97.173 82
142.4.117.162 80
198.200.33.140 80
192.74.228.161 73
193.105.210.217 63
142.4.96.66 63
91.231.40.28 55
193.105.210.216 54
142.4.96.74 54
192.74.228.164 51
192.162.19.193 48
80.93.213.249 48
192.74.231.156 46
142.0.136.12 46
91.231.40.27 46

There is so much spam from some IP addresses that we can lock them out safely. This is works in a .htaccess, for example, like this:

# IP block list
order allow,deny
deny from 173.242.120.58
allow from all


Do you have any useful WordPress SQL queries you use?

I’d love to hear from you! Comment below with some useful WordPress SQL queries you use to update, optimize and manage your site.

WordPress or Drupal: Compare to learn what’s best for your site!

I recently received a message from a local Austin computer technician, Sergio Garcia. His question is a common one among business owners looking for a quick and easy solution to setup and mange their new sites, which is better, WordPress or Drupal.

Hey I just have a quick question, I fix computers in the South Austin area for a living and it has been time for me to have a website. Nothing too complicated but a good-looking simple and effective page where guests can find my business phone number, map where they can find me, maybe with a slider in the first page with pictures of my work and the office, blog and all that.

I don’t know much but I hear Drupal and WordPress are good options but I do not know which one is better.

Thanks,
Sergio

Do a quick Google search for WordPress or Drupal and you’ll find article after article proclaiming one over another. It’s a never-ending debate that’s been taking place for decades. Years ago, there may have been a right and wrong answer to that question, but with time comes technology and technology is what builds advanced CMS. Since the release of WordPress 3.0 on June 17, 2010 and Drupal 7 on January 5, 2011, the line between which is better has become fuzzy. It’s no longer a question which is best, but which is best for each person site’s needs. I might take some flak for this statement, but I challenge my readers to prove me wrong; There’s nothing a WordPress site can do that a Drupal site can’t and nothing a Drupal site can do that a WordPress site can’t.

Continue reading “WordPress or Drupal: Compare to learn what’s best for your site!”

Never fear, WordPress is here! Why to Choose WordPress for Your Site

I do a lot of freelance web development work on the side. From small businesses to large, I’ve done them all. Many of these require the owner the ability to manage their site without the need for a web developer every time they want to make a change. That can get quite expensive for an individual or owner. I find that many times these people believe that getting a customized website built for their company or person can be extremely expensive. My aim is this article is to reach those small business owners and individuals interested in a site and put their mind at ease. The fact is, it can be very inexpensive to get a customized site built. That may be a relative term, but after reading this post, I think you’ll agree with me. I’m going to introduce you to WordPress. A phenomenal content management system (CMS) that will make setting up and managing your own site inexpensive and simple.

I do a lot of freelance web development work on the side. From small businesses to large, I’ve done them all. Many of these require the owner the ability to manage their site without the need for a web developer every time they want to make a change. That can get quite expensive for an individual or owner. I find that many times these people believe that getting a customized website built for their company or person can be extremely expensive. My aim is this article is to reach those small business owners and individuals interested in a site and put their mind at ease. The fact is, it can be very inexpensive to get a customized site built. That may be a relative term, but after reading this post, I think you’ll agree with me. I’m going to introduce you to WordPress. A phenomenal content management system (CMS) that will make setting up and managing your own site inexpensive and simple.

Welcome to the new world of WordPress, no longer just for blogs!

I’m a big believer in what a amazing tool WordPress is to power a website. And before you start thinking it’s only good for blogs, it’s not. WordPress has evolved in the past five years to become a versatile CMS capable of being transformed into a fully featured site. It provides a way to manage the site’s content effortlessly with little to no need of technical knowledge. Here’s just a few popular sites you might be surprised that are running on WordPress:

And of course, another expected benefit of using WordPress is that you’ll have a website and blog all in one working harmoniously together.

Why you should choose WordPress for your site and over other content management systems

Learning Curve for Popular CMS
From CaroLINES blog post, Drupal’s learning curve http://urlnub.com/lCM
When people ask me why they should use WordPress over another content management system like Drupal it’s easy to give them an answer. In addition to the reasons below, I suggest you do your own research and not take my word on it. Google whats better wordpress or drupal and you’ll see thousands of pages and posts praising WordPress over Drupal. Check out CaroLINES blog post, Drupal’s learning curve. Frustrated with Drupal and other content management systems like it, she was inspired to create the graphic on the right.

There’s a lot of reasons why to choose WordPress. With it proven success and easy-of-use it’s makes it an obvious choice. But, just incase you’re still on the fence, here’s just a few of the many reasons why you should be using WordPress to manage your site:

Reason One: WordPress is easy to use and affordable

WordPress is simple to use, affordable, easy to learn and has a great intuitive interface. It makes adding images, videos, new pages, blog posts, and more on a regular basis a breeze. You can even customize it to meet your needs for pages like user profiles, specialized landing pages, calendars and more. The sky is the limit! If you can think it, WordPress can be easily customized to do it, from basic informational sites to full-fledge shopping carts.

Code Is Poetry
WordPress Philosophy, http://codex.wordpress.org/WordPress_Philosophy

Best of all, with a little bit of technical knowledge, you can download and install WordPress for free! You can’t beat that. Most custom systems like WordPress can cost thousands upon thousands of dollars. Worst of all, the money pit keeps getting deeper and deeper for site upgrades, fixes and other changes. With WordPress, you’re using a trusted system, tested and used by thousands of users since 2003. It provides free updates and fixes to security vulnerabilities as technology changes and hackers get more resourceful.

Reason Two: No technical knowledge or special software needed

WordPress lives in the Do-It-Yourself world. You no longer have to wait for a programmer to fix a tiny error for a lot of money. One of the great things about the system is that it’s self-contained and doesn’t require any technical knowledge or special software. You’ll no longer need to spend hundreds, even thousands on special development software like Adobe’s tools. All you need is a device with an internet connection and you’re good to go. Spending days and days trying to figure out how to make your site look the way you want are gone. You can easily create new pages, blog posts, format text, upload media, documents and more all without the need for HTML or FTP software.

Reason Three: WordPress goes where you go, update your site from anywhere

WordPress is internet based, so where ever there’s an internet connection you’ll have access to manage your site. You can even use it on your smartphone! In addition, you can set it up to add new blog posts just by sending it to a email address. It’s key feature is the ease at which is makes it to run your website. While your on vacation, walking the dog, out to dinner or sitting on the pot, if you’ve got an internet-enabled device that has a browser or email client, you’re set!

Reason Four: WordPress offers maximum flexibility to it’s users

WordPress is one of the most efficient and powerful tools for building websites. Built on an open-source platform, it’s simple and easy to make changes from the look of the site to customized functionality like a reservation or booking system. Launching Poptart Cooking was a massive success thanks to the ability to customize the site to fit our needs. With its plug-in feature, you can easily add various applications and tools for improving both the front-end and back-end of your website. There are thousands of great one’s to choose from. A limitless number of site’s like WordPress’s Plugin Directory and CodeCanyon provide an endless amount of WordPress plugins to choose from to meet every need. Most of the plugins only require one click to install and use.

Reason Five: Reach the people you want to reach easily driving traffic

SEO or Search Engine Optimization may sound confusing or even difficult to do. With WordPress, there’s plugins out there built specifically for the purpose of SEO to drive a high volume of traffic to your website. It allows online businesses to get high rankings in the various different search engines like Google and Bing. Higher rankings equals more targeted visitors to your website. It can be one of the best methods when you want to link directly to your customers. Additionally, it saves your valuable time and makes it easy to effortlessly create your company’s website while helping to better manage it in an efficient manner. It’s the perfect content management system for both big and small companies since it’s affordable and brings visibility to business.

A quick look into the features of WordPress

Here’s just a few of the screenshots that showcase the powerful features of WordPress. You can find more on their official site.

WordPress is gaining in popularity by the day and is the platform of choice for many high profile establishments such as the American Government, the CIA, the FBI, the Airforce… and many more.

Is WordPress Scalable? Absolutely!

There’s a big myth out there that WordPress isn’t scalable. That’s absolutely false! Many times the reason the site is having a problem growing are issues with the host, poor server setup and/or poorly coded WordPress plugins. Check out Mark Cahill’s post on WordPress Scalability and what you need to know to make WordPress scale.


In Conclusion

Looking to get a website up fast while being able to efficiently manage it? WordPress is your answer. With a bit of technical knowledge you can install it yourself or pay a small fee to have it professionally installed. I’ve done this personally for many of my clients from the basic install to custom functionality. If WordPress is something that interests you and you’re looking to have a site built, I’d be happy to discuss options with no obligation.

The stumbling block for many people who are desperate to get themselves or their company online is the technical step. With WordPress, it’s never been so easy to quickly set a site up online. With the wealth of free knowledge it makes it easy success launching and managing your site.