This link has been bookmarked by 1 people . It was first bookmarked on 29 Jun 2008, by Frederik Van Zande.
-
29 Jun 08
Frederik Van Zandekalqlate suggests a way to get better performance with an EAV compiler, around 75% less query joines.
-
Hi all,
Two things:
1) @Alexis: I really wouldn’t muck around too much with the Magento DB schema, except for “additions” to it. Otherwise you risk making upgrades a nightmare for yourself and those who follow.
2) @Pricoll: 95% of the spead issues with Magento ARE NOT with CSS, JS, or compression. The curse/blessing of Magento is its flexible EAV implementation. (See http://en.wikipedia.org/wiki/Entity-Attribute-Value_model for background on the EAV db model.) Magento is a set of fixed-attribute tables supplemented with extended-attribute tables. The extended-attribute tables are what allow you to mold Magento into what you want, for the most part, without altering the db schema. Every extended-attribute comes at the cost of several EXTRA join queries that a flat/traditional and less flexible schema would not incur.
Therein lies the speed issues with Magento. They will not be resolved until the Magento team tackles the issue and opts to implements what I would call an “EAV compiler”. I’ve sketched one out that would retain the flexibility of EAV while at the same time result in approximately 75% fewer queries/joins. It would work by doing a one time bulk materialization of current data to a working semi-flat view. This semi-flat view allows direct reads and rights on the data with absolute field names and data types as in non-EAV schemas. Each time an attribute is added or removed from an “Entity Attribute Map” the corresponding “flat” table schema is updated. This is no
-
Oops, got cut off.
--continued---
This is not a problem as catalogs, categories, and product attributes are modified far less often than they are used for the real job/goal of shopping. Depending on the entity, this type of materialization will result in fewer or more tables for a given entity than the current EAV implementation. Even if the number of tables were to mushroom to 3 times as much, who cares? Speed of use is the bottom line. Let the software manage the underlying complexity.
The EAV compiler that I’ve sketched out would consist of the following tables:
-Attribute_Maps which identify ( ID ) “common” groups of attributes that are detailed in…
-Attribute_Map_Attributes which identifies and describes each attribute from all Attribute_Maps and which correspond to the absolute field names in the _Values table described below. If a given field is itself an attribute map then that will be its type whose value in the _Value table below will be a pointer to another attribute map.
The first two tables are only touched during attribute modification. Thereafter, only the _Values tables are accessed, much as in non-EAV paradigms.
-Attribute_Map_( ID )_Values which contain the fully materialized set of fields for a given attribute group. The ( ID ) portion of the table name corresponds to the ID of the Attribute_Map for which it holds the values. Each row has the absolute field names, types, and data and also has fields that hold entity_typ
-
and the last bit-
...and data and also has fields that hold entity_type_id, entity_id.
Sounds complex? A bit. But it would remove a great deal of the thrashing that goes on beneath Magento’s beautiful skin.
Cheers,
David -
Oh, just thought I’d point out that each Attribute_Map_( ID )_Values table would look and operate like a traditional flat data table with the number of columns corresponding to the Attribute_Map that it represents, the column names and column types will correspond to the field names and column types given in Attribute_Map_Attributes.
As an example of the savings: Currently reads on products that contain 8 attributes requires multiple queries to discover where and what the data is and then finally another query that joins the 5 data type tables to get the actual data. In the semi-flat materialized mode discovery would be slashed to one query with one query for the actual data (with two sub queries for any sub attribute types which will be rare and currently not supported by Magento. I was just looking ahead).
David
-
Would you like to comment?
Join Diigo for a free account, or sign in if you are already a member.