Ständig Ärger mit der DB - lausige Performance die Zweite

Archive Shopware 3.5
1

Hi, seit wir Anfang Dezember auf 4.05 geupdatet haben hat unser Virtual Server Max ständig Performance Probleme die bis zur kompletten Nichterreichbarkeit gehen. Am Anfang wurden die Probleme öfters durch den BingBot ausgelöst, was aber mittlerweile gelöst ist (Kombo aus einen Teil der MSN IPs „über die .htaccess aussperren“ und für den Rest crawl delay). Nachdem wir die Sache mit dem Bingbot gelöst haben bleibt noch das Problem mit der Shopdatenbank. Der Shop läuft tlw. über Stunden sehr schnell um dann plötzlich (wir haben eigentlich keine Dienste, nur einen Piwik Cronjob jede Stunde) einen enormen Lastanstieg bei den Apache Prozessen zu verzeichnen und danach (meistens) komplett abzusaufen (allein gestern hatten wir 7 (!) Reboots)…ihr könnt euch sicher vorstellen wieviel Spaß es macht alle paar Minuten zu schauen ob der Shop noch „da“ ist…:x Lt. HostEurope ist unser Paket aber mehr als ausreichend dimensioniert (eher sogar überdimensioniert)…der Support ist der Meinung das das Problem die Shop-Datenbank ist und rät uns die Datenbank zu „optimieren“. Ich hab mal die Slow-Queries unten angehängt (sorry für das ewig lange Log…:oops:…habe es schon gekürzt damit ich es überhaupt posten kann…;)) Jemand eine Idee woran es liegen könnte? Bin langsam echt ratlos… [quote]/usr/sbin/mysqld, Version: 5.1.49-3~bpo50+1-log ((Debian)). started with: Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock Time Id Command Argument # Time: 130203 14:30:56 # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 18.518514 Lock_time: 0.000428 Rows_sent: 0 Rows_examined: 354 use db1111111-ksv12; SET timestamp=1359898256; SELECT fv.optionID AS id, COUNT(DISTINCT a.id) AS count, fo.id AS optionID, fo.name AS optionName, f.id AS groupID, f.name AS groupName, fv.value AS optionValue, fv.id AS valueID, st.objectdata AS optionNameTranslation, st2.objectdata AS groupNameTranslation, st3.objectdata AS valueTranslation FROM s_categories c, s_categories c2, s_articles_categories ac JOIN s_filter_articles fa ON fa.articleID=ac.articleID JOIN s_filter_values fv ON fv.id=fa.valueID JOIN s_filter_options fo ON fo.id=fv.optionID AND fo.filterable = 1 JOIN s_articles a ON a.id=ac.articleID AND a.active =1 AND a.changetime <= NOW() JOIN s_filter f ON f.id=a.filtergroupID LEFT JOIN s_filter_relations fr ON f.id = fr.groupId AND fo.id = fr.optionId LEFT JOIN s_articles_avoid_customergroups ag ON ag.articleID=fa.articleID AND ag.customergroupID=1 LEFT JOIN s_core_translations AS st ON st.objecttype=‚propertyoption‘ AND st.objectkey=fv.optionID AND st.objectlanguage=’’ LEFT JOIN s_core_translations AS st2 ON st2.objecttype=‚propertygroup‘ AND st2.objectkey=f.id AND st2.objectlanguage=’’ LEFT JOIN s_core_translations AS st3 ON st3.objecttype=‚propertyvalue‘ AND st3.objectkey=fv.id AND st3.objectlanguage=’’ INNER JOIN s_filter_articles fv1110 ON fv1110.articleID = a.id AND fv1110.valueID = 1110 INNER JOIN s_filter_articles fv1111 ON fv1111.articleID = a.id AND fv1111.valueID = 1111 INNER JOIN s_filter_articles fv1124 ON fv1124.articleID = a.id AND fv1124.valueID = 1124 INNER JOIN s_filter_articles fv1191 ON fv1191.articleID = a.id AND fv1191.valueID = 1191 INNER JOIN s_filter_articles fv1192 ON fv1192.articleID = a.id AND fv1192.valueID = 1192 INNER JOIN s_filter_articles fv1196 ON fv1196.articleID = a.id AND fv1196.valueID = 1196 INNER JOIN s_filter_articles fv1201 ON fv1201.articleID = a.id AND fv1201.valueID = 1201 WHERE c.id=3 AND c2.active=1 AND c2.left >= c.left AND c2.right <= c.right AND ac.articleID=a.id AND ac.categoryID=c2.id AND ag.articleID IS NULL GROUP BY fv.id ORDER BY fr.position, fo.name ASC, IF(f.sortmode=1, TRIM(REPLACE(fv.value,’,’,’.’))+0, 0), IF(f.sortmode=2, COUNT(*) , 0) DESC, IF(f.sortmode=3, fv.position, 0), fv.value; # Time: 130203 14:36:26 # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 317.081396 Lock_time: 0.000311 Rows_sent: 0 Rows_examined: 354 SET timestamp=1359898586; SELECT fv.optionID AS id, COUNT(DISTINCT a.id) AS count, fo.id AS optionID, fo.name AS optionName, f.id AS groupID, f.name AS groupName, fv.value AS optionValue, fv.id AS valueID, st.objectdata AS optionNameTranslation, st2.objectdata AS groupNameTranslation, st3.objectdata AS valueTranslation FROM s_categories c, s_categories c2, s_articles_categories ac JOIN s_filter_articles fa ON fa.articleID=ac.articleID JOIN s_filter_values fv ON fv.id=fa.valueID JOIN s_filter_options fo ON fo.id=fv.optionID AND fo.filterable = 1 JOIN s_articles a ON a.id=ac.articleID AND a.active =1 AND a.changetime <= NOW() JOIN s_filter f ON f.id=a.filtergroupID LEFT JOIN s_filter_relations fr ON f.id = fr.groupId AND fo.id = fr.optionId LEFT JOIN s_articles_avoid_customergroups ag ON ag.articleID=fa.articleID AND ag.customergroupID=1 LEFT JOIN s_core_translations AS st ON st.objecttype=‚propertyoption‘ AND st.objectkey=fv.optionID AND st.objectlanguage=’’ LEFT JOIN s_core_translations AS st2 ON st2.objecttype=‚propertygroup‘ AND st2.objectkey=f.id AND st2.objectlanguage=’’ LEFT JOIN s_core_translations AS st3 ON st3.objecttype=‚propertyvalue‘ AND st3.objectkey=fv.id AND st3.objectlanguage=’’ INNER JOIN s_filter_articles fv1110 ON fv1110.articleID = a.id AND fv1110.valueID = 1110 INNER JOIN s_filter_articles fv1111 ON fv1111.articleID = a.id AND fv1111.valueID = 1111 INNER JOIN s_filter_articles fv1124 ON fv1124.articleID = a.id AND fv1124.valueID = 1124 INNER JOIN s_filter_articles fv1191 ON fv1191.articleID = a.id AND fv1191.valueID = 1191 INNER JOIN s_filter_articles fv1211 ON fv1211.articleID = a.id AND fv1211.valueID = 1211 INNER JOIN s_filter_articles fv1198 ON fv1198.articleID = a.id AND fv1198.valueID = 1198 INNER JOIN s_filter_articles fv1207 ON fv1207.articleID = a.id AND fv1207.valueID = 1207 INNER JOIN s_filter_articles fv1204 ON fv1204.articleID = a.id AND fv1204.valueID = 1204 WHERE c.id=3 AND c2.active=1 AND c2.left >= c.left AND c2.right <= c.right AND ac.articleID=a.id AND ac.categoryID=c2.id AND ag.articleID IS NULL GROUP BY fv.id ORDER BY fr.position, fo.name ASC, IF(f.sortmode=1, TRIM(REPLACE(fv.value,’,’,’.’))+0, 0), IF(f.sortmode=2, COUNT(*) , 0) DESC, IF(f.sortmode=3, fv.position, 0), fv.value; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 103.414477 Lock_time: 0.000102 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898586; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 78.424686 Lock_time: 0.000119 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898586; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 70.533458 Lock_time: 0.000127 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898586; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 18.450549 Lock_time: 0.000178 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898586; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 48.511954 Lock_time: 0.000147 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898586; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 25.237254 Lock_time: 0.000131 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898586; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # Time: 130203 14:38:49 # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 143.134847 Lock_time: 0.000100 Rows_sent: 2 Rows_examined: 62 SET timestamp=1359898729; SELECT ce.name, IFNULL(IFNULL(cv.value, cm.value), ce.value) as value FROM s_core_plugins p JOIN s_core_config_forms cf ON cf.plugin_id = p.id JOIN s_core_config_elements ce ON ce.form_id = cf.id LEFT JOIN s_core_config_values cv ON cv.element_id = ce.id AND cv.shop_id = NULL LEFT JOIN s_core_config_values cm ON cm.element_id = ce.id AND cm.shop_id = ‚1‘ WHERE p.name=‚Log‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 142.906776 Lock_time: 0.000094 Rows_sent: 0 Rows_examined: 58 SET timestamp=1359898729; SELECT s0_.id AS id0 FROM s_emarketing_banners s0_ WHERE (s0_.valid_from <= ‚2013-02-03 14:36:26‘ OR (s0_.valid_from = NULL OR s0_.valid_from IS NULL)) AND (s0_.valid_to >= ‚2013-02-03 14:36:26‘ OR (s0_.valid_to = NULL OR s0_.valid_to IS NULL)) AND s0_.categoryID = 3; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 142.908353 Lock_time: 0.000465 Rows_sent: 1 Rows_examined: 9 SET timestamp=1359898729; SELECT s0_.id AS id0, s0_.main_id AS main_id1, s0_.name AS name2, s0_.title AS title3, s0_.position AS position4, s0_.host AS host5, s0_.base_path AS base_path6, s0_.base_url AS base_url7, s0_.hosts AS hosts8, s0_.secure AS secure9, s0_.secure_host AS secure_host10, s0_.secure_base_path AS secure_base_path11, s0_.default AS default12, s0_.active AS active13, s0_.customer_scope AS customer_scope14, s1_.id AS id15, s1_.main_id AS main_id16, s1_.name AS name17, s1_.title AS title18, s1_.position AS position19, s1_.host AS host20, s1_.base_path AS base_path21, s1_.base_url AS base_url22, s1_.hosts AS hosts23, s1_.secure AS secure24, s1_.secure_host AS secure_host25, s1_.secure_base_path AS secure_base_path26, s1_.default AS default27, s1_.active AS active28, s1_.customer_scope AS customer_scope29, s2_.id AS id30, s2_.locale AS locale31, s2_.language AS language32, s2_.territory AS territory33, s3_.id AS id34, s3_.currency AS currency35, s3_.name AS name36, s3_.standard AS standard37, s3_.factor AS factor38, s3_.templatechar AS templatechar39, s3_.symbol_position AS symbol_position40, s3_.position AS position41, s4_.id AS id42, s4_.template AS template43, s4_.name AS name44, s4_.description AS description45, s4_.author AS author46, s4_.license AS license47, s4_.esi AS esi48, s4_.style_support AS style_support49, s4_.emotion AS emotion50, s4_.version AS version51, s4_.plugin_id AS plugin_id52, s5_.id AS id53, s5_.currency AS currency54, s5_.name AS name55, s5_.standard AS standard56, s5_.factor AS factor57, s5_.templatechar AS templatechar58, s5_.symbol_position AS symbol_position59, s5_.position AS position60, s0_.main_id AS main_id61, s0_.template_id AS template_id62, s0_.document_template_id AS document_template_id63, s0_.category_id AS category_id64, s0_.locale_id AS locale_id65, s0_.currency_id AS currency_id66, s0_.customer_group_id AS customer_group_id67, s0_.fallback_id AS fallback_id68, s1_.main_id AS main_id69, s1_.template_id AS template_id70, s1_.document_template_id AS document_template_id71, s1_.category_id AS category_id72, s1_.locale_id AS locale_id73, s1_.currency_id AS currency_id74, s1_.customer_group_id AS customer_group_id75, s1_.fallback_id AS fallback_id76, s4_.plugin_id AS plugin_id77 FROM s_core_shops s0_ LEFT JOIN s_core_shops s1_ ON s0_.main_id = s1_.id LEFT JOIN s_core_locales s2_ ON s0_.locale_id = s2_.id LEFT JOIN s_core_currencies s3_ ON s0_.currency_id = s3_.id LEFT JOIN s_core_templates s4_ ON s0_.template_id = s4_.id LEFT JOIN s_core_shop_currencies s6_ ON s0_.id = s6_.shop_id LEFT JOIN s_core_currencies s5_ ON s5_.id = s6_.currency_id LEFT JOIN s_core_shop_pages s8_ ON s0_.id = s8_.shop_id LEFT JOIN s_cms_static_groups s7_ ON s7_.id = s8_.group_id LEFT JOIN s_core_customergroups s9_ ON s0_.customer_group_id = s9_.id LEFT JOIN s_core_templates s10_ ON s1_.template_id = s10_.id LEFT JOIN s_core_shop_currencies s12_ ON s1_.id = s12_.shop_id LEFT JOIN s_core_currencies s11_ ON s11_.id = s12_.currency_id WHERE s0_.active = 1 AND (s0_.host = ‚www.kaffeestore.com‘ OR (s0_.host IS NULL AND s1_.host = ‚www.kaffeestore.com‘)) ORDER BY s0_.main_id ASC, s0_.position ASC, s5_.position ASC, s5_.id ASC; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 142.911663 Lock_time: 0.000038 Rows_sent: 14 Rows_examined: 14 SET timestamp=1359898729; SELECT basename, basefile, inheritname, inheritfile FROM s_core_factory; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 142.911430 Lock_time: 0.000026 Rows_sent: 1 Rows_examined: 1 SET timestamp=1359898729; SELECT host FROM s_core_shops WHERE default=1; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 129.847322 Lock_time: 0.000124 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898729; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 112.925369 Lock_time: 0.000099 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898729; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 93.040669 Lock_time: 0.000099 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898729; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 82.985717 Lock_time: 0.000112 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898729; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 71.957817 Lock_time: 0.000127 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898729; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 101.830903 Lock_time: 0.000094 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898729; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 41.292093 Lock_time: 0.000225 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898729; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 142.947972 Lock_time: 0.000102 Rows_sent: 2 Rows_examined: 62 SET timestamp=1359898729; SELECT ce.name, IFNULL(IFNULL(cv.value, cm.value), ce.value) as value FROM s_core_plugins p JOIN s_core_config_forms cf ON cf.plugin_id = p.id JOIN s_core_config_elements ce ON ce.form_id = cf.id LEFT JOIN s_core_config_values cv ON cv.element_id = ce.id AND cv.shop_id = ‚1‘ LEFT JOIN s_core_config_values cm ON cm.element_id = ce.id AND cm.shop_id = ‚1‘ WHERE p.name=‚Compare‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 143.230793 Lock_time: 0.000067 Rows_sent: 5 Rows_examined: 65 SET timestamp=1359898729; SELECT ce.name, IFNULL(IFNULL(cv.value, cm.value), ce.value) as value FROM s_core_plugins p JOIN s_core_config_forms cf ON cf.plugin_id = p.id JOIN s_core_config_elements ce ON ce.form_id = cf.id LEFT JOIN s_core_config_values cv ON cv.element_id = ce.id AND cv.shop_id = ‚1‘ LEFT JOIN s_core_config_values cm ON cm.element_id = ce.id AND cm.shop_id = ‚1‘ WHERE p.name=‚LastArticles‘; # Time: 130203 14:38:50 # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 17.258584 Lock_time: 0.000179 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898730; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 12.810736 Lock_time: 0.000151 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898730; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 33.768010 Lock_time: 0.000193 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898730; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 400.923219 Lock_time: 0.000446 Rows_sent: 0 Rows_examined: 354 SET timestamp=1359898730; SELECT fv.optionID AS id, COUNT(DISTINCT a.id) AS count, fo.id AS optionID, fo.name AS optionName, f.id AS groupID, f.name AS groupName, fv.value AS optionValue, fv.id AS valueID, st.objectdata AS optionNameTranslation, st2.objectdata AS groupNameTranslation, st3.objectdata AS valueTranslation FROM s_categories c, s_categories c2, s_articles_categories ac JOIN s_filter_articles fa ON fa.articleID=ac.articleID JOIN s_filter_values fv ON fv.id=fa.valueID JOIN s_filter_options fo ON fo.id=fv.optionID AND fo.filterable = 1 JOIN s_articles a ON a.id=ac.articleID AND a.active =1 AND a.changetime <= NOW() JOIN s_filter f ON f.id=a.filtergroupID LEFT JOIN s_filter_relations fr ON f.id = fr.groupId AND fo.id = fr.optionId LEFT JOIN s_articles_avoid_customergroups ag ON ag.articleID=fa.articleID AND ag.customergroupID=1 LEFT JOIN s_core_translations AS st ON st.objecttype=‚propertyoption‘ AND st.objectkey=fv.optionID AND st.objectlanguage=’’ LEFT JOIN s_core_translations AS st2 ON st2.objecttype=‚propertygroup‘ AND st2.objectkey=f.id AND st2.objectlanguage=’’ LEFT JOIN s_core_translations AS st3 ON st3.objecttype=‚propertyvalue‘ AND st3.objectkey=fv.id AND st3.objectlanguage=’’ INNER JOIN s_filter_articles fv1110 ON fv1110.articleID = a.id AND fv1110.valueID = 1110 INNER JOIN s_filter_articles fv1120 ON fv1120.articleID = a.id AND fv1120.valueID = 1120 INNER JOIN s_filter_articles fv1131 ON fv1131.articleID = a.id AND fv1131.valueID = 1131 INNER JOIN s_filter_articles fv1193 ON fv1193.articleID = a.id AND fv1193.valueID = 1193 INNER JOIN s_filter_articles fv1192 ON fv1192.articleID = a.id AND fv1192.valueID = 1192 INNER JOIN s_filter_articles fv1199 ON fv1199.articleID = a.id AND fv1199.valueID = 1199 INNER JOIN s_filter_articles fv1202 ON fv1202.articleID = a.id AND fv1202.valueID = 1202 INNER JOIN s_filter_articles fv596 ON fv596.articleID = a.id AND fv596.valueID = 596 WHERE c.id=3 AND c2.active=1 AND c2.left >= c.left AND c2.right <= c.right AND ac.articleID=a.id AND ac.categoryID=c2.id AND ag.articleID IS NULL GROUP BY fv.id ORDER BY fr.position, fo.name ASC, IF(f.sortmode=1, TRIM(REPLACE(fv.value,’,’,’.’))+0, 0), IF(f.sortmode=2, COUNT(*) , 0) DESC, IF(f.sortmode=3, fv.position, 0), fv.value; # Time: 130203 14:40:26 # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 96.699936 Lock_time: 0.000143 Rows_sent: 2 Rows_examined: 62 SET timestamp=1359898826; SELECT ce.name, IFNULL(IFNULL(cv.value, cm.value), ce.value) as value FROM s_core_plugins p JOIN s_core_config_forms cf ON cf.plugin_id = p.id JOIN s_core_config_elements ce ON ce.form_id = cf.id LEFT JOIN s_core_config_values cv ON cv.element_id = ce.id AND cv.shop_id = ‚1‘ LEFT JOIN s_core_config_values cm ON cm.element_id = ce.id AND cm.shop_id = ‚1‘ WHERE p.name=‚Compare‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 96.417350 Lock_time: 0.000097 Rows_sent: 0 Rows_examined: 21 SET timestamp=1359898826; SELECT org_path, main FROM s_core_rewrite_urls WHERE path LIKE ‚Deutsch_cat_3.html‘ AND subshopID =‚1‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 96.443652 Lock_time: 0.000030 Rows_sent: 1 Rows_examined: 1 SET timestamp=1359898826; SELECT id as key, m.* FROM s_core_multilanguage m; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 96.699472 Lock_time: 0.000399 Rows_sent: 0 Rows_examined: 915 SET timestamp=1359898826; SELECT a.id as articleID, aDetails.id AS articleDetailsID, a.notification as notification, weight, aDetails.ordernumber, a.datum, aDetails.releasedate, additionaltext, aDetails.shippingfree,aDetails.shippingtime,instock, a.description AS description, description_long, aSupplier.name AS supplierName, aSupplier.img AS supplierImg, a.name AS articleName, topseller as highlight, IFNULL(p.price, p2.price) as price, laststock, sales, IF(p.pseudoprice,p.pseudoprice,p2.pseudoprice) as pseudoprice, aTax.tax, taxID, aDetails.minpurchase, aDetails.purchasesteps, aDetails.maxpurchase, aDetails.purchaseunit, aDetails.referenceunit, aDetails.unitID, pricegroupID, pricegroupActive, IFNULL(p.pricegroup,IFNULL(p2.pricegroup,‚EK‘)) as pricegroup, attr1,attr2,attr3,attr4,attr5,attr6,attr7,attr8,attr9,attr10, attr11,attr12,attr13,attr14,attr15,attr16,attr17,attr18,attr19,attr20, – cd.discount, IFNULL((SELECT 1 FROM s_articles_details WHERE articleID=a.id AND kind=2 LIMIT 1), 0) as variants, (a.configurator_set_id IS NOT NULL) as sConfigurator, IFNULL((SELECT 1 FROM s_articles_esd WHERE articleID=a.id LIMIT 1), 0) as esd, IFNULL(( SELECT CONCAT(AVG(points),’|’,COUNT(*)) as votes FROM s_articles_vote WHERE active=1 AND articleID=a.id), ‚0.00|00‘) as sVoteAverange, IF(DATEDIFF(NOW(), a.datum)<=30,1,0) as newArticle, IF(aDetails.sales>=30,1,0) as topseller, IF(aDetails.releasedate>CURDATE(),1,0) as sUpcoming, IF(aDetails.releasedate>CURDATE(), aDetails.releasedate, ‚‘) as sReleasedate FROM s_categories c, s_categories c2, s_articles_categories ac JOIN s_articles AS a ON a.id=ac.articleID AND a.mode = 0 AND a.active=1 JOIN s_articles_details AS aDetails ON aDetails.id=a.main_detail_id JOIN s_articles_attributes AS aAttributes ON aAttributes.articledetailsID=aDetails.id JOIN s_core_tax AS aTax ON aTax.id=a.taxID LEFT JOIN s_articles_avoid_customergroups ag ON ag.articleID=a.id AND ag.customergroupID=1 LEFT JOIN s_articles_supplier AS aSupplier ON aSupplier.id=a.supplierID INNER JOIN s_filter_articles fv1110 ON fv1110.articleID = a.id AND fv1110.valueID = 1110 INNER JOIN s_filter_articles fv1120 ON fv1120.articleID = a.id AND fv1120.valueID = 1120 INNER JOIN s_filter_articles fv1133 ON fv1133.articleID = a.id AND fv1133.valueID = 1133 INNER JOIN s_filter_articles fv1190 ON fv1190.articleID = a.id AND fv1190.valueID = 1190 INNER JOIN s_filter_articles fv1211 ON fv1211.articleID = a.id AND fv1211.valueID = 1211 INNER JOIN s_filter_articles fv1194 ON fv1194.articleID = a.id AND fv1194.valueID = 1194 INNER JOIN s_filter_articles fv1207 ON fv1207.articleID = a.id AND fv1207.valueID = 1207 INNER JOIN s_filter_articles fv594 ON fv594.articleID = a.id AND fv594.valueID = 594 LEFT JOIN s_articles_prices p ON p.articleDetailsID=aDetails.id AND p.pricegroup=‚EK‘ AND p.to=‚beliebig‘ LEFT JOIN s_articles_prices p2 ON p2.articledetailsID=aDetails.id AND p2.pricegroup=‚EK‘ AND p2.to=‚beliebig‘ WHERE c.id=3 AND c2.active=1 AND c2.left >= c.left AND c2.right <= c.right AND ac.articleID=a.id AND ac.categoryID=c2.id AND ag.articleID IS NULL GROUP BY a.id ORDER BY a.datum DESC, a.id LIMIT 0, 12; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 96.445097 Lock_time: 0.000062 Rows_sent: 1 Rows_examined: 1 SET timestamp=1359898826; SELECT id as key, m.* FROM s_core_multilanguage m; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 96.715264 Lock_time: 0.000290 Rows_sent: 6 Rows_examined: 2154 SET timestamp=1359898826; SELECT (s0_.right - s0_.left - 1) / 2 AS sclr0, (SELECT COUNT(s1_.id) AS dctrn__1 FROM s_categories s2_ INNER JOIN s_articles_categories s3_ ON s2_.id = s3_.categoryID INNER JOIN s_articles s1_ ON s1_.id = s3_.articleID AND (s1_.active = 1) WHERE s2_.active = 1 AND (s2_.right <= s0_.right AND s2_.left >= s0_.left)) AS sclr1, s4_.id AS id2, s0_.id AS id3, s0_.parent AS parent4, s0_.description AS description5, s0_.position AS position6, s0_.left AS left7, s0_.level AS level8, s0_.right AS right9, s0_.metakeywords AS metakeywords10, s0_.metadescription AS metadescription11, s0_.cmsheadline AS cmsheadline12, s0_.cmstext AS cmstext13, s0_.active AS active14, s0_.template AS template15, s0_.blog AS blog16, s0_.showfiltergroups AS showfiltergroups17, s0_.external AS external18, s0_.hidefilter AS hidefilter19, s0_.hidetop AS hidetop20, s0_.noviewselect AS noviewselect21, s0_.changed AS changed22, s0_.added AS added23, s5_.id AS id24, s5_.albumID AS albumID25, s5_.name AS name26, s5_.description AS description27, s5_.path AS path28, s5_.type AS type29, s5_.extension AS extension30, s5_.userID AS userID31, s5_.created AS created32, s5_.file_size AS file_size33, s6_.id AS id34, s6_.categoryID AS categoryID35, s6_.attribute1 AS attribute136, s6_.attribute2 AS attribute237, s6_.attribute3 AS attribute338, s6_.attribute4 AS attribute439, s6_.attribute5 AS attribute540, s6_.attribute6 AS attribute641 FROM s_categories s0_ LEFT JOIN s_media s5_ ON s0_.mediaID = s5_.id LEFT JOIN s_categories_attributes s6_ ON s0_.id = s6_.categoryID LEFT JOIN s_categories_avoid_customergroups s7_ ON s0_.id = s7_.categoryID LEFT JOIN s_core_customergroups s4_ ON s4_.id = s7_.customergroupID AND (s4_.id = 1) WHERE s0_.active = 1 AND s0_.parent = 3 GROUP BY s0_.id HAVING (sclr1 > 0 OR s0_.external IS NOT NULL OR s0_.blog = 1) AND s4_.id IS NULL ORDER BY s0_.left ASC; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 96.726592 Lock_time: 0.000060 Rows_sent: 31 Rows_examined: 31 SET timestamp=1359898826; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Frontend‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 96.677636 Lock_time: 0.000112 Rows_sent: 2 Rows_examined: 62 SET timestamp=1359898826; SELECT ce.name, IFNULL(IFNULL(cv.value, cm.value), ce.value) as value FROM s_core_plugins p JOIN s_core_config_forms cf ON cf.plugin_id = p.id JOIN s_core_config_elements ce ON ce.form_id = cf.id LEFT JOIN s_core_config_values cv ON cv.element_id = ce.id AND cv.shop_id = NULL LEFT JOIN s_core_config_values cm ON cm.element_id = ce.id AND cm.shop_id = ‚1‘ WHERE p.name=‚Log‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 96.708573 Lock_time: 0.000070 Rows_sent: 3 Rows_examined: 67 SET timestamp=1359898826; SELECT ce.name, IFNULL(IFNULL(cv.value, cm.value), ce.value) as value FROM s_core_plugins p JOIN s_core_config_forms cf ON cf.plugin_id = p.id JOIN s_core_config_elements ce ON ce.form_id = cf.id LEFT JOIN s_core_config_values cv ON cv.element_id = ce.id AND cv.shop_id = ‚1‘ LEFT JOIN s_core_config_values cm ON cm.element_id = ce.id AND cm.shop_id = ‚1‘ WHERE p.name=‚Google‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 96.743642 Lock_time: 0.000100 Rows_sent: 4 Rows_examined: 64 SET timestamp=1359898826; SELECT ce.name, IFNULL(IFNULL(cv.value, cm.value), ce.value) as value FROM s_core_plugins p JOIN s_core_config_forms cf ON cf.plugin_id = p.id JOIN s_core_config_elements ce ON ce.form_id = cf.id LEFT JOIN s_core_config_values cv ON cv.element_id = ce.id AND cv.shop_id = ‚1‘ LEFT JOIN s_core_config_values cm ON cm.element_id = ce.id AND cm.shop_id = ‚1‘ WHERE p.name=‚InputFilter‘; # Time: 130203 14:41:45 # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 78.853594 Lock_time: 0.000065 Rows_sent: 0 Rows_examined: 0 SET timestamp=1359898905; SELECT module, host, license FROM s_core_licenses WHERE active=1; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 78.880027 Lock_time: 0.000341 Rows_sent: 1 Rows_examined: 7 SET timestamp=1359898905; SELECT a.id as articleID, d.id AS articleDetailsID, d.ordernumber, datum, sales, topseller as highlight, a.description, a. description_long, s.name AS supplierName, s.img AS supplierImg, a.name AS articleName, a.taxID, IFNULL(p.price,p2.price) as price, IF(p.pseudoprice, p.pseudoprice, p2.pseudoprice) as pseudoprice, tax, attr1,attr2,attr3,attr4,attr5,attr6,attr7,attr8,attr9,attr10, attr11,attr12,attr13,attr14,attr15,attr16,attr17,attr18,attr19,attr20, instock, weight, a.shippingtime, IFNULL(p.pricegroup, IFNULL(p2.pricegroup, ‚EK‘)) as pricegroup, pricegroupID, pricegroupActive, filtergroupID, d.purchaseunit, d.referenceunit, d.unitID, laststock, additionaltext, (a.configurator_set_id IS NOT NULL) as sConfigurator, IFNULL((SELECT 1 FROM s_articles_esd WHERE articleID=a.id LIMIT 1), 0) as esd, IFNULL((SELECT CONCAT(AVG(points),’|’,COUNT(*)) as votes FROM s_articles_vote WHERE active=1 AND articleID=a.id),‚0.00|00‘) as sVoteAverange, IF(DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= a.datum, 1, 0) as newArticle, IF(d.sales>=30, 1, 0) as topseller, IF(d.releasedate > CURDATE(), 1, 0) as sUpcoming, IF(d.releasedate > CURDATE(), d.releasedate, ‚‘) as sReleasedate, (SELECT 1 FROM s_articles_details WHERE articleID=a.id AND kind!=1 LIMIT 1) as sVariantArticle FROM s_articles a JOIN s_articles_details d ON d.articleID=a.id AND d.kind=1 – JOIN s_articles_categories ac – ON ac.articleID=a.id – AND ac.categoryID=1 JOIN s_articles_attributes at ON at.articleID=a.id JOIN s_core_tax t ON t.id=a.taxID LEFT JOIN s_articles_supplier s ON s.id=a.supplierID LEFT JOIN s_articles_prices p ON p.articleDetailsID=d.id AND p.pricegroup=‚EK‘ AND p.from=‚1‘ LEFT JOIN s_articles_prices p2 ON p2.articleDetailsID=d.id AND p2.pricegroup=‚EK‘ AND p2.from=‚1‘ WHERE a.id=‚4‘ AND a.active=1 LIMIT 1; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 78.893115 Lock_time: 0.000134 Rows_sent: 18 Rows_examined: 572 SET timestamp=1359898905; SELECT p.id, p.description, p.link, p.target, g.key as group, m.key as mapping, (SELECT COUNT(*) FROM s_cms_static WHERE parentID=p.id) as childrenCount FROM s_cms_static p, s_cms_static_groups g LEFT JOIN s_cms_static_groups m ON m.id=g.mapping_id LEFT JOIN s_core_shop_pages s ON s.group_id=g.id AND s.shop_id=‚1‘ WHERE g.active=1 AND parentID=0 AND CONCAT(’|’, p.grouping, ‚|‘) LIKE CONCAT(’%|’, g.key, ‚|%‘) AND (m.id IS NULL OR s.shop_id IS NOT NULL) AND (m.id IS NULL OR m.active=1) ORDER BY mapping, p.position, p.description; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 78.838998 Lock_time: 0.000108 Rows_sent: 0 Rows_examined: 58 SET timestamp=1359898905; SELECT s0_.id AS id0 FROM s_emarketing_banners s0_ WHERE (s0_.valid_from <= ‚2013-02-03 14:40:26‘ OR (s0_.valid_from = NULL OR s0_.valid_from IS NULL)) AND (s0_.valid_to >= ‚2013-02-03 14:40:26‘ OR (s0_.valid_to = NULL OR s0_.valid_to IS NULL)) AND s0_.categoryID = 3; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 78.826096 Lock_time: 0.000079 Rows_sent: 0 Rows_examined: 21 SET timestamp=1359898905; SELECT org_path, main FROM s_core_rewrite_urls WHERE path LIKE ‚Deutsch_cat_3.html‘ AND subshopID =‚1‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 175.618684 Lock_time: 0.000067 Rows_sent: 12 Rows_examined: 300 SET timestamp=1359898905; SELECT ce.subscribe as name, ce.listener, ce.position, cp.name as plugin FROM s_core_subscribes ce JOIN s_core_plugins cp ON cp.id=ce.pluginID AND cp.active=1 AND cp.namespace=‚Backend‘ WHERE ce.type=0 ORDER BY name, position; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 8.221703 Lock_time: 0.000144 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898905; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 175.354620 Lock_time: 0.000088 Rows_sent: 12 Rows_examined: 300 SET timestamp=1359898905; SELECT ce.subscribe as name, ce.listener, ce.position, cp.name as plugin FROM s_core_subscribes ce JOIN s_core_plugins cp ON cp.id=ce.pluginID AND cp.active=1 AND cp.namespace=‚Backend‘ WHERE ce.type=0 ORDER BY name, position; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 175.651351 Lock_time: 0.026884 Rows_sent: 31 Rows_examined: 300 SET timestamp=1359898905; SELECT ce.subscribe as name, ce.listener, ce.position, cp.name as plugin FROM s_core_subscribes ce JOIN s_core_plugins cp ON cp.id=ce.pluginID AND cp.active=1 AND cp.namespace=‚Core‘ WHERE ce.type=0 ORDER BY name, position; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 119.532036 Lock_time: 0.000139 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898905; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 90.747486 Lock_time: 0.000142 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898905; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 76.499092 Lock_time: 0.000111 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898905; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 78.890434 Lock_time: 0.000136 Rows_sent: 49 Rows_examined: 300 SET timestamp=1359898905; SELECT ce.subscribe as name, ce.listener, ce.position, cp.name as plugin FROM s_core_subscribes ce JOIN s_core_plugins cp ON cp.id=ce.pluginID AND cp.active=1 AND cp.namespace=‚Frontend‘ WHERE ce.type=0 ORDER BY name, position; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 157.641042 Lock_time: 0.000146 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898905; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 175.626854 Lock_time: 0.000063 Rows_sent: 31 Rows_examined: 300 SET timestamp=1359898905; SELECT ce.subscribe as name, ce.listener, ce.position, cp.name as plugin FROM s_core_subscribes ce JOIN s_core_plugins cp ON cp.id=ce.pluginID AND cp.active=1 AND cp.namespace=‚Core‘ WHERE ce.type=0 ORDER BY name, position; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 515.504939 Lock_time: 0.000404 Rows_sent: 0 Rows_examined: 354 SET timestamp=1359898905; SELECT fv.optionID AS id, COUNT(DISTINCT a.id) AS count, fo.id AS optionID, fo.name AS optionName, f.id AS groupID, f.name AS groupName, fv.value AS optionValue, fv.id AS valueID, st.objectdata AS optionNameTranslation, st2.objectdata AS groupNameTranslation, st3.objectdata AS valueTranslation FROM s_categories c, s_categories c2, s_articles_categories ac JOIN s_filter_articles fa ON fa.articleID=ac.articleID JOIN s_filter_values fv ON fv.id=fa.valueID JOIN s_filter_options fo ON fo.id=fv.optionID AND fo.filterable = 1 JOIN s_articles a ON a.id=ac.articleID AND a.active =1 AND a.changetime <= NOW() JOIN s_filter f ON f.id=a.filtergroupID LEFT JOIN s_filter_relations fr ON f.id = fr.groupId AND fo.id = fr.optionId LEFT JOIN s_articles_avoid_customergroups ag ON ag.articleID=fa.articleID AND ag.customergroupID=1 LEFT JOIN s_core_translations AS st ON st.objecttype=‚propertyoption‘ AND st.objectkey=fv.optionID AND st.objectlanguage=’’ LEFT JOIN s_core_translations AS st2 ON st2.objecttype=‚propertygroup‘ AND st2.objectkey=f.id AND st2.objectlanguage=’’ LEFT JOIN s_core_translations AS st3 ON st3.objecttype=‚propertyvalue‘ AND st3.objectkey=fv.id AND st3.objectlanguage=’’ INNER JOIN s_filter_articles fv1110 ON fv1110.articleID = a.id AND fv1110.valueID = 1110 INNER JOIN s_filter_articles fv1111 ON fv1111.articleID = a.id AND fv1111.valueID = 1111 INNER JOIN s_filter_articles fv1124 ON fv1124.articleID = a.id AND fv1124.valueID = 1124 INNER JOIN s_filter_articles fv1191 ON fv1191.articleID = a.id AND fv1191.valueID = 1191 INNER JOIN s_filter_articles fv1211 ON fv1211.articleID = a.id AND fv1211.valueID = 1211 INNER JOIN s_filter_articles fv1198 ON fv1198.articleID = a.id AND fv1198.valueID = 1198 INNER JOIN s_filter_articles fv1207 ON fv1207.articleID = a.id AND fv1207.valueID = 1207 INNER JOIN s_filter_articles fv1204 ON fv1204.articleID = a.id AND fv1204.valueID = 1204 WHERE c.id=3 AND c2.active=1 AND c2.left >= c.left AND c2.right <= c.right AND ac.articleID=a.id AND ac.categoryID=c2.id AND ag.articleID IS NULL GROUP BY fv.id ORDER BY fr.position, fo.name ASC, IF(f.sortmode=1, TRIM(REPLACE(fv.value,’,’,’.’))+0, 0), IF(f.sortmode=2, COUNT(*) , 0) DESC, IF(f.sortmode=3, fv.position, 0), fv.value; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 78.938739 Lock_time: 0.000142 Rows_sent: 6 Rows_examined: 66 SET timestamp=1359898905; SELECT ce.name, IFNULL(IFNULL(cv.value, cm.value), ce.value) as value FROM s_core_plugins p JOIN s_core_config_forms cf ON cf.plugin_id = p.id JOIN s_core_config_elements ce ON ce.form_id = cf.id LEFT JOIN s_core_config_values cv ON cv.element_id = ce.id AND cv.shop_id = ‚1‘ LEFT JOIN s_core_config_values cm ON cm.element_id = ce.id AND cm.shop_id = ‚1‘ WHERE p.name=‚TagCloud‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 125.867835 Lock_time: 0.000134 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898905; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 78.923369 Lock_time: 0.000087 Rows_sent: 4 Rows_examined: 64 SET timestamp=1359898905; SELECT ce.name, IFNULL(IFNULL(cv.value, cm.value), ce.value) as value FROM s_core_plugins p JOIN s_core_config_forms cf ON cf.plugin_id = p.id JOIN s_core_config_elements ce ON ce.form_id = cf.id LEFT JOIN s_core_config_values cv ON cv.element_id = ce.id AND cv.shop_id = ‚1‘ LEFT JOIN s_core_config_values cm ON cm.element_id = ce.id AND cm.shop_id = ‚1‘ WHERE p.name=‚InputFilter‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 175.649675 Lock_time: 0.000029 Rows_sent: 14 Rows_examined: 14 SET timestamp=1359898905; SELECT basename, basefile, inheritname, inheritfile FROM s_core_factory; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 175.382773 Lock_time: 0.000045 Rows_sent: 31 Rows_examined: 31 SET timestamp=1359898905; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Frontend‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 29.580411 Lock_time: 0.000157 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898905; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 78.919014 Lock_time: 0.000457 Rows_sent: 6 Rows_examined: 2154 SET timestamp=1359898905; SELECT (s0_.right - s0_.left - 1) / 2 AS sclr0, (SELECT COUNT(s1_.id) AS dctrn__1 FROM s_categories s2_ INNER JOIN s_articles_categories s3_ ON s2_.id = s3_.categoryID INNER JOIN s_articles s1_ ON s1_.id = s3_.articleID AND (s1_.active = 1) WHERE s2_.active = 1 AND (s2_.right <= s0_.right AND s2_.left >= s0_.left)) AS sclr1, s4_.id AS id2, s0_.id AS id3, s0_.parent AS parent4, s0_.description AS description5, s0_.position AS position6, s0_.left AS left7, s0_.level AS level8, s0_.right AS right9, s0_.metakeywords AS metakeywords10, s0_.metadescription AS metadescription11, s0_.cmsheadline AS cmsheadline12, s0_.cmstext AS cmstext13, s0_.active AS active14, s0_.template AS template15, s0_.blog AS blog16, s0_.showfiltergroups AS showfiltergroups17, s0_.external AS external18, s0_.hidefilter AS hidefilter19, s0_.hidetop AS hidetop20, s0_.noviewselect AS noviewselect21, s0_.changed AS changed22, s0_.added AS added23, s5_.id AS id24, s5_.albumID AS albumID25, s5_.name AS name26, s5_.description AS description27, s5_.path AS path28, s5_.type AS type29, s5_.extension AS extension30, s5_.userID AS userID31, s5_.created AS created32, s5_.file_size AS file_size33, s6_.id AS id34, s6_.categoryID AS categoryID35, s6_.attribute1 AS attribute136, s6_.attribute2 AS attribute237, s6_.attribute3 AS attribute338, s6_.attribute4 AS attribute439, s6_.attribute5 AS attribute540, s6_.attribute6 AS attribute641 FROM s_categories s0_ LEFT JOIN s_media s5_ ON s0_.mediaID = s5_.id LEFT JOIN s_categories_attributes s6_ ON s0_.id = s6_.categoryID LEFT JOIN s_categories_avoid_customergroups s7_ ON s0_.id = s7_.categoryID LEFT JOIN s_core_customergroups s4_ ON s4_.id = s7_.customergroupID AND (s4_.id = 1) WHERE s0_.active = 1 AND s0_.parent = 3 GROUP BY s0_.id HAVING (sclr1 > 0 OR s0_.external IS NOT NULL OR s0_.blog = 1) AND s4_.id IS NULL ORDER BY s0_.left ASC; # Time: 130203 14:41:46 # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 176.085163 Lock_time: 0.000050 Rows_sent: 49 Rows_examined: 300 SET timestamp=1359898906; SELECT ce.subscribe as name, ce.listener, ce.position, cp.name as plugin FROM s_core_subscribes ce JOIN s_core_plugins cp ON cp.id=ce.pluginID AND cp.active=1 AND cp.namespace=‚Frontend‘ WHERE ce.type=0 ORDER BY name, position; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 41.337718 Lock_time: 0.000188 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898906; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 33.647352 Lock_time: 0.000184 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898906; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 67.385674 Lock_time: 0.000147 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898906; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 41.274738 Lock_time: 0.000154 Rows_sent: 22 Rows_examined: 22 SET timestamp=1359898906; SELECT name, id, name, label, description, source, active, installation_date as installationDate, update_date as updateDate, changes, version FROM s_core_plugins WHERE namespace=‚Core‘; # User@Host: db1111111-db1[db1111111-db1] @ localhost # Query_time: 456.553975 Lock_time: 0.000568 Rows_sent: 0 Rows_examined: 354 SET timestamp=1359898906; SELECT fv.optionID AS id, COUNT(DISTINCT a.id) AS count, fo.id AS optionID, fo.name AS optionName, f.id AS groupID, f.name AS groupName, fv.value AS optionValue, fv.id AS valueID, st.objectdata AS optionNameTranslation, st2.objectdata AS groupNameTranslation, st3.objectdata AS valueTranslation FROM s_categories c, s_categories c2, s_articles_categories ac JOIN s_filter_articles fa ON fa.articleID=ac.articleID JOIN s_filter_values fv ON fv.id=fa.valueID JOIN s_filter_options fo ON fo.id=fv.optionID AND fo.filterable = 1 JOIN s_articles a ON a.id=ac.articleID AND a.active =1 AND a.changetime <= NOW() JOIN s_filter f ON f.id=a.filtergroupID LEFT JOIN s_filter_relations fr ON f.id = fr.groupId AND fo.id = fr.optionId LEFT JOIN s_articles_avoid_customergroups ag ON ag.articleID=fa.articleID AND ag.customergroupID=1 LEFT JOIN s_core_translations AS st ON st.objecttype=‚propertyoption‘ AND st.objectkey=fv.optionID AND st.objectlanguage=’’ LEFT JOIN s_core_translations AS st2 ON st2.objecttype=‚propertygroup‘ AND st2.objectkey=f.id AND st2.objectlanguage=’’ LEFT JOIN s_core_translations AS st3 ON st3.objecttype=‚propertyvalue‘ AND st3.objectkey=fv.id AND st3.objectlanguage=’’ INNER JOIN s_filter_articles fv1110 ON fv1110.articleID = a.id AND fv1110.valueID = 1110 INNER JOIN s_filter_articles fv1120 ON fv1120.articleID = a.id AND fv1120.valueID = 1120 INNER JOIN s_filter_articles fv1131 ON fv1131.articleID = a.id AND fv1131.valueID = 1131 INNER JOIN s_filter_articles fv1193 ON fv1193.articleID = a.id AND fv1193.valueID = 1193 INNER JOIN s_filter_articles fv1192 ON fv1192.articleID = a.id AND fv1192.valueID = 1192 INNER JOIN s_filter_articles fv1199 ON fv1199.articleID = a.id AND fv1199.valueID = 1199 INNER JOIN s_filter_articles fv1202 ON fv1202.articleID = a.id AND fv1202.valueID = 1202 INNER JOIN s_filter_articles fv596 ON fv596.articleID = a.id AND fv596.valueID = 596 WHERE c.id=3 AND c2.active=1 AND c2.left >= c.left AND c2.right <= c.right AND ac.articleID=a.id AND ac.categoryID=c2.id AND ag.articleID IS NULL GROUP BY fv.id ORDER BY fr.position, fo.name ASC, IF(f.sortmode=1, TRIM(REPLACE(fv.value,’,’,’.’))+0, 0), IF(f.sortmode=2, COUNT(*) , 0) DESC, IF(f.sortmode=3, fv.position, 0), fv.value; [/quote]

