WordPress database Optimization is practiced to cater to the needs of developers that use the database for extensions and plugins without modifying the database or introducing new columns. To achieve this, WordPress provides a key-value table structure as an extension table to the main table of data.
For example, against the wp_users table, we have a wp_usermeta table as an extension table. This table can be used to add user data without modifying the main table as adding a row with user_id, meta_key and meta_value.
For small or medium projects, WordPress database structure works well. However, as soon as the database grows and the main table records reach in thousands, the number of records in the extension table will reach millions and at some point, database queries start getting slow.
To enhance WordPress performance, WordPress database optimization may be required at each layer, including PHP, server, and code design. To serve the purpose, one approach is horizontal portioning of database tables. To find out if database portioning could help improve performance, we have to enable slow query log, and then analyze the log to find out which table needs partitioning.
Below are the steps to implement database tables partitioning for better WordPress database optimization.
WordPress Database Optimization—Query Logs
· Set slow query log global variables
Login to your MySQL database via SSH with root credentials and run the following command to enable the slow query log.
SET GLOBAL slow_query_log = 'ON';
By default, long_query_time global variable record queries that take longer than 10 seconds to complete, you can override it depending upon your analysis approach for WordPress Database Optimization.
To set the slow query log file path that you will analyze, run the following command.
SET GLOBAL slow_query_log_file = '/path_to_dir/log_filename';
After doing configurations above, restart MySQL so it reloads global variables with updated values.
· Analyze slow query log
Depending on the number of hits per hour, check the query log after a few hours, it will log queries that take more than 10 seconds which is the default value that can be overridden. Looking at details of the slow query log file, you will find out which query is involved for each function, like involving tables, and table columns.
Please note that analyzing the structure of involving tables and their column is very important for WordPress database optimization as you may want to check whether a table has a single index or composite index. If there are no indices applied to the table in question, you may want to try testing slow query options, while applying indices first.
· Decide the range and column considering updates in view
The main thing that you need to consider before applying the range type of horizontal portioning for WordPress database optimization is that your database should not belong to an update-intensive website. WordPress sites are read-intensive—mostly updates are done by the owner or the admin.
After finding the table and column involving queries that are taking more time than the set interval, we can decide on the table to partition. In a slow query log, detail of each slow query examined is explained with different labels. Query_time label tells the number of the second a ‘selected’ query takes, and Item columns tell the columns involving in the query.
· Apply range portioning
Count the number of rows of the table you want to partition, and decide an optimal range. Remember, do not take too small range as it may disrupt your update operations in the backend.
Following is an example MySQL statement to apply partitioning to wp_usermeta table that has the number of records around 550000.
ALTER TABLE wp_usermeta DROP PRIMARY KEY , ADD PRIMARY KEY ( umeta_id , user_id )
ALTER TABLE wp_usermeta PARTITION BY RANGE(user_id)
PARTITION p0 VALUES LESS THAN (100000),
PARTITION p1 VALUES LESS THAN (200000),
PARTITION p2 VALUES LESS THAN (300000),
PARTITION p3 VALUES LESS THAN (400000),
PARTITION p4 VALUES LESS THAN (500000)
Beside integer values, the partitioning range can be applied to date values as well.
Other Considerations For WordPress Database Optimization
If there are deleted records that would make the range uneven it has been applied to, you may want to count the actual existing rows per range number and revise the ranges accordingly. Also, you may need to apply more partition ranges as the database keeps on growing.
Make sure you keep the copy of the table as a backup you are portioning, so it can be reverted in case of partition if the practice does not go well in the worst-case scenario.
By following these steps to implement database tables partitioning, you can implement WordPress Database Optimization in a more effective way. If you are looking for more technical and precise WordPress services, please free to reach us out at vteams and get professional assistance now!