Türchen 09: StoreView-spezifische EAV-Daten über schnelle SQL-Abfragen auslesen

Mit direkten SQL-Abfragen lassen sich viele Daten aus der Magento-Datenbank oft deutlich schneller auslesen, auch wenn diese über EAV-Tabellen verteilt sind. Als Nachtrag zu dem Artikel wie man mit SQL-Abfragen Produkt- und Kategoriedaten schnell ausliest wird hier gezeigt, wie man StoreView-spezifische Werte in einer Abfrage berücksichtigen kann, sodass die Abfrage weiterhin sehr schnell bleibt.

Alle EAV-Tabellen (z.B. catalog_product_entity_varchar) verfügen über die Spalte store_id, die den StoreView bestimmt, für den der Wert gelten soll. Für Standardwerte enthält diese Spalte den Wert 0, für alle anderen StoreViews einen Wert größer 0. Wird z. B. der Name eines Produkts für einen StoreView festgelegt, wird in der EAV-Tabelle eine zweite Zeile für das gleiche Produkt und Attribut eingefügt. Wird im Backend der StoreView-spezifische Wert durch Setzen des Schalters Standardwerte verwenden gelöscht, wird auch die Zeile in der EAV-Tabelle gelöscht.

Unser Ziel besteht nun darin, eine schnelle SQL-Abfrage zu formulieren, die für alle Produkte den Namen für ein StoreView (z. B. store_id=2) ausgibt. Ist der StoreView-spezifische Name nicht festgelegt, muss der Standardwert für store_id=0 ausgegeben werden. Mit einem StoreView-Filter bestehend aus einer einfachen WHERE-Bedingung würde diese Abfrage für einige Produkte mehrere Zeilen liefern:

SELECT entity_id, sku, eav_vc.value as name, store_id FROM catalog_product_entity
JOIN catalog_product_entity_varchar eav_vc USING(entity_id)
JOIN eav_attribute eav ON (eav_vc.entity_type_id=eav.entity_type_id AND eav_vc.attribute_id=eav.attribute_id
AND eav.attribute_code = 'name')
WHERE store_id IN (0, 2);

Filtern wir nur die Zeilen mit store_id=2, bekommen wir nur die Produkte, bei den der Name für diesen StoreView festgelegt wurde. Gruppieren wir die Liste nach der entity_id, verschwinden die StoreView-spezifischen Werte aus der Liste, da die jeweilige Gruppe stets die erste Zeile mit dem Standardwert enthält.

Der SQL-Trick besteht nun darin, durch Verschachtelung der Abfrage eine absteigende Sortierung vor der Gruppierung durchzuführen, sodass die Zeile mit der store_id=2 immer als erste in jeweiliger Gruppe auftaucht:

SELECT * FROM (
  SELECT entity_id, sku, eav_vc.value as name, store_id FROM catalog_product_entity
  JOIN catalog_product_entity_varchar eav_vc USING(entity_id)
  JOIN eav_attribute eav ON (eav_vc.entity_type_id=eav.entity_type_id AND eav_vc.attribute_id=eav.attribute_id
  AND eav.attribute_code = 'name')
  WHERE store_id IN (0, 2)
  ORDER BY store_id DESC
) AS t
GROUP BY entity_id;



Ein Beitrag von Pawel Kazakow
Pawel's avatar

Pawel Kazakow ist Magento Certified Developer Plus und CompTIA Certified Technical Trainer und der Inhaber von xonu EEC, einem Anbieter von E-Commerce-Lösungen mit Magento. Der Shop-Betreiber bekommt ein Full-Service-Paket: Vom individuellen Shop-Design, Einrichtung und Integration des Magento-Systems, Anpassung und Magento-Entwicklung, Performance Optimierung, Produkt-Import, SEO/SEM, Mitarbeiter-Training, Shop-Wartung und Unterstützung beim Marketing.

Alle Beiträge von Pawel

Kommentare
Hansjörg am

Ich würde sehr gern ein select coupon aus der Datenbank auslesen und diesen dann auf einer Magento cms-Seite ausgeben lassen. Wie muss ich da vor gehen ?

Daniel Niedergesäß am

Hallo, ich habe mir eben das Query mal angeschaut und noch ein wenig Optimirungspotetial gefunden. Das ganze hängt ein wenig mit den Internen Abläufen in MySQL zusammen. Wenn man ein GROUP BY macht, wird das Ergebnis automatisch nach den / der Spalte(n) sortiert. Wenn man bei GROUP BY die Sortierung weglässt wird trotzdem nach der Spalte aus der Group By Regel Sortiert. Abhilfe schafft hier ein ORDER BY NULL. Dadurch wird in den meisten Fällen ein Filesort bzw bei größeren Ergebnissen ein Filesort on Disc erspart. Das Problem tauch ziemlich oft im Magento Core auf. In meinem Falle brachte das ganze eine Ersparniss von knapp einer Sekunde. Mit diesem Trick lassen sich die Indexer von Magento beschleunigen. ggf baue ich mal einen Patch.

Hier mal das Query Rewritten:

SELECT SQL_NO_CACHE * FROM ( SELECT entity_id, sku, eav_vc.value AS name, store_id FROM catalog_product_entity JOIN catalog_product_entity_varchar eav_vc USING(entity_id) JOIN eav_attribute eav ON ( eav_vc.entity_type_id = eav.entity_type_id AND eav_vc.attribute_id = eav.attribute_id AND eav.attribute_code = 'name' ) WHERE store_id IN ( 0, 2 ) ORDER BY store_id DESC ) AS t GROUP BY entity_id ORDER BY NULL

Vinai Kopp am

Gute Frage. Das dürfte schwierig zu ermitteln sein - je nach Menge an Datensätzen und Menge der vorhandenen Store-View Werten könnte es große Unterschiede geben. Die verwendete MySQL Version dürfte auch ein Faktor sein. Der Aufwand um Festzustellen ob sich die Frage generell beantworten lässt ist bestimmt schon ganz ordentlich. Und dann müssten noch die eigentlichen Benchmarks gemacht werden. Vermutlich lohnt es sich nur jeweils den konkreten Fall in einem Projekt zu optimieren. Leider.

Tobias Vogt am

Was ist hier wohl schneller? Gerade beim sortierten. IF mit null oder group-by?

Vinai Kopp am

Danke, immer wieder schön zu sehen das gut mit den EAV Tabellen auf DB Ebene gearbeitet wird!

Nur der Vollständigkeit halber noch eine Variante: Der Magento EAV Core Code macht ein INNER JOIN auf die value Tabelle mit store_id=0 (mit alias t_d für table default) und ein LEFT JOIN auf die gleiche value Tabelle mit store_id=$currentStoreId (alias t_s für table store) und unterscheidet dann in den SELECT Feldern mit IFNULL(t_s.value,t_d.value) ob es einen store view spezifischen wert gibt oder nicht.

Um diese Variante Magento konform zu konstruieren sollte das SQL Feld mit Hilfe von

$select->columns('value' => $adapter->getIfNullSql('t_d.value', 't_s.value'))

gebaut werden.

Andreas von Studnitz am

Danke für den Tipp. Kann man das (den? die?) SQL-Query in Magento-konformem Code umschreiben?

Wenn man nur einen Wert für ein einziges Produkt auslesen möchte und dafür nicht das komplette Produkt laden oder eine Collection aufbauen möchte, kann mein Blogbeitrag unter http://www.avs-webentwicklung.de/nc/blog/artikel/schnelles-auslesen-von-attributen.html helfen.

Dein Kommentar