2

Hi, das Thema Performace steht bei uns ganz oben auf der Roadmap. In diesem Bereich wird es auf Dauer immer weitere Verbesserungen geben. Bei deinem Problem könnte vielleicht schon eine MySQL Option helfen. Die Option „optimizer_search_depth“ müsste auf „0“ gesetzt werden. Die Option müsste vom Hoster angepasst werden. Diese Option zu deaktivieren ist immer dann hilfreich, wenn Filter-Queriues Probleme verursachen. Dies scheint in deinem Fall zu sein. Wir würden uns das Problem auch gerne direkt bei dir anschauen. Vielleicht können wir Änderungen mit in die 4.0.7 übernehmen. Falls du möchtest, dann schick mir die Daten doch am besten an entwicklung@shopware.de. Also komplette Zugangsdaten (Backend, FTP, phpMyAdmin) Grüße aus dem Münsterland Patrick

3

Hi, das Angebot nehme ich dankend an (Mail geht in den nächsten Minuten raus)… Ich habe den HE-Support gebeten die Funktion auf 0 zu setzen, allerdings kommen die wohl erst Morgen dazu…:x…bis dahin verbläst es mir den Server so im Schnitt nach 10 min. von Neustart gerechnet… Ich habe in den Logs viele, ausschließlich vom BingBot angefragte (die robots.txt interessiert den nicht), GET Zugriffe auf [quote]/Deutsch_cat_3.html?sFilterProperties=…[/quote] Der Googlebot wiederum interessiert sich überhaupt nicht für die sFilterProperties… Könnte die o.g. Abfrage in größeren Mengen (wie gesagt der BingBot stürzt sich regelrecht auf die „GET /Deutsch_cat_3.html?sFilterProperties=…“) dazu führen das sich die DB beim Versuch den optimalen execution plan für die Anfragen zu berechnen (Standardwert für optimizer_search_depth soll irgendwas um die 60 sein) selbst wegschießt?

