In this article I am going to talk about typical mistakes that I have observed in almost every Magento project which had low productivity. Working with Magento I sometimes have to conduct an audit of someone’s code. That is why I would like to share my experience and help you to increase Magento’s productivity and avoid mistakes in the future.
*In this article you will find information about Magento 1 , however, the following tips are applicable for Magento 2 as well.
The reasons for that are simple:
To explain how to increase productivity I would like to highlight the following issues:
EAV is an approach for data storage when the entity to which the attribute refers, the attribute itself and its value are separated in different tables.
In Magento the EAV entities include: products, categories, customers and customers’ addresses. The attributes are stored in eav_attribute tables.
There are four types of attribute values in Magento: text, varchar, int, decimal and datetime. The fifth type that is called static is store in entity table. In attribute table it is specified in which table or what type the attribute is and in such a way Magento “knows” where to read it from and where to write it.
Such value storage allows to have simply implemented sets of attributes (where each entity can have its own attribute or not to have it at all); adding new attribute means new line in database.
How it is stored in DB:
Flat is a common approach where everything is stored in one place and no additional tables are needed. In order to get an item and all attributes enter: SELECT – FROM table
WHERE id = some id
From EAV entities, Flat are applicable for categories and items only.
How it is stored in DB:
In order to include attribute in Flat table and generally enable Flat tables type the following:
Pros and cons of EAV and Flat tables:
+ more flexible
+ no need to re-index the data when adding new attribute
+ almost unlimited amount of attributes
+ availability of all attributes
+ presenceof static attributes (sku, created_at, updated_at) even when they are not specifically indicated
– Fatal error: Call to a member function get Back end () when selecting / filtering of non-existing attribute
+ Only existing attributes that are added to Flat table can be selected/filtered
– Limitation of line size (65,535 bytes or 85 varchar 255) and number of columns (1000 for InnoDB)
– Used for collections only
– The result differs from respond to EAV request (no static attributes)
– Re-indexing is needed otherwise EAV tables will be used
– Adding of new attribute requires re-indexing of Flat table
Many of you may say that there is no need to figure out how to speed up DB requests and generally how the collections work because cache will solve all the problems. Briefly, cache has nothing to do with that. None of Magento cache types is able to cache the collections automatically as well as work in your custom controllers and models that you are using when importing data, for instance.
In order to show why it is necessary to do some actions differently than others got used to, I decided to carry out some performance tests of different approaches. Let’s start from testbed:
OS X 10.10
3.1 GHz Intel Core i5 (4 cores)
Magento EE 1.14.0
2000 CMS pages
I created the extension with one controller and one action. Each test was carried out 5 times and then the average was time was counted. All results are given in seconds.
With load of models within the cycle
3 types of Selection:
1. addAttributeToSelect ( ‘*’); // All the attributes
2. addAttributeToSelect ( ‘sku’); // 1 Static attribute
3. addAttributeToSelect ( ‘name’); // 1 standard attribute
As you probably noticed, the time without load of models is much less than with load of models. Also the time is smaller when Flat tables are on (no extra joins and unions) and we choose the necessary attributes.
in the first case we carry out load with a bunch of joins and then do it for the model again…and then over and over again. In the second case, we do it for static attribute (it is stored in the same table as the product) and Magento does not have to make joins. That is why less time is needed. In the third case Magento has to join the table where the attribute is stored.
Similarly for Flat tables: in two cases the figures are the same because both attributes are stored in one table and, therefore, the time is the same.
I suggest checking selection without cache, with the help of method offered by Magento
and with a workaround which I have not observed anywhere else. I invented it myself basing on the methods of cache model.
Selection without cache is obvious. On the contrary, Magento method increased the time what surprised me personally. EAV cache was not a good idea at all since EAV collection loads entities from product tables first (that is what’s being cached) and then with separate request selects the attribute values and fills in the objects. In Flat collection everything is selected from one table.
more time is needed for work with cache than with DB. InitCache means that it first collects all data (pagination, filters, events, etc.) in to collection itself, creates a hash from sql request which will then search in cache; if he finds anything there he will then analyze it and after all the launch of all events and other further methods will start. This is the slowest procedure in the whole process and this shows that cache works much slower than a simple request in DB.
I want to explain the method invented by myself that implies caching final result of the collection and, what is more, excluding all events and additional load of attributes.
• The proper use of count () and getSize()
The difference between the methods lies in the following: count() loads all objects of the collection and then with the help of php count counts the number of objects and gives us the figure.
GetSize() does not load the collection and generates one more request in DB where there are no limits, orders and lists of selected attributes. There is only COUNT* .
If you need to know whether the values are in DB and how many are they, then use getSize(). If you need already loaded collection, then use count() – he will gives you the number of elements that are already loaded in collection.
• The proper use of getFirstItem and setPage (1,1)
The disadvantage of getFirstItem is that he loads the entire collection and then returns the first element in to the foreach; if it is absent he returns the empty element.
setPage (он же $this->setCurPage($pageNum)->setPageSize($pageSize)) limits the selection to one record only what, as you can see, speeds up the load of result.
Even load is faster than getFirstItem, however, load is much slower than selection of one-element collection. The reason is that load always works with EAV tables.
I would like to give some tips for those who are working with Magento:
• Never call load method for objects derived from collection
• Load only necessary attributes
• Use Flat table if applicable for the project
• Use count to count the results of loaded collection and getSize to get the number of all records
• Do not use GetFirstItem without setPage(1,1) or similar methods.