Managing your website is an important task. A lot of webmasters are not aware of good principles regarding website management. WordPress involves usage of a server which has PHP and Database functionality. Page load time is an important parameter which involves a lot of care. An increase page load time means, a higher bounce rate. No visitor waits for more than 1 second for a website page to load. There are many steps involved in reducing the load time of a WordPress blog. However, one such step is ignored, that is WordPress Database Optimization. If your database is mis-configured than chances are that your blog will face increased load times. Let’s learn some basic tips to ensure good WordPress Database Optimization, in order to reduce page load time of an article.
1. Configure your mySQL Cache
Using Cache for MySQL is a good way to go optimize your database. Blogs normally spend a lot of time reading content from the database. On the contrary, social media sites and forums involve a lot of writing into the database. It’s recommended to use cache feature of MySQL for an enhanced speed. The query time is significantly reduced when those same queries are cached.
Query cache will speed up the process of fetching data; as a result it will ensure a reduced page load time. It’s equally good for Core Web Vitals score. In order to check if you are really using query cache, login to your database using the following commands:
sudo mysql -u root -p
show variables like 'have_query_cache';
show variables like 'query_cache_%';The second command; show variables like ‘have_query_cache’; will show the status of cache facility. If it returns Yes, then cache is available. The third command will show cache related tables. The result shown is something like this:
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 15728640 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
5 rows in set (0.00 sec)This is the configuration of the entire query cache scheme related to MySQL. The query_cache_size variable shows the maximum size allowed for the cache storage. Optimal one is 15mb or 20mb, as shown above. 15728640 is shown in bytes and it equals 15mb. However, you need to check your own optimal values. The query_cache_limit variable shows the maximum value of query which can be cached. The query_cache_type selects the type of query to be cached or ignored. Set it to 0 for no cache, 1 for every query cache and 2 for on demand cache using different select statements. We recommend setting it to 1 for a WordPress blog.
Enabling MySQL query cache using configuration and SSH:
You need to edit your MySQL configuration in order to enable query cache features. The mysql configuration file is a good start for WordPress database optimization. Run the following command:
sudo nano /etc/mysql/my.cnfThe location of mysql configuration file can be different in different linux versions. Sometimes it’s inside /etc/mysql/my.cnf or other times it’s at /etc/my.cnf; therefore find it for your own self. Now, add the following lines inside the MySQL configuration, directly below [mysqld]:
[mysqld]
query_cache_size = 10M
query_cache_limit=256K
query_cache_type=1After saving the configuration with ctrl+x; restart your MySQL service. You can use a common command like service mysqld restart.
Check ‘increased speed’ or ‘reduce load time’ due to MySQL:
Since, query cache is enabled, this will help in a reduced read time from the MySQL database. You can use a sample database to write content and later read them from different tables using select statements. A good way to do is to enable profiling on the sample database to keep a note of the read query time. You can use profiling in the following manner:
Use db_talkofweb;
SET profiling = 1;
Select * from customers;
SHOW PROFILES;The third step assumes that you’ve created a database named db_talkofweb, further you’ve also added data inside customers table. The show profiles; query will sow the new read time, which would be 30-50% lesser than the previous configuration of MySQL without cache.
The other way to check MySQL is to use Tools Pingdom, before the configuration and after the configuration, as you’ll witness a reduced page load time and a lesser Time to First Byte.
2. Install a WordPress Database Optimization Plugin:
A lot of clutter inside database can be present, just because of the increased number of revisions. WordPress keeps a revision of the article which you update or write. This revision is kept to revert accidental changes inside articles. Therefore, you don’t need these revisions for old posts, which you haven’t edited for one year or more. We are going to use a plugin to clear clutter inside WordPress database. It’s recommended that you backup your database before cleaning database.
Learn how to Backup WordPress Database and Files
Install a plugin named Sweep, it can help you in cleaning all the unused information stored inside your database. Unused tags, trash posts and stored revisions of your WordPress blog, this plugin can handle everything in one click.
It gives you a lot of options to clean WordPress database. Just press the Sweep button and you are good to go.
3. Go through your WordPress theme for Excessive queries:
The above two solutions will reduce the query time, as a result, your WordPress page load time will stand reduced. However, we assumed that you are using a good template. Sometimes a bad template can cause a lot of troubles. If your template is full of database driven modules, like large post descriptions, excessive calling of theme options and other features which require database. The result is, poor page load time.
Solution to this issue is, that you create a child theme and try to overwrite bad calls to the database. For example, look at this piece of code, something found commonly inside header.php of your theme file.
<meta http-equiv="Content-Type" content="<?php bloginfo('html_type'); ?>
charset=<?php bloginfo('charset'); ?>" />Now, have a look at the following piece of code, found in a good template optimised for better load time.
<html xmlns="http://www.w3.org/1999/xhtml" dir="ltr">
<head profile="http://gmpg.org/xfn/11">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />The goal is to interchange unnecessary calls to database with static content. You don’t need to call your blog name or blog information from the database, every time. A good practice is to use static html.
Conclusion:
The above three practices of caching, optimizing and reducing SQL calls can have a significant effect on your WordPress Page Speed. Google and other search engines focus on the page speed a lot. It’s a part of the user experience that the intended page loads within seconds. WordPress database Optimization can even improve Time to First Byte. It’s important to keep your WordPress installation optimized.