4

geh doch auf bing, melde dort deine seite an und probiere diesen pfad auszuschliessen? vielleicht rettet dich das ja über die nacht :slight_smile:

5

@leseaw …habe ich schon vor ca. 2-3 Wochen gemacht…:(…volles Programm inkl. Zeitplan wann der BingBot crawlen „darf“, wie schnell und inkl. der Parameter die er ignorieren (sFilterProperties z.B.) soll… Da der Bingbot (in der Theorie zumindest) die Angaben in der robots.txt zu den Bing Webmastertools priorisiert habe ich in der robots.txt auch nochmal die crawl delay etc. drin… Problem hierbei…das interessiert den buckligen Crawler offensichtlich überhaupt nicht…:-(…von den ca. 40 IPs die der BingBot hat halten sich vll. 10 daran…der Rest der Bande macht was er will…(–> die die sich nicht dran halten kicke ich per .htaccess…ist aber ein Sch***spiel und die Liste ist mittlerweile lang)…

6

@Porter Hat sich denn mittlerweile was getan mit deinem slowquery??? Dieses Eigenschaften Problem in Verbindung mit elenden Ladezeiten macht mir nämlich auch das leben zur hölle mfg robert

7

Hi, ich habe es jetzt zumindest eingrenzen können. Wenn ich an meinen Shop eine Anfrage á la [quote]www.meinedomain/Deutsch_cat_3.html?sFilterProperties=1109%7C1112%7C1124%7C1191%7C1200%7C1209%7C1215%7C1203[/quote] stelle braucht mein Shop (der ansonsten wirklich gute Ladezeiten hat) im Schnitt satte (mit Stoppuhr gemessen) 40 Sekunden um dann folgende URL auszuliefern [quote]http://www.meinedomain.com/listing/?f=1109|1112|1124|1191|1200|1209|1215|1203[/quote] Da sich der BingBot (Googlebot ignoriert die „sFilterProperties“) wie blöd im Sekundentakt (tlw. mit 5-6 IP´s gleichzeitig) auf die sFilterProperties stürzt haut´s mir dann recht zeitnah den Server vom Stängele weil die Last hochschießt… Einzigste Abhilfe bisher ist das ich den BingBot konsequent per .htaccess ausschließe was aber keine Lösung sein kann… @Shopware Ich habe in meinem Shop Artikeleigenschaften hinterlegt, die stehen aber alle bei „filterbar“ auf inaktiv, d.h. im ganzen Shop kann man eigentlich nirgends „filtern“ (bis auf den Hersteller)… Ich verstehe nicht wie der Bingbot überhaupt zu den sFilterPropertie Anfragen kommt und hätte jetzt gedacht das der Shop die Anfrage gar nicht erst ausliefert (da ja auf inaktiv) und dann gleich auf die Startseite umleitet?!? Desweiteren steht auch bei den SEO´s die sFilterPropertie als „Nofollow Query“ drin, d.h. eigentlich dürfte der Bot dem Link doch gar nicht folgen? Habe ich da etwas falsch konfiguriert?

8

Handelt es sich um einen mehrsprachigen Shop?

9

Hi, [quote]Handelt es sich um einen mehrsprachigen Shop?[/quote] Nein, Shop steht komplett auf “Deutsch”…(unter Shopeinstellungen --> Shops habe ich nur einen Eintrag)…bei Lokalisierungen habe ich noch Englisch…das kann ich aber nicht löschen…

10

Ok, bei der Mehrsprachigkeit wird das entsprechende SQL sehr komplex - wenn aber nur die deutsche Sprache verwendet wird, so dürfte dies nicht das Problem sein. Mein Tipp wäre, einen SQL-Profi auf den Server schauen zu lassen - oft kann man durch Anpassungen der MySQL-Konfiguration oder im Aufbau der SQL-Befehle noch etwas retten. Viel Erfolg! EDIT: Ich lese gerade, dass Sie die Filter gar nicht nutzen wollen? In diesem Fall könnten Sie die den GET-Parameter „sFilterProperties“ testweise in der shopware.php auf NULL setzen - vielleicht löst sich das Problem dadurch schon.