In this lunchtime #WPQuickies, I show you how to implement Two Factor AuthIn this lunchtime #WPQuickies, I talk about different database engines and databases that you can use with WordPress.
Why Is MySQL the Default WordPress Database?
I often get asked why MySQL is the preferred choice of database engine to work with WordPress.
Let’s rewind the clock to before WordPress was created.
The b2/cafelog was one of the first open-source blog systems created and maintained by author Michel Valdrighi.
The b2/cafeglog system ran on PHP and stored most of its data in the open-course MySQL database engine.
Because Michel Valdrighi was engaged in other projects, he wasn’t able to maintain the service, leaving it stale and not moving forward.
There were other blogging systems like Movable Type, Textpattern, and Blogger. Still, there were some minor and major issues with all of these, like licensing, or the programming language used and the lack of functionalities.
So, in 2003 Matt Mullenweg and Mike Little saw an opportunity in the market and forked (made a copy) b2/cafelog into WordPress (April 1, 2003).
The first version of the WordPress v0.7 was released on May 27th, 2003.
So, long story short, WordPress was a direct copy of b2/cafelog it too used MySQL.
What Is MariaDB?
MariaDB is a copy of MySQL and maintains a similar core feature branch.
Developer Michael “Monty” Widenius invented MySQL. It’s named after his eldest daughter, “My”.
Long story short, Sun Microsystems acquired Monty’s business in 2008 for approximately $1b.
Sun was then bought up MySQL a year later.
Oracle decided, for the most part, to shelf MySQL due to its popularity and direct competitiveness with their ,money-making, proprietary and licenced database.
Oracle also planned to license the premium features they developed for MySQL under a proprietary license.
Michael Widenius didn’t like either approach and forked MySQL into MariaDB (naming the database after his youngest daughter “Maria”), vowing to keep MariaDB open-source under the GPL license.
Monty and his team maintain MariaDB as an open-source project – keeping the core database engine in line with the MySQL project, still owned by Oracle.
MariaDB underwent a significant change with version 10, adding new features to compete with MySQL, maintaining the core functionality to remain compatible.
You can read more about MariaDB at https://en.wikipedia.org/wiki/MariaDB.
MySQL vs MariaDB – Which Is Best?
For general performance, MariaDB outperforms MySQL.
Because both products have different non-core feature sets, depending on how you use each database will determine which one out-performs the other.
Also, you can’t directly compare the open-source GPL MariaDB feature set to the premium licensed MySQL Enterprise edition.
You can read the Kinsta blog for the technical differences between the two database systems https://kinsta.com/blog/mariadb-vs-mysql.
The most significant takeaway difference between the two systems is that MySQL is corporate and money-driven, and MariaDB is a community and open-source driven.
For the rest of this talk, we’ll use MySQL and MariaDB interchangeably.
Databases and Storage Engines
A database is comprised of two primary components.
Part one is the Database Management System (DBMS) or Relational Database Management System (RDBMS) and is the relational engine of the database.
In the relational engine, the query is parsed and then processed by the query optimiser, which generates an execution plan. The plan is sent (in a binary format) to the storage engine.
Part two is the storage engine.
This part of the database system manages queries; SELECT, INSERT, UPDATE and DELETE.
It uses that plan sent through from the relational engine as a basis to retrieve or modify the underlying data. The storage engine is where processes such as locking, index maintenance, and transactions occur.
MySQL Database Engine Types
MySQL supports a considerable amount of database engines, MariaDB supports more.
Here are the four significant types of database engines used by MySQL.
ISAM
ISAM has been designed to optimise for reading operations over updates.
Therefore, ISAM performs read operations very quickly and does not take up a lot of memory and storage resources.
The two main shortcomings of ISAM are that it does not support transaction processing and is not fault-tolerant.
If your hard disk crashes, then ISAM cannot recover the data files.
If you are using ISAM in mission-critical applications, you must always back up all your real-time data.
MyISAM
MyISAM is MySQL’s ISAM extended format and was the default database engine for MySQL v5.5 and lower.
In addition to providing a large number of functions for index and field management that are not available in ISAM, MyISAM also uses a table locking mechanism to optimise multiple concurrent read and write operations.
The table locking process results in space being wasted by the system, with database administrators having to run the OPTIMIZE TABLE command frequently to recover the wasted space.
MyISAM emphasises fast read operations, which may be the main reason why MySQL is preferred in web development projects as most database operations are read or SELECT queries.
HEAP
HEAP allows temporary tables that only reside in memory.
As the data is held in RAM, HEAP is faster than ISAM and MyISAM.
However, the data it manages is unstable, and if it is not saved before the shutdown or if the database server crashes, HEAP will lose all data.
InnoDB
InnoDB is the default database of MySQL since version 5.6.
Although much slower than the ISAM and MyISAM engines, InnoDB includes support for transaction processing and foreign keys, allowing more complex queries to be run.
Translation processing allows the DBMS to commit (roll-forward) and roll-back transactions, increasing data integrity.
InnoDB also supports row-locking, allowing multiple transactions to be applied to a single table at once.
How To Convert MyISAM To InnoDB
I often get asked how to convert tables from the old default MyISAM to the newer engine InnoDB.
To check which storage engine your databases tables are using, open up PhpMyAdmin and select the database WordPress uses.
Simply click on the MyISAM table, click into the “Operations” tab, and change the storage engine to InnoDB.
you prefer SQL, you can change the storage engine of any table by running the following SQL statement.
ALTER TABLE wp_comments ENGINE=InnoDB;
Replace wp_comments with whatever table you need to convert.
You can also change the database storage engine for tables using the WP-CLI command-line interface tool.
This article shows you how to find and change the storage engine using the WP-CLI tool https://guides.wp-bullet.com/converting-wordpress-database-tables-from-myisam-to-innodb-with-wp-cli/.
What Other Databases Can I Use With WordPress?
Officially, WordPress can use these other database systems without installing any other supporting software, libraries, or drivers.
- Amazon Aurora
A MySQL and PostgreSQL-compatible relational database built for the Amazon cloud.
https://aws.amazon.com/rds/aurora/ - Amazon RDS for MariaDB
- Amazon RDS for MySQL
- Google Cloud SQL
- Percona MySQL Server 8.0
Enterprise-level replacement for MySQL
WordPress and NoSQL Databases
MongoDB is the most popular document-based database. https://www.mongodb.com/what-is-mongodb
t stores data in JSON format and only required a file-based system for storage, making it extremely easy to use, fast and scalable.
Does WordPress Work With MongoDB?
No.
At least, not natively.
WordPress is designed to work with a SQL-based database.
The queries WordPress makes to read, update and delete data are all formatted as SQL queries.
Theoretically, you could decouple WordPress from the SQL database server and use the WordPress REST API to read and post data to a MongoDB database.
However, even if you wrote your custom database later for the core WordPress app, many plugins that use direct SQL would fail.
At present, you can’t just replace your MySQL database with MongoDB.
Read this article from codeforgeek for more in-depth information https://codeforgeek.com/migrating-wordpress-content-to-mongodb/.
Summary
There are a few different database types that WordPress can use perfectly well.
There are lots of different typed of database engines that tables can use but your DB server will work better if they are all using the same one.
#WPQuickies
Join me every Thursday at 1 pm Sydney time for some more WPQuickies – WordPress tips and tricks in thirty minutes or less.
Broadcasting live on YouTube and Facebook.
Suggest a #WPQuickies Topic
If you have a WordPress topic you’d like to see explained in 30 mins or under, fill out the form below.
https://forms.gle/mMWCNd3L2cyDFBA57
Watch Previous WPQuickies
Using 2FA Logins For WordPress
