Frederik Van Zande's Bookmarks tagged magento →  View Popular

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