How to add an index in the WordPress options table and why you should do it • WordPress Help

Table options ( wp_options by default, or ideally with your custom prefix) of WordPress is the one that goes accumulating information about each of the plugins, settings and configurations and more, that you are installing throughout the life of your website.

If we add that the Most plugins do not delete lines from this table, you'll see that is gradually becoming a monster with thousands of lines, which WordPress checks and launches on each page load .

As you can imagine, a table options large can greatly slow down your website since your WordPress must make many requests and launch requests before loading each page for each visitor.

Ergo, if we could limit this amount ad requests your web would load faster, having to make fewer queries, right?

The culprit: Autoload

The culprit of all this is the field Autoload of each line of the table options, usually with the value in Yes that is, yes, it is automatically loaded by the function wp_load_alloptions () .

What's the problem with Autoload?

Well, very simple, it automatically loads all options from table options though:

  • The plugin option does not need to load on each page (Example: contact form that loads its settings across the web when it is only on one page.)
  • The plugin is no longer installed and active.
  • The theme is no longer active.
  • The plugin should have created their own tables and do not put their options in the table options

And, the green The problem is when it self-loads too much but what is too much?

Important note : In this article, we will work directly with the database. Before executing any query or touching something in it, make a backup copy.

Execute this SQL command, you can do it in PHPMyAdmin of your hosting panel:

Note: Change the example prefix to yours, do not forget

Click on the button Continue or execute the SQL query and you will get the size of the query of all the values ​​with autoload e bytes. And the rule should be:

  • If the result of autoload_size is greater than 1 Mb (approximately 1,000,000 bytes) you should do something to optimize this.

the example of the capture size is ridiculous, so if your results are like that you have just read the article, dedicate your time to blog something.

If you obtained a result above 1Mb, or just want to dedicate a Having said that,

Having said that, would it not be better to define what is loaded and when in our WordPress installation?

Create an index in the options table

There are several ways to thin the table options but we will see mainly one that has proven to be tremendously effective and easy to apply.

Why is the best solution?

The best solution to tables options huge that slow down your w eb is to create an index of the table options . In tests on sites with many lines with Autoload the difference between loading the table with and without index return some results beyond doubt.

In case you do not know English: in red loading time Autoload without index, in blue with index.

You can also see how the loading time in a site was reduced after adding an index:

The improvements performance and speed are brutal, especially with large databases, and the best thing is that creating an index is very simple.

Check the index

A good test to do before creating the index is to see how many columns (options) have Autoload in Yes that is, they are loaded. This would be done as follows:

For those that do not load, those that have Autoload in No .

Results will look like this:

The rule to apply would be the following:

  • If the values ​​in No exceed 60% of the total then you do not need an index.

Create an index with SQL commands

If you feel comfortable, you can create the index by executing a command like this:

Again, you can change the name of the index, autoloadindex in the example, and you must change the prefix of the table options by which you use.

Create an index with WP-CLI

If you prefer to use the WordPress command interface WP- CLI you would do it this way:

Create an index with plugins

In case you do not you want or do not you dare with SQL, there is also a plugin that will help you in this task of creating the index.

It is called Index Autoload and it does not even have adjustments, you activate it and it's done. [19659003] Of course, even if it is a plugin, the rule of backing up the database is still valid, do not forget it.

This plugin creates the Autoload index equally.

I do not want the index anymore

As there is nothing like freedom of choice, if at any time you no longer want the index you can execute this command to erase it:

Again, remember to change the index name and prefix of the table to yours.

If you used the option to do so with the plugin to deactivate it and delete it is also deleted the index.

Is it really worth it?

Although there is nothing better than doing a test, I will give you some advice:

  • If the options in Autoload of your table options weigh more than 1Mb worth it
  • If the Autoload Yes exceeds 60% it is worth it
  • If you have a table options great is worth it

The result is to reduce the WordPress queries to the table options in more than 90% in many cases, which results in better load times on your website. [19659138] To learn more

Here you have some resources to continue learning about this subject:

Car gando …

That may also help you:

How to export emails from WordPress comments

Sometimes you have had to clean your database or your users, but the problem is that you lose some contacts from great interest .

Likewise, if you want to send emails to your visitors who have commented the internal process can be tedious, and it would be better to send them an email through a newsletter system right? [19659003] Well, let's see a simple way to export the emails of your commentators so you can have them in a more appropriate and clean format to send them an email or a newsletter, of course with prior consent. [19659003] For this we are going to use an SQL command so we will go to the hosting panel and open the application PHPMyAdmin which is the database manager.

[19659003] Once inside select your base of data and go to the SQL tab

That's where will execute the following SQL command :

Where you will have to substitute the default code prefix (wp_) for the one you use .

In any way it's easy because PHPMyAdmin has a predictive search engine as you type the name of a table

Then press the continue button and it will show you the results:

[19659003] You only have to click on the link of Export from the bottom of the list and on the next screen choose the format, for example CSV .

Y exports a super cute list to be used at your will.

Oh, what do you prefer a plugin?

Well, nothing, you learn less but existing exists, it's called Comments Emails and it's very easy to use … and pull, when you have finished using it.

Loading …

may also help you:

Important security update: WordPress 4.1.2

If your site has not yet been updated automatically, you are already slow to update to WordPress 4.1.2 since corrects a good number of important security flaws .

that is not just a major minor update to the WordPress 4.1 branch but the push you needed to update if you're still using WordPress 3.9 .

Is WordPress really safer by changing the prefix of the database? • WordPress Help

One of the most common advice given (me too) about WordPress security is do not use the default WordPress prefix for database tables but does this change really improve WordPress security?

 protect wordpress

Either from installation or later (see link in previous paragraph) ), using a different prefix for the database tables is a basic WordPress security tip to avoid SQL injections .

As you already know, WordPress by default uses the prefix wp_tablename but is it really a security improvement to use another one like mistablas_nombretabla ? Let's see arguments

What is an SQL injection?

 sql injection

To begin with it's good to know what exactly is an SQL injection . To summarize, a SQL injection offers the attacker the possibility of injecting SQL code through some input path that is available to visitors (visible or not) and that can be executed from the database server, which in the case of WordPress would be the MySQL server where it is hosted.

For example, imagine that instead of entering an email address in a registration form the attacker enters SQL code that makes a list of all the records in the table wp_users which is where all the data of registered users of a WordPress is saved. It gives miedito no?

If so, once sent the form, instead of rejecting the SQL code, the web runs it and the database server would deliver the contents of the table wp_users to the attacker.

An SQL injection, that is, the execution of code through an entry path to a web is the typical result of a problem with the code of a form, a plugin, the theme or any other component of the WordPress installation. And it is possible almost always because the gateway for visitors has not been sanitized so it allows the introduction of SQL code.

It's basically that. In a typical installation of WordPress the attacker will also be able to write to the database, which is even more dangerous as we will see later.

As in everything, there are many variants of possible SQL injections some really gimmicky, but it's good that you have an overview of how an SQL injection works, the impact it can have if it is carried out (read or write in the database) and, above all, how it can be avoided. [19659005] Now let's see how this affects a typical installation of WordPress and if a change in the prefix of the database influences the time to avoid SQL injections, do you think?

Names and tables in the database of WordPress

We have already seen on several occasions which are the tables of the WordPress database and what each table is for, but there is never a new review, and what we are talking about today is a reminder comes from pearl. [19659005] Basically, WordPress installs by default 11 tables that, if you do not modify it, will have the prefix wp_ so if you have not made any changes they will be:

  • wp_commentmeta
  • wp_comments
  • wp_links
  • wp_options
  • wp_postmeta
  • wp_posts
  • wp_terms
  • wp_term_relationships
  • wp_term_taxonomy
  • wp_usermeta
  • wp_users

If you understand some English, just by looking at the names of the tables you can guess easily what is stored in each table. For example, it is easy to imagine that in the table wp_comments comments are stored or that in wp_options is where the settings are right?

Exploiting an SQL injection in WordPress

]  insecurity wordpress

Let's get into the realms of Mordor so choose your best weapon and trust the ring community (or the WordPress community) hehe

Imagine that one of the plugins that you have installed in your WordPress is vulnerable to an SQL injection, something that is not uncommon, it is the most frequent way of vulnerabilities. An attacker who wants you the first thing you would do would be to scan your WordPress installation with tools like WPScan to have the list of the plugins you have installed, even those that are disabled. If when looking at the list it detects that one of them is vulnerable to injections SQl will already have half the work done, if not the most.

The next thing he would do is exploit the SQL injection for what he would execute some codes like Next, the usual ones to manually create an administrator in the WordPress database, there's nothing:

What do those codes do? As nothing more and nothing less than the attacker can create a WordPress user with administrator privileges on your website, which will immediately get access to your WordPress desktop with full access.

On other occasions the attacker not only creates an new admin user but also changes the current password and, by the way, leaves you without access, a symptom that when you see it and is slow to react.

Why the attacker can create an administrator?

Knowing in advance that your website is made with WordPress and that it is vulnerable to SQL injections due to some vulnerable plugin or whatever you may have seen, the attacker only needs basic configuration knowledge of the WordPress database, something fully documented in the same website

Guessing database table names

If the prefix of the WordPress database on the site is the default one, that is wp _ the attacker can easily execute code and read or write information in the tables.

If you change the prefix of the WordPress database, for example to MordorX25_ the attacker can not Read or write in the database so easily since you do not know the names of the tables. This is true even if you have done the SQL injection and the code is exploitable, because they would not have any effect when you did not find an objective to act on.

Yes, changing the prefix of the WordPress database tables improves WordPress security

The – good – idea of ​​changing the prefix of the WordPress database tables is old, in fact from the first versions of WordPress, to avoid SQL injections that could create users and inject spam or malware The only way to quickly stop them was to change the default names of the tables.

