Collations can get quite complicated as the MySQL documentation describes: An example of a Collation rule, is a case insensitive collation where strings are compared even if they are in lower or uppercase characters. To compare strings, the database uses the character encoding numbers to perform the comparison. MySQL supports many character sets that allow you to store almost any character in a string.Ī MySQL collation is a set of rules used to compare characters in a particular character set. The combination of all letters a to z, and their corresponding number encodings is what makes a character set. The letter is the symbol and the associated number is the encoding. Each letter is assigned a number, for example, a = 1, b = 2, c = 3 and so on. In an alphabet there are 26 characters – a to z. So what are character sets and collations?Ī MySQL character set is a set of characters that are allowed in a string. You might have come across character encoding problems when migrating your WordPress database from one server to another, and wondered what are these charsets and collations that are mentioned on support articles. Pro Tip: Convert MyISAM to InnoDB with phpMyAdmin to improve database performance. Some sites might even have a mix of tables using both MyISAM and InnoDB storage engines. MyISAM was the storage engine used in the past by WordPress and you might still have older WordPress sites running on it.
It is the most commonly used storage engine, as it has row-level locking instead of full table-level locking (super important when you’re doing a mysqldump export/backup), has support for transactions (allowing you to commit and rollback SQL statements), and has full support for foreign keys and relationship constraints. Since MySQL 5.5 the default storage engine has been InnoDB. Pro Tip: SpinupWP supports MySQL 5.7 (but being dropped with Ubuntu 20), MySQL 8 as well as the latest version of MariaDB MySQL Storage EnginesĪ storage engine is the part of the database that is responsible for reading and writing data. Kinsta have written a deep dive into the history of MariaDB and some of its differences to MySQL. MariaDB is a fully GPL licensed fork of MySQL, that can be used as a drop-in replacement for MySQL. It also works on MariaDB version 10.1 or greater. WordPress uses the MySQL database management system and requires MySQL version 5.6 and higher. The database is an essential part of a WordPress website. WordPress Database and Query Optimization.So I have put together the ultimate guide to the WordPress database. However, we believe that any developer working with WordPress should have some level of knowledge about the database that sits behind a site. Here at Delicious Brains we eat, drink and sleep databases.