Over time, a lot of unnecessary information accumulates in the WordPress database. The volume of which often reaches such a size that the site starts to stumble and may even fall. Today I will show you a few tricks for cleaning and optimizing your WordPress database.
The WordPress database resembles a closet in which all site materials are stored: posts, pages, their revisions, comments, including those marked as spam, as well as all theme and plugin settings. Therefore, if the site is used long timemost likely there is data in its database that can be deleted.
Storing useless data leads to database bloat. For example, why keep the settings for a theme that was deleted many years ago? Clearing the database not only frees up space, but also helps to increase the speed of the site.
For WordPress, there are several different ways optimizing the database, I'll show you some useful MySQL queries that you can run in, for example. I will also tell you about a couple of useful plugins that will help simplify the task.
Attention: Before any action with the database, I strongly recommend that you create a full backup of the site.
Optimizing your WordPress database with phpMyAdmin
There are several ways to execute SQL queries in a database. The most simple option is phpMyAdmin. You can usually get access to it in the hosting control panel in the "Databases" section.
Inside phphMyAdmin, go directly to the SQL section.
This is where we will execute all the SQL queries.
I draw your attention right away, in the examples below, the default prefix of the WordPress tables is used - " wp_»Therefore, first make sure your database table prefixes are the same. If not, just change them in requests for your own.
Remove old plugins and data
Let's start by removing the remaining data from the removed plugins. In the table wp_postmeta you can also find a lot of other unnecessary data that can be cleaned up with the same query.
DELETE FROM wp_postmeta WHERE meta_key \u003d "META-KEY-NAME";
Instead of META-KEY-NAME, you must specify the keys of the plugins to be removed. They can be found in the database tables.
Delete all revisions
Revisions in WordPress are very useful function... But if the authors actively use it, a lot of copies of posts are stored in the database, which are stored even after its publication.
You can delete all revisions at once with the following query:
DELETE a, b, c FROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID \u003d b.object_id) LEFT JOIN wp_postmeta c ON (a.ID \u003d c.post_id) LEFT JOIN wp_term_taxonomy d ON (b.term_taxonomyid ) WHERE a.post_type \u003d "revision" AND d.taxonomy! \u003d "Link_category";
Remove all spam comments
Sometimes there are so many spam comments that it is no longer possible to manually delete them. With a single SQL query, you can delete all comments marked as "Spam" at once.
DELETE FROM wp_comments WHERE comment_approved \u003d "spam";
Remove all unconfirmed comments
If you don't want to manually delete all unconfirmed comments, you can delete them like spam with one request.
DELETE from wp_comments WHERE comment_approved \u003d "0";
Remove all unused tags
You can remove all tags that are not associated with any post with the following query:
DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count \u003d 0); DELETE FROM wp_term_taxonomy WHERE term_id not IN (SELECT term_id FROM wp_terms); DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);
Remove old shortcodes
Often, after removing plugins, non-working shortcodes remain in the database, which have to be removed manually. This can also be done with one SQL query.
UPDATE wp_post SET post_content \u003d replace (post_content, "", "");
Where YOUR-SHORTCODE is the shortcode to remove.
Remove pingbacks and trackbacks
I wonder if anyone uses them at all?
Before starting, make sure you disable them in the admin area.
DELETE FROM wp_comments WHERE comment_type \u003d "pingback"; DELETE FROM wp_comments WHERE comment_type \u003d "trackback";
Remove temporary options
Temporary options in WordPress allow you to cache part of the data in the database. But sometimes this cache can get bloated too. You can clear it with one request.
DELETE FROM wp_options WHERE option_name LIKE ("% \\ _ transient \\ _%")
Optimize tables
Now that we are in phpMyAdmin, we can check and optimize the tables at the same time. This is done very simply.
Select all tables and click " Optimize table»
Optimizing your WordPress database with plugins
There are a number of plugins for WordPress that you can use to clean and optimize your database. The most effective of them are: and.
The most popular WordPress database optimization plugin with over 600K active installations. Very easy to use, operated by one button.
The "Table Information" section displays information on the current sizes of the database tables and the amount that the plugin can free up. In the "Settings" you can schedule automatic optimization of the database. For example, every week, fortnight, or month.
WP-Optimize plugin is very easy to use. The main thing, do not forget to create a backup copy of the site before using it or at least a database.
A growing plugin from Lester Chen, a well-known WordPress developer.
The plugin has an intuitive interface, a detailed report is immediately displayed on how much unnecessary data is contained in the database. You can run a full optimization right away, or step-by-step.
Unlike WP-Optimize, WP-Sweet uses WordPress functions for deletion rather than direct database queries. This reduces the likelihood of missing unnecessary data. However, WP-Sweep does not yet have any process automation.
Finally
I hope this article helps you optimize and speed up your site's database. Remember to always make a backup copy of the site before making changes to the database.
Based on materials from wp-rocket.me
All the newest and most interesting from the world of Wordpress in my Telegram channel. Subscribe!
Ilya Zhuravlev is in touch,by reading this article, you will learn how to clean up and optimize your wordpress database. Over time, a lot of unnecessary garbage accumulates in the database, which can affect, for example, the speed of opening your site. The database is loaded not only by the installed plugins, but also when you delete the plugin, it may leave unnecessary code, tables, rows after it. Not many people know about post revision, when you write an article, wordpress automatically saves a draft of the entry in the database every time a post is changed. Imagine how many of these drafts are saved when writing one article.
Those who are not engaged in optimization, then their garbage in the database is 2, or even 3, 4, 5 times the size of the main content of the database. Imagine that your database weighs 90 megabytes, but the necessary and basic content of the database weighs only 30 megabytes, 60 megabytes is a load of 200, that is, dead weight, unnecessary garbage. Remove this load and your site will fly like a falcon!
Let's start cleaning up and optimizing the database.
First, let's clean up the database using a plugin - WP Clean Up, a very simple and straightforward plugin, does not need any settings, click on the button and that's it, the best of its kind. You can install this plugin directly from the WordPress admin panel. Go to the tab: Plugins - Add New, enter the name of the plugin in the search form, press Enter, install and activate the plugin that opens.
To configure the plugin, go to the tab: Settings - WP Clean Up.
On the plugin page, the first field will display the types of database tables that can be cleared. At the bottom, click on the - Delete All button to clear everything at once. Attention! If your site has the drafts you need, then when you clear all the elements at the same time, they will also be deleted. To save drafts, delete all elements individually, except for the Draft element, by clicking on the - Delete button opposite the value.
In the second field, you will have all the available tables in your database and their size displayed. After cleaning, you will need to optimize the databases, that is, update. Click on the button - Optimize. In the Total field, you can see how much your database has changed in size.
As you can see from the screenshot, in the second table, only 11 main database tables are displayed. You can have much more tables, from 50 to 100. In addition to 11 main tables, there are tables related to installed plugins and remote ones. Next, I will show you how to clear the database of unnecessary tables of remote plugins. After cleaning the database, you can deactivate the plugin - WP Clean Up. Periodically, once every 3-6 months, activate the plug-in and clean again.
Hello! In the course of my work, it became necessary for MySQL to clear the table the most fast way... Directly with a database and programming languages \u200b\u200bsuch as PHP, C, recently it is not often necessary to deal with. I have already forgotten something and climbed into my notes to refresh my memory.
Surprisingly quickly I found and remembered everything. In the current publication I will share with you.
Full and selective table clearing
In general, there are two suitable queries for this kind of work with MySQL:
- TRUNCATE TABLE.
- DELETE FROM.
They have a significant difference in performance - the speed of execution of the operation.
TRUNCATE is usually faster, consuming less system resources.
As an example, I'll show you how it works. Let's say there is a table called "newspk" that needs to be cleared. For this purpose, the following query applies.
TRUNCATE TABLE newspk;
So with complete cleaning we figured out the table and its advantages, let's look at the selective one.
DELETE FROM newspk WHERE id \u003d '34 ';
The condition is already used here, so only the record with ID 34 is deleted. If the WHERE condition is not present, then a complete clearing will occur.
I will note that programming belongs to my field of activity, however, it is a secondary topic on this blog. The main topic is working on the Internet. At the same time, I try to consider not everything in a row, but only normal methods with good financial prospects. If you wish, you can see the publications.
Blog updates appear regularly. I add announcements of new materials to social networks, there are links to jump in the sidebar. Also, through a special form, you can subscribe to a free newsletter by e-mail. Until next time.
Hello everyone. There are times when the need arises delete MySql database... Why delete and not edit? They just unknowingly piled up a bunch of tables, or don't know how to identify an error. Easier to demolish and create a new database. The best optionwill of course produce. Basically, every self-respecting administrator should know how to delete a MySql database.
PhpMyAdmin login
Let's consider working with MySql databases in phpMyAdmin, which is built into (this is not necessary at all, it is better to use it for training and training). After installing the local server on your computer, you will have access to phpMyAdmin and the ability to create databases.
Login to phpMyAdmin at local server you can follow the link: http://localhost./Tools/phpmyadmin/index.php.
Deleting MySql Database
In the admin panel on the left, select the database you are going to remove.
In the next window, at the top, the button Operations... You click.
And the last step ⇒ press Drop database (DROP).
How to delete MySql database from command line
The database can be erased using the command in command line... Sign in account to the database server: Z: \\ usr \\ local \\ mysql-5.5 \\ bin \\ mysql.exe -u root.
The command to delete a database looks like this: DROP DATABASE database_name,
-where database_name denotes the name of the database.
For example, DROP DATABASE moyblog - denotes to drop the database named moyblog. 
Press Enter to run the command. The system will display a message about the completion of the command and the elapsed time.
Cleaning and Editing MySql Database
Database deletion is a radical method. In many cases, it will be enough to remove a specific table, clear the table rows, some records - that is, edit the MySql database.
Dropping tables in a database
Enter the required database. Check the boxes you want and click Delete... If you need to erase only rows without deleting the table - press Clear.
Good day, dear friends. While my office door is driving from St. Petersburg, I decided to write a very useful post for you and tell you how you can completely clear the database in a few minutes mySQL data your blog from garbage, significantly reducing its size. So! In this article, you will learn:
Let's start.
BEFORE ALL MANIPULATIONS WITH YOUR BLOG DATABASE MAKE A BACKUP! DO NOT KNOW HOW, WRITE TO THE HOSTER!
Immediately advice: Many people will think after reading the article, “Oh, how complicated it is, I’d better not clean anything, otherwise I’ll clean it up so that everything will fly, let everything remain as it is”. In principle, not a bad option, but the base keeps growing and growing. \u003d) In general, it is necessary to do, you do not need to be afraid, but if you are afraid, then turn to freelance to a specialist who will do everything for you.
Why do you need to clear the base of debris and how is it clogged with it?
The answer to this question is simple. If you do not clean up your database, which is getting bigger and bigger from month to month of your blogging, then one day it may grow to such a size that your hosting (server) will not stand it. \u003d)
Naturally, if the blog is well-tuned from the inside and out, no plugins are buggy, everything is well opened, post revisions are disabled, spam does not attack it, etc., then the database will not grow by leaps and bounds. But if it grows hard, then this is a signal that something is wrong with you. Well, of course, the base should grow, but not much!
My database for 3 years of blogging weighs 58 MB. This is normal! But you can reduce, which I will show you in this article!
A woman wrote to me here the day before yesterday, - "Alexander, I looked at the hosting, my database weighs 450 mb." I asked her, - "You probably have been blogging for 20 years?" She said, "No, only half a year." I almost fell off my chair. Here the woman definitely has problems. Let's see what causes databases to grow:
1. Due to the growth in the number of articles on the blog.
2. Due to revisions of articles (backups).
3. Due to the increase in the number of comments on the blog (spam comments).
4. Because of the plugins installed on the blog.
5. Because of the extra, not used MySQL tables.
6. Due to the heap of unnecessary Pingbacks.
The more content - the larger the base - this is normal! But when revisions ( backups articles) are not disabled or manually cleaned all the time, this is bad! The database will weigh tons. And it's not a joke. Well, imagine, you are writing a post, a draft is automatically saved:
And how many of them can be saved during the writing of the post? \u003d) A lot! Very often, it happens for novice bloggers that when they find out information on cleaning the database, they find that the revisions of their posts weigh 2-3 times more than the posts themselves. \u003d)
Plugins. They do not occupy the base, but take the place of the tables created by these plugins. Often the growth of the Mysql database is due to the fact that some plugins conflict on the blog or do not work correctly, during this, horror occurs in the database tables and it grows.
It also grows, more precisely, tables from plugins that were once used, but now no longer can take place in it. Such tables can be deleted, and then I will tell you how this is done.
And the last thing is pingbacks. Pinkbacks. I will explain briefly and clearly. Some blogger is blogging, writing an article and linking to your blog in it. If the setting is enabled in his admin panel - "Try to notify the blogs mentioned in the article" (Admin - Options - Discussion):
then a record will be entered into your database that such and such a blog has put a link to you. Additional record - additional kilobytes of weight in the database. It's that simple! Sometimes it is necessary to clean the database of pinkbacks.
Gut! We figured out and understood how our base gets clogged and that it needs to be MUST cleaned. Now consider what to clean?
What do we need to clean up in the database (what are we going to clean up)?
Spam comments
Not needed tables
How to clean up MySQL database directly (by which plugin):
In order not to mess things up for a long time here, take a look at my database:
You can view your database by going to your hosting (server) in phpMyAdmin and select the required database on the left. It makes no sense to show how this is done, since all hosting services are different. But phpMyAdmin is the same. I think most of you know where to find it, if not, write to the support of your hosting and ask for help.
So! What do we see? These are all the tables in my database. What takes up the most space? Do not pay attention to KB. We are interested in Mb. Tables:
wp-comments
wp-posts + wp_postmeta
wp_options
wp_blc_instances
wp_blc_links
wp_blc_synch
wp_wppage_conversion
wp_wppage_conversion_log
wp_wppage_stats
Well, as if they are of interest to me, since there are MB, you may additionally have other tables. I don’t know what plugins you are using.
Let's go in order. wp_comments - all blog comments are entered into this table. 23.5 MB is normal! I have almost 40,000 comments. \u003d)
If you have a hefty table, then you either have a lot of blog comments or a lot of spam comments that need to be removed! What we will do with you. By the way, immediately go to the blog admin panel in comments — spam and basket and look, maybe there are several thousand comments you need to delete. As I once ... I deleted more than 5000. \u003d)
wp_posts + wp_ppostmeta - all blog posts + their metadata are entered here. 15 MB is a great size for 400 long blog posts, some of which are as much as 30,000 characters of text, even more.
wp_options - This is where our blog settings are stored. Usually the size is not big. I have 1.5 mb and that's great! If someone big size (but this is rare), then clean with the Optimize DB or Clean Options plugin. There are a lot of articles on this topic on the Internet, read it.
A few words about the Clean Options plugin. A good plugin, it finds a bunch of unnecessary data in the wp_options table. For example, in this table I have settings for the Math Comment Spam Protection plugin, which was used a hundred years ago on a blog and is no longer used. But its settings remained in the database. It would be great to remove them.
BUT! I advise you to use this plugin very carefully! Delete only what you really don't need!
Tip: If your wp_options table is really small. Well, there is 1-2 mb, then in principle it is not worth picking anything, otherwise you can pick it up so that it will be bad! I have 1.5 MB and I won't even bother. I think you have no more.
Then we go. wp_blc_instances, wp_blc_links, wp_blc_synch + wp_wppage_conversion, wp_wppage_conversion_log, wp_wppage_stats. Take a look at these tables. What is it? And these are tables from the Broken Link Checker and Wppage plugins, which are on my blog, but are not used. More precisely, Broken Link Checker is used to check for broken links, but the Wppage plugin is never used at all!
It used to be in use but has now been removed. I placed it on another domain so that it would not create a load on this blog. In general, what do we see? Plugins are not used and tables are crammed with space. For example, tables from the Broken Link Checker plugin can be cleaned, and tables from the Wppage plugin can be deleted altogether. What we will do now.
First, a little explanation. To understand why you can clean the tables of the Broken Link Checker plugin, you need. From it you will learn how the plugin works. That is, in principle, I can safely clear the tables, the data of this plugin is not important to me. There are plugins whose tables do not need to be cleaned! I clean:
We select the tables we need (check the boxes) and at the bottom we select - clear. Done! Now see how much my base weighs:
Gut! Now we delete unnecessary tables from the old Wppage plugin that is no longer used:
We select the tables we need (check the boxes) and at the bottom we select - delete. Done! And now my base weighs:
Attention! Carefully mark the tables with checkboxes, otherwise you may accidentally grab something else. \u003d) In total, I have reduced the size of the database by as much as 16 MB. It's not that good! It's even super, I will say so!
Further. You need to disable post revisions and delete all existing ones. How to turn them off altogether. By the way, in the same place I wrote about deleting mysql revisions by queries sent to phpmyadmin. For example, everything was gone without problems.
But someone's database was covered! That is, something went wrong. There is such a thing, so I do not advise you to delete revisions that way. Let's take a closer look at another way.
We put ticks everywhere and press the blue button. Done! Your database has been cleared. Well, as you can see, I already had it clean.
I advise you to disable the plugin after cleaning, as well as the WordPress Database Backup and Clean Options plugins. Why do they need active ones when they are not used? Then turn it on after 2-3 months.
How often should the database be cleaned?
I recommend doing this procedure as you clog the base. You need to make backups every single day. wordPress plugin Database Backup, but the cleaning is at your discretion. Well, once every 2-3 months is desirable! In addition to cleaning, sometimes just keep an eye on the database, its size, so that it does not happen that you decide to look at it after 2 years of blogging, and there is a size of 3 GB. \u003d)
Well, in general, I will finish the post on this, I hope it was useful to you! If you follow all the procedures that I described here, then your database will be small, clean and perfect, thanks to this, your blog will work faster and users will enjoy how quickly the pages load.
P.S. By the way, who will apply the above procedures, please describe how your database has shrunk. Thank! Well that's it! Until!
P.P.S. By the way, in the coming months, it is planned to begin recording a new training video course on protecting and speeding up a blog for beginners and not only. The course will be written by me and another person. System Administrator, a true professional in the field of protection of sites and blogs. Do not miss...
Best regards, Alexander Borisov