Does this mean that I'm safe just by changing the prefix of the WordPress database tables?

Of course not. Changing the prefix of the tables in the WordPress database is a very good security measure, and it stops an infinity of attacks on the database, but it's not the only way they can enter your site.

Most of the time the culprits of a WordPress attack are badly programmed or not updated plugins, the reality is that you can get access to a WordPress installation in other ways, for example through social engineering, stealing passwords and any other method that imagine Everything will depend on the interest that your site provokes in the possible attackers, and with the plague of spammers that invades us, nobody is 100% sure.

So, in addition to changing the prefix of the tables in the database apply these 15 rules to have a bomb-proof WordPress you'll be happier.

NOTICE : this publication is from two years ago or more. If it's a code or a plugin it might not work in the latest versions of WordPress, and if it's a news story it might be obsolete. Then do not say that we have not warned you.

Loading …

That may also help you:

10 basic skills of the WordPress plugin developer

 developer wordpress

The plugins are great part of the greatness and beauty of WordPress and create your own plugin will give you a knowledge and freedom that you will not find if you simply use what others create.

Now, if you throw yourself into the WordPress plugin development it does not hurt to keep this in mind ] 10 basic tips for creating plugins :

:: 1. Creating the plugin ::

The first step is to create the folder of your plugin in the directory / wp-content / plugins . Once you have created the folder you must place the files of your plugin in that folder. There must be a main plugin file, and the file names must have only letters and hyphens (-) if you want to separate words, do not use other punctuation marks or spaces.

A type name would be something like this: plugin-seo-sencillo.php

The main file of the plugin must have the following structure for WordPress to identify it as a plugin:

Once you save the changes , even if you do not have the plugin yet, you will see it on the WordPress plugins page.

:: 2. Activation and deactivation of the plugin ::

Plugins can be activated by clicking on the link " ] Activate "from the list of plugins. In a basic plugin you do not have to do anything when activating it. But an advanced plugin will require tasks such as launch the plugin settings, create tables, etc., so let's see how we can manage the activation and deactivation of a plugin.

Plugin activation hook

WordPress offers a function called register_activation_hook that is launched when a plugin is activated. We can add custom functions that are executed when activating the plugin using this method. An example would be the following:

You have to define the path of the file that contains the activation function as the first parameter and the name of the function as the second parameter.

If the activation function is inside the main plugin file you can use _FILE_ as in the previous example. Within the activation function you can also perform tasks such as validations, initializations and creation of tables

Plugin deactivation hook

We can manage the deactivation of a plugin with register_deactivation_hook using a similar syntax to that of activation. In the deactivation function you can also perform additional actions such as cleaning plugin resources, settings and tables.

:: 3. Creation of custom tables ::

The structure of the WordPress database and it is very flexible, and you can implement most of the custom features using the tables that are available.

However, sometimes you may prefer to include more advanced systems, such as shopping carts, task management systems , subscriptions, etc.

In these cases you should know how and when create custom tables . The first thing is to take into account the requirements of your project and try to use the table and meta tables wp_options to store the data specific to your project. If you consider that these tables do not have the necessary structure to implement the functionality you need then create custom tables using the following method:

First we check if the table exists before creating it. You can decide to create the table at the time of activation, depending on what you need. In the example you will see that {$ wpdb-> prefix} is used before creating the name of the table. This is because, normally, WordPress tables start with the prefix wp_ . This can be changed when you install WordPress, so it's better not to manually add the prefix wp_ since it could be another, and so we make sure it will adapt to what exists.

Using {$ wpdb -> prefix} you will get the prefix defined in the WordPress installation, so always use this syntax to create the tables.

Even if you use the function $ wpdb-> query to create the tables it is advisable to use the function dbDelta since it compares the current table structure. It is not loaded by default so you must include it at the beginning of the file.

:: 4. Including Scripts and styles ::

Even if you only echo scripts and styles it is recommended to add the scripts using the function wp_enqueue_script . This function checks whether files and dependencies are available with other scripts. The following example shows how to use the function wp_enque_script :

First you use wp_register_style to register the style file and wp_enqueue_style to include the file. You must also indicate the path and unique identifier of the style file. Then the scripts are included using the function wp_enqueue_script . If it depends on other scripts you can mention them in the third parameter. In the example, a dependency of jQuery has been added.

Finally, you can add data that will be used within specific scripts using the function wp_localize_script . You can include the scripts where you want but always use the functions wp_enqueue_scripts and wp_enqueue_styles .

Also make sure to use the action admin_enqueue_script instead of wp_enqueue_script for the admin part.

:: 5. Creating shortcodes ::

The shortcodes are predefined code blocks that you can use anywhere. It is essential to learn how to use shortcodes if you want to be a plugin developer since they add dynamic elements to custom pages.

You can create shortcodes using a syntax like the following: