Magento uses the MySQL database management system with an Entity-Attribute-Value (EAV) model that allows space-efficient data encoding. As of version 2.4, Magento supports MySQL 8, which is up to twice as fast as the previously supported version 5.7.
What is MySQL?
MySQL is an open-source relational database management system developed, distributed, and supported by Oracle Corporation. It uses structured query language for accessing, adding, and managing content in a database.
MySQL is an open-source software licensed under the GNU General Public License v2, allowing users to use, scrutinize, share and modify the software. The Oracle Corporation also offers commercially licensed MySQL servers that take-away the pains of configuring, securing, and managing a database.
MySQL provides high-performance databases that are ideal for heavy-load production systems. Industry leaders like Amazon, Netflix, Uber, and Airbnb are companies using MySQL in their tech stacks.
Like most database management systems, MySQL uses a client-server type architecture ideal for networked environments where the software resides on the same system as the database files. It also supports arrangements where the client system resides on a remote host and uses database requests over a secure network connection to communicate with the server.
Benefits of MySQL
Flexibility and ease-of-use
Magento uses MySQL, as it provides the flexibility of a fully customizable database system without a hefty price tag. Installing and configuring MySQL with Magento takes less than 20 minutes on average and is an extremely straightforward process.
Superior performance
MySQL is designed to meet the demands of heavy-load applications without compromising on performance. This provides Magento users with the ability to host large-scale inventories and support large order volumes on their online stores without being bottlenecked by the database.
Data security
MySQL implements user account management and an access control system to keep its databases secure. This allows Magento users to keep sensitive customer data secure by limiting access to authorized users. Using the mysql_secure_installation command, admins can quickly secure their MySQL servers through a user-friendly guided process.
Reduced costs of ownership
MySQL allows Magento store owners to reduce the total costs of ownership of their ecommerce stores by providing them with a dependable and easy-to-use database system that ensures consistent performance without unnecessary downtimes.
What is the Entity-Attribute-Value (EAV) model?
The Entity-Attribute-Value model is a data model that allows encoding entities with a vast number of associated attributes in a space-efficient manner. It is also known as a vertical database model and an object-attribute-value model.
In an EAV model, each pair of an attribute and value describes an entity and is stored in a single row. It’s worth noting, however, that while the number of attributes used to describe an entity may be vast, the actual number of values that apply to an entity will be modest.
In Magento, an entity is usually a data item such as a product, category, customer, or order, and each entity has its record in the database. The attributes are data items belonging to an entity like the name, price, SKU, or size of a product entity. The value is simply the value assigned to each attribute, i.e., it will be the name of the product, price of the product, or the SKU.
For example, a store sells a t-shirt in six colors across four different sizes for $5.99. The entity, in this case, will be the product itself. The attributes associated with the product will be the product name, color, size, and price. The values for each, i.e., the individual colors, sizes, and prices for each variation, would then be stored in the database as individual entries.
The primary advantage of using an EAV data model is the flexibility and scalability it provides. It enables a database table to grow vertically instead of horizontally, eliminating the need for a schema redesign each time new attributes are introduced. This allows Magento store owners to introduce custom attributes without breaking the structure of their database.
EAV and extension attributes
Magento provides users with two attribute types that allow developers to easily extend the functionality in Magento.
1. Custom and EAV attributes
Custom attributes are added automatically on behalf of the customer by Magento. These include the attributes added by a merchant within the Magento admin panel to describe products like shape, size, or volume. The Catalog, Customer, and Order modules in Magento are the primary models using the EAV attributes.
2. Extension attributes
Introduced in Magento 2, the Extension attributes allow users to extend Magento functionality using data types that are more complex than the Custom attributes, especially ones that do not appear in the admin panel.
MySQL and the EAV data model provide Magento with a database structure that supports its flexibility. This combination holds the key to ensuring Magento remains true to its reputation of being one of the most flexible and scalable platforms in the ecommerce industry.