Chapter 8. Optimizing Magento

As you've no doubt realized by now, Magento 2 is a very powerful e-commerce platform. From its robust product management suite to its virtually unlimited extendability, Magento packs a lot into one open source platform.

From our work within its files, we have learned that Magento combines a great number of separate files and data tables to present any page to online visitors. By its very nature, the MVC architecture of Magento puts a good deal of overhead on any web server. In the beginning, the complexity of Magento discouraged some developers because of the demands the platform placed on hosting servers.

However, over time Magento's developers have worked hard to improve overall performance, and Magento 2 represents a milestone in that regard. Magento 2 is indeed faster than before, even when taking faster servers into account.

That said, for Magento to perform at the highest levels of performance, there are areas with which you should become familiar. Specifically:

The Magento EAV methodology
Indexing and caching
Server tuning configurations

After all, a faster website improves the customer's experience and helps improve your rankings with search engines.

Exploring the EAV

Most databases for open source platforms are quite simple in comparison to Magento's. For instance, in the past, when one designed e-commerce systems from scratch, it was likely that a simple relational model would suffice: one table for products that would contain all the key information relating to that product, such as price, available quantity, description, weight, and so on. The challenge with this traditional approach was that if we needed to add a new product attribute, such as height, that field would have to be added to the product table, thereby making previous versions incompatible and complicating any upgrade path.

In order for Magento to be a truly scalable platform, its developers utilize an Entity, Attribute and Value (EAV), or Sparse Matrix, architecture. This database structure adds a great deal of complexity to the Magento data model to be sure, but its advantage is its ability to allow an unlimited number of attributes to be added to any core item, such as products, categories, customers, addresses, and more. Today's Magento installs an initial 296 data tables, many of which are related to EAV.

EAV allows developers (and you) the ability to extend any entity's attributes without changing any of the data tables. Let's break down EAV to understand how this works.

Note

As you go through this chapter, you may want to take an actual look at the tables of your Magento installation. With most hosting providers, you are provided phpAdmin as a tool for exploring and manipulating your database. If not, you can use any number of available tools, including the free MySQL Workbench (http://www.mysql.com/products/workbench/). See your hosting provider for information on how to directly access your database.

Entity

Products, categories, customers, customer addresses, ratings, and reviews are all entities in the Magento data scheme. Each entity has its own entity record in one of the following tables:

catalog_product_entity
catalog_category_entity
customer_entity
customer_address_entity
rating_entity
review_entity
eav_entity (stores product attribute entities)

Each of these entity tables stores very basic information about the entity. For instance, let's take a look at the columns of the catalog_product_entity table:

entity_id
entity_type_id
attribute_set_id
type_id
sku
has_options
required_options
created_at
updated_at

These are the only columns required to define any product in the database. Notice that the name, description, and price of the product are not included in this table.

Attribute

Attributes are the names of various items that belong to an entity. For instance, a product has attributes of price, description, and quantity. Attribute tables don't store the actual value of the item, only its name and its relationship to the entity. That's where value comes in.

Value

If you're following the bouncing ball so far, you now can surmise that value is the actual data of the attribute. So, if we use a simple graphic such as shown in the following figure, we can visualize the entire EAV relationship.

Putting it all together

Let's look at how this works in practice for a product. The product entity is stored in the catalog_product_entity table, as described before. To bring together all the information related to a product, we have to pull in all the various attributes (and their values) connected to the product.

To do that, we look into the eav_attribute table. This table connects all attributes to their respective entities. One column in this table is called entity_type_id. This column relates to the entity_type_id column in the catalog_product_entity table, as shown in the following figure:

Once the associated attribute for an entity (in this example, a product) is determined, Magento next works to associate the actual value for the attribute. Here's where it gets a bit complicated, but fun!

For each attribute, Magento stores a type for the associated value. In other words, is the value a decimal value (such as price), a text value (for example, description), and so on. These are stored in the eav_attribute table in a column named backend_type. For each type, Magento has a corresponding table whose name ends in the particular type. The following are the value tables for product entities:

catalog_product_entity_datetime
catalog_product_entity_decimal
catalog_product_entity_int
catalog_product_entity_text
catalog_product_entity_varchar

If a lookup of a product's attribute shows a type of decimal, then the associated value would be found in the catalog_product_entity_decimal table. The following figure illustrates this basic relationship:

If you take a look at the Magento data tables, you'll now begin to understand the relationship between various entities, attributes, and values.

The good and bad of EAV

EAV is a key feature of Magento that allows developers to extend the platform without changing its core data structure. Imagine that you want to add new functionality that depends on a new product attribute - you could simply add that attribute into the system without adding a single new column to any table!

Unfortunately, there is a trade-off: performance. As you can see, in order to pull in all the information for a product — such as for a product detail page — Magento has to do a lot of calls to a lot of tables within the database. These lookups take time and server resources.

Fortunately, Magento's developers are still a step ahead of us.

Making it flat

Long ago, developers realized that while EAV was a cool way to build extendability into Magento, it added a major hit in performance. All the lookups, particularly for busy sites, can really slow down response times. MySQL, the database used for Magento, is a single-threaded database, meaning it can only handle one operation at a time.

The solution was to take all the various relationships and pre-compile them into other tables. In essence, Magento could do its lookups using fewer tables (and therefore fewer SQL statements) in order to get the same data.

If you look again at the data tables, you'll see a number of tables with index or flat in the name. These tables combine the EAV relationships into one table.

In order to take advantage of this feature for categories and products (sales orders are automatically flattened):

Log into your Magento backend.
Go to Stores | Configuration | Catalog.
Click to expand Storefront.
Select Yes for both Use Flat Catalog Category and Use Flat Catalog Product.
Click on Save Config.

After saving, you should get a notice at the top of your Admin page, like the following screenshot:

One of the things you will have to do as you add or update products and categories is to reindex your site. We'll discuss indexing in more depth in the next section.

Table of Contents for Magento 2 - Build World-Class online stores