MySQL is one of the most popular Web server database management systems. If you are running a WordPress Web site, it is likely to use MySQL as its database. This makes it necessary for you to understand how to optimize MySQL performance.
optimizes your MySQL database so that the software can respond to queries more quickly. Eventually, this may lead to shorter load times and faster sites as a whole. In addition, if you maintain your database correctly, it should continue to provide high performance, even if it grows in size.
in this article, we will give a brief overview of what MySQL is and how it works. Then we will explore how to use four basic techniques to perform MySQL performance tuning. What
- MySQL is and how it works
- completes MySQL performance tuning 4 tips
MySQL is and how it works by cleaning up revisions, drafts, spam comments and posts.
dynamic Web sites store information in a database, a strange term for complex table structures that contain a lot of information. The database enables you to store variables and “query” them to get the information you need. For example, when you load a WordPress site, the content management system (CMS) connects to its database to get the information you need.
if for some reason WordPress cannot connect to the database, you may encounter errors such as “MySQL Server Has Gone Away” or “MySQL 1064”. MySQL is open source software that allows you to create and manage databases. It is far from the only database management system that can be used online. However, it does dominate in terms of market share, with about 15% of websites using it.
MySQL Market share there are different approaches to
database management, and MySQL uses “relational” and “client-server” models. What does this mean:
- Relational database-this type of database breaks down information into tables. Because of the relational model, you can use keys or unique identifiers to connect data from multiple tables.
- Client-server model-using this model, your database is in the server and your Web site is the client. The browser makes a request to your website, which in turn queries the database.
because MySQL is open source software, there are many branches available. For example, MariaDB, which is a branch of MySQL. MariaDB provides higher performance than its parent software.
learning how to make MySQL run faster on its own is still a valuable skill, but mainly if your Web host is not optimized for you. Let’s talk about what optimization needs.
four tips for MySQL performance tuning
if you use WordPress and want to learn how to make MySQL run faster, the best way is not to focus on a specific configuration. Instead, you need to fine-tune your database to match the way you use WordPress. Let’s talk about what this means.
1. Keep your MySQL version up to date most network hosts will automatically update the MySQL to the latest version for you. However, if you have full control of the server, you need to update the database management system manually. Because you are using WordPress, you may understand the benefits of keeping your software up to date. MySQL performance tuning has many advantages, such as: newer versions of
fix security vulnerabilities in the software. The latest version of
- , MySQL, is better optimized and therefore faster.
- ‘s faster database management software translates directly into shorter load times. However, you may not know which version of MySQL you are using. If you have full access to the server, you can start the command line and run the following command: the
parameter returns information about the version of MySQL used by your server. You can compare this information with the latest version on the official MySQL website to see which newer versions are available. If you are not used to using the command line, you can find out the version of MySQL you are using through the WordPress dashboard.
mysql -v
-vfirst, visit the dashboard and go to the tool & gt; site Health. Next, open the information tab and click the database:
checks the MySQL version in WordPress
, depending on your MySQL version, the software may update automatically. If not, you can update MySQL to the latest version using the command line.
2. Check to see if there is an isolated table
in your database. Usually, plug-ins and site themes that you uninstall from the site will leave data. This may be due to poor coding standards, or because plug-ins / themes decide to store the data in case you want to reinstall them. The idea behind this approach is that storing settings and configurations in your database will save you time and work.
however, all of these unused tables can cause the database to swell, which in turn can slow down queries, depending on the capabilities of the server. Ideally, you will clean up after uninstalling the plug-in to prevent orphaned tables and data from piling up. You can accomplish this task in two ways: manually or by using plug-ins. Before
makes any changes to the database under any circumstances, we recommend that you fully back up your WordPress site. This applies even if your host makes daily backups, so you will have a copy of the site to restore when needed. After
backup, you can access your database through phpMyAdmin. If you are a pagoda panel user, please log in to the pagoda panel backstage and go to the database. Select the site you want to process and click the “manage” link. You can access your database directly through phpMyAdmin:
After accessing phpMyAdmin
through the pagoda panel and entering phpMyAdmin, you can use the software’s search function to find isolated tables related to specific plug-ins. The problem with this approach is that it is difficult to know which queries to use unless the plug-in you are trying to clean up is fully documented.
for example, Yoast SEO recommends that you use
queries to find tables related to the plug-in after uninstalling the plug-in. Running a quick search with phpMyAdmin returns a list of entries containing the term from different tables:wpseolooks for entries related to Yoast SEO in your database
although manual methods work, we encourage you to use the database cleanup plug-in solution. With some of these plug-ins, you can even find orphaned tables from the dashboard and delete them without mining through phpMyAdmin.
3. Determine what data MySQL is automatically loading
each WordPress database contains a large number of tables. One of the tables, called
, contains the following information:wp_optionsplug-ins and theme settings
- your site URL, blog name, description, etc.
- if you use phpMyAdmin to check the
table, you will notice a column namedwp_options. WordPress checks the row with the “autoload” entry under “autoload” and then loads that data every time someone visits your site and visits any of its pages:yeschecks wp_options to automatically load data
out of the box, WordPress should only load critical data automatically. However, some plug-ins and themes add data to
and configure it to load automatically. Over time, automatically loading data slows down response time, as if you have too many programs set to start automatically when you turn on your computer.wp_optionsif your WordPress site feels slow and you can see many plug-in-related entries in
, it is recommended that you take a closer look at the amount of data automatically loaded in WordPress and determine which plug-ins you can disable.wp_options4. Clean up revisions, drafts, spam comments and posts
when you use WordPress, your site will accumulate a lot of unused data. For example, WordPress automatically saves multiple revisions and drafts for your article, depending on the number of changes you make to them. Over time, all of this data will swell your database, so it’s a good idea to clean up these entries regularly. The best way is to use plug-ins, such as the Advanced Database Cleaner:
Advanced Database Cleaner plug-in
you do not need to use the advanced version of the plug-in for this task (unlike when dealing with orphaned tables). After enabling the plug-in, you can go to the WP DB Cleaner tab and view the General cleanup section. There, you will find a complete list of all the transient data that the plug-in can help you remove from the database:
uses Advanced Database Cleaner to clean up your database
you can choose which tables to “clean” or empty, and then run the process manually. Alternatively, the plug-in enables you to schedule cleanup for the tables of your choice and configure them to run periodically. The last option is your best choice– which means you have fewer tasks to worry about. However, we recommend that you do not reconfigure database cleanup to run too frequently. In this way, you can still access previous drafts for a rainy day.
summarizes the benefits of
MySQL performance tuning. However, the main reason you want to optimize the database is to improve its performance so that your site always loads as fast as possible. Because you are using WordPress, there are several ways to perform MySQL performance tuning without changing the software configuration. If you are interested in learning how to make MySQL run faster, here are four tips to get you started:
keeps your version of MySQL up to date.
- checks to see if there are isolated tables in your database.
- determines which data MySQL is loading automatically.
- cleans up revisions, drafts, spam articles and comments.
- 清理修订、草稿、垃圾文章和评论。
