WordPress uses the open source database management system Mysql to store and retrieve all the information on the website, from posts and page content to comments, user names and passwords. If visualization is required, treat the site database as a cabinet and the company that created mysql. MySQL is a popular database choice for web applications -joomla! Drupal also uses it. According to Wikipedia, well-known companies such as Google, Facebook, twitter, Flickr and Youtube also use it. The
So how does MySQL work in WordPress? This article will introduce MySQL and how MySQL interacts with word press, including database architecture, storage engine, optimization technology and optimization, and database management best practices. What is MySQL? MySQL is the central component of the lamp stack of open source web application software used to create websites. Lamp stands for Linux, Apache, mysql, and PHP. MySQL is also used in LEMP stack to replace Apache (pronounced engine-x) of nginx. The
WordPress uses php to store and retrieve data in a MySQL database using SQL queries within PHP annotations. For example, if you are a member of a WordPress based member site, log in using SQL, search for unique membership, check for active members, and ensure that the correct profile data is displayed earlier- End PHP and SQL work with word press, so you can generate dynamic content based on various elements, such as IDs and user roles. This allows you to hide or show content to specific users, such as administrators, editors, and subscribers. Without SQL and MYSQL, neither is possible. The
The plug-ins and topics also use database storage options and other data, and then use SQL to query the database within the PHP annotation and dynamically output the content. It is worth mentioning that if you run a small website (such as cat’s blog), you don’t have to mess up SQL. SQL knowledge is required only if you work on an enterprise web site. WordPress database architecture or: to help you understand how tables, tables, and tables WordPress work correctly with MySQL, let’s take a look at the tables that WordPress stores in a regular database. The
WordPress has a fairly simple and uncomplicated database architecture. It consists of 11 tables and is used in the core component of word press. It cannot be deleted or deleted. Wp_commentmeta – stores the metadata of WordPress posts and all comments on the page, including custom post types. Wp_comments – saves all comments created on the site, including posting, drafting, shelving, and spam comments. Wp_links – retains all information entered in the link manager function of WordPress. This function is rarely used recently. The link function itself is no longer used in WordPress 3.5. It will be hidden by default when newly installed. The
Wp\u options – all word press options (such as reading and discussion settings) are stored in this table, and wp\u options is usually used to save plug-in settings different from custom tables. Wp_postsmeta – as most people expect, this table stores all metadata related to posts and pages. Wp_posts – all
When saving, pages and navigation \/ menu items. Wp_terms – this table stores categories for posts, links, and tags. Wp\u term\u relations – the post is associated with categories and labels in the wp\u terms table, which will remain in the wp\u term\u relations table. The table also retains links to links to the category. The
Wp\u term\u taxonomy- describes the category, link or tag of items in the same wp\u terms\u table. Wp\u usermeta – stores metadata for all users in the wp\u users table. Wp_users – all users are stored in this table. Remember that data such as passwords is serial. The structure of a multisite database is very different. The database structure of a multi site installation is very different from that of an independent site. Therefore, if you manage one or two sites, it is important to understand the differences between them, so you can effectively manage the website. The
Wp_blogs – each site created in a multi site network is stored in this table. Wp_blog_versions – stores the current database version of each site in the network. It is mainly used for the network update process. Each site is updated with each upgrade. Wp\u registration\u log – the log generated by the administrator when registering each new site. Wp_site – this table contains the primary site address. Wp_sitemeta – each site has site data. This table stores site data, including options for site administrators. The
Wp_users – contains all users, but this field is also used for single site installations. This includes spam and two additional fields \/ rows that have been deleted. Wp_usermeta – when using multisite, this table stores user metadata for each site (wp_usermeta will not be regenerated in a single site installation). Tables for each site are also added to the database (for example, wp_2_commentmeta, wp_2_comments, wp_2_links). The primary site data is stored in a table without an existing number, and the subsequent site data is stored in a table numbered according to the naming structure of the primary site table. The
The plug-in also uses a database. After installing the plug-in, the database will be used to store and retrieve data related to the plug-in. For example, the custom field plug-in stores the generated fields in the database and then searches for display in future related posts. Without a database, the plug-in will not be able to save the created fields, nor will it be able to associate the posts or query values to be displayed in the front end with the fields. Plug ins can use default word press database tables, such as wp\u posts or wp\u postsmeta, or create custom tables. A popular example of a plug-in that creates its own tables is woocommerce, which creates eight custom tables for storing and retrieving product IDS, order items, tax rates, and other product information. The
If you are worried about the plug-in creating tables in the database, do not worry. Plug ins typically do this. Although it is best to use existing tables (such as wp\u options) to store plug-in data, this is not always possible, especially on more complex plug-ins such as woocommerce. Note: it is recommended to delete the custom table from the database when deleting the plug-in from the site. Otherwise, unused table sets in the database will pile up during installation. Some plug-ins include all the
You can choose to automatically delete data. You should delete a custom table only if you are absolutely sure that a particular plug-in will not be reused, because it cannot be restored. MySQL storage engine description MySQL uses the storage engine to store, process, and retrieve information from tables. MySQL supports 13 different storage engines, but the two most commonly used options are MyISAM and InnoDB. In most cases, the default storage engine defined in the MySQL configuration file is usually MyISAM, which is also commonly used by people. The default value is because many people are reluctant to take the time to choose a storage engine. If you decide to choose a storage engine, WordPress will make your decision easier. MyISAM reads faster, but InnoDB’s row locking mechanism makes reading and writing faster. WordPress relies heavily on reading and writing, and InnoDB is the best choice. By default, the tables generated by phpMyAdmin use the MyISAM storage engine. In general, this means that if you use a shared hosting or non expert word press host, the tables will use MyISAM instead of InnoDB. To change the storage engine, you can use the following SQL query (available in the preferred database management tool (such as phpMyAdmin): Set default\u storage\u engine=innodb; Note: for very strange reasons, the tables created in phpMyAdmin use MyISAM by default. That is, if shared hosting or non professional hosts are used, the table will become MyISAM. Don’t be afraid! You can change the engine in use in the database. To change a table, you can use: Changing the storage engine table by loading the summary code monkey\/9c310c82ad5357ff825799df 36727414 table may be a time-consuming process. In this case, we recommend that you check Pantheon’s excellent tutorial. Now you say, \
Only install the plug-ins to be used but do nothing. The simple way to optimize the database is to install the plug-ins to be used instead of installing the plug-ins. Activating shiny new plugins is easy to attract! Remember that a new data populating the database will be created for all installed plug-ins. There is a plug-in that stores a large amount of data. It is usually divided into the following four categories: Security plug-ins – most security plug-ins collect and store attack information about the site to protect the site from future attacks, spam, and login attempts. Statistics plug-in – this plug-in does not obtain data from third-party sources such as Google Analytics, but stores measurement items (such as pages, visits, browsers, keywords, etc.) in the database. Anti spam plug-in – according to the characteristics of the anti spam plug-in, it stores a large amount of data like the security plug-in, including IP address, e-mail address, country \/ region and other information. Popular Posts plug-in – tracking items such as hits and likes in hundreds or thousands of posts may expand the database. It is best to keep these plug-ins to a minimum. So, should I stop using the above plug-ins? Yes and No. If you think that the garbage information and security of the site are very important, but the type of site you are running does not need it, please avoid and try to count and popular post type plug-ins. The spam message is one of the main reasons for the expansion of the database if it is not properly maintained. I saw a website with tens of thousands of spam messages. Fortunately, deletion will not be easier. Execute SQL command: Delete from wp_comments where comment_approved= log in to the \
You may want to load a temporary project that does not exist. In addition, site owners who install temporary removal plug-ins are also popular. Deleting temporary items used in plug-ins and themes that should not be deleted may cause multiple problems in the site. Delete temporary items only if you know exactly what you are ultimately doing and for what. Do not delete all temporary items at once, as the site is likely to be damaged. Using Memcache on the Memcache site can speed up the centralized database queries (data and objects) in RAM, thereby reducing database reads. This allows the page to load faster because the data already exists without creating a query. As with all caches, one drawback is that posts \/ pages \/ sites are updated. If they are already cached, the cache must be emptied before the changes are displayed. A common mistake many people make when using memcaching is to install plug-ins such as W3 total cache, check the Memcache settings, and actually enable Memcache instead of setting Memcache. You cannot set options unless you first configure the Memcache database \/ server side! Misconfigured Memcache (or all object caches for this problem) may cause great harm to the site and database. Most importantly, automatic updates and plug-in \/ theme problems that depend on temporary problems may occur. Undoubtedly, my favorite database based caching method in WordPress is redis, which makes a huge difference in page loading time. Unlike Memcache, redis has built-in persistence. Like Memcache, redis is also a data structure storage in memory (data is stored in RAM). You can use the redis object cache plug-in to connect redis to the WordPress site. However, redis must be set and cache must be configured first. One method is to use the predis script or the redis extension of HVM (only when HVM is used instead of PHP). Verify whether redis is configured wisely. Instead of storing large blocks of data in each key, keep the appropriate number of keys. If you want to execute thousands of redis calls to create longer objects, using database caching is meaningless. Cache transactions. There are important differences between Memcache and redis. Memcache is a memory storage cache system, and redis is an appropriate data structure server, so it can be used as actual data storage instead of volatile cache. If there is no large-scale investment setting for Memcache system, stackoverflow will help you understand why you want to use redis instead of Memcache. Maria DB Maria DB is a MySQL fork created by one of the original creators and developers of MySQL after it was acquired by Oracle. MariaDB has faster replication speed, and the thread pool allows tens of thousands of connections without significantly reducing i\/o speed. MariaDB also provides more storage engines, replacing the widely used storage engines such as InnoDB. Memcache cannot be used with MariaDB, but the excellent query cache can be used to set up the database cache with Maria dB. Do you need to switch to MariaDB? It is open source, faster, and provides some excellent functions as a whole. If there are large and medium-sized websites, yes, I do recommend them. However, if you use small sites and cheap shared hosting, your time and effort are of no value.
In the end, MariaDB is my first choice, especially for connection processing. This reduces the dreadful \
