I think there’s no need to write lengthy paragraphs about the disadvantages of a slow website performance, but let me mention just a few issues that I feel important. Apart from the fact that user experience declines seriously while visiting a slow website, Google takes into account page load speeds and ranks lower those pages that perform poorly. In the case of online stores, it can be disastrous since your competitors will be ranked higher.

According to official statistics, even if visitors find your store, every extra second they have to wait will reduce the chance of conversion by 7%. You can measure page speed with external tools that give suggestions for reaching the optimal solution. Such websites include:

Our favourite is is New Relic , a software analytics tool with which you can always see the speed performance of your website and many other useful attributes as well. These solutions can help a great deal in detecting slow speeds, however, they do not see the faulty processes within Magento. In the following I will write about how to examine these inner processes and how to detect and fix them.

Detection

Default profiler

You can use a default profiler for measuring the speed of internal processes. Thanks to the profiler, you can get an insight into the speed performance and memory usage of the controllers, actions, blocks, observers and other events. To use this, you need to authorize profiling in the System / Configuration / Developer / Debug / Profiler section. If you run speed analytics on a public page, you need to define your computer’s IP address in the System / Configuration / Developer / Developer Client Restriction. Next, you need to remove the comment in the index.php file from the line preceding this:

Varien_Profiler::enable();

Varien_Profiler::start('unique_profile_identifier'); //... the code lines to be analysed Varien_Profiler::stop('unique_profile_identifier');

<default_setup> <connection> <host><![CDATA[localhost]]></host> <username><![CDATA[mage_user]]></username> <password><![CDATA[mage_password]]></password> <dbname><![CDATA[mage_db]]></dbname> <initStatements><![CDATA[SET NAMES utf8]]></initStatements> <model><![CDATA[mysql4]]></model> <type><![CDATA[pdo_mysql]]></type> <pdoType><![CDATA[]]></pdoType> <active>1</active> <profiler>1</profiler> </connection> </default_setup>

The result will look similar to this:In your own modules you can use the profiler option by inserting these lines:For profiling the SQL queries you need to make your settings in the app/etc/local.xml:

The table below shows what the result of these settings will look like:

Aoe Profiler

Let’s be honest, it is quite hard to read and to see how these data are structured. To solve this you can use the Aoe Profiler plugin, which displays the data in a hierarchical structure with the help of small diagrams. These show you clearly those elements that spoil the speed performance of the website.

However, here at AionHill, we use an even more competent solution, our own module that helps us detect page speed problems more effectively.

Introducing AionHill_Profiler

Blocks

The blocks are shown in a hieararchical structure indicating the time (seconds) needed for displaying them, whether the given block uses cache, and we can also see the SQL queries that are run while displaying the block.

SQL queries

The module also displays the SQL queries used on the page. Here we also use diagrams, time figures and stack trace figures that show the line and Magento class from which the SQL query started.

Repetitive SQL queries

The module notifies us if there are completely identical SQL queries on the page. The table below shows how many of them are present and how frequently they occur and also how much of the MySQL server’s time was consumed.

Cycle-structured SQL queries

Finally, we detect the cycle-structured, but not necessarily identical, SQL queries:

Solution proposals

And now let’s see some real-life examples revealing what we can do for tackling the issues that have been detected.

Eliminate the cycle-structured SQL queries.

No matter how fast the SQL server is, using its capacity unnecessarily still influences its performance. Download the needed data in one bulk and not in cycles one by one, whenever possible. As examples, I show you two functions that return with the average price based on product identifiers set as parameters. The first method, which is wrong, loads in the products in one cycle one by one and then adds the price to one bulk, from which it calculates the average price out of the cycle, and then returns with it.

/** * get Average Price (bad example) * * @param array $productIds product ids * * @return float */ public function getAveragePriceBadMethod(array $productIds) { $prices = array(); foreach ($productIds as $productId) { $product = Mage::getModel('catalog/product')->load($productId); $prices[] = $product->getPrice(); } return array_sum($prices) / count($prices); }

An example for a fine solution: Instead of making a query for each product separately, we make a query for the whole collection containing them and then we use these items.

/** * get Average Price (good example) * * @param array $productIds product ids * * @return float */ public function getAveragePriceGoodMethod(array $productIds) { if (empty($productIds)) { return 0; } $prices = array(); $products = Mage::getResourceModel('catalog/product_collection') ->addAttributeToSelect('price') ->addAttributeToFilter('entity_id', array('in' => $productIds)); foreach ($products as $product) { $prices[] = $product->getPrice(); } return array_sum($prices) / count($prices); }

Indeed, it is still not the best approach because we need the prices only, so it is not necessary to load the whole collection. When only the values of one field are required, use the following method:

/** * get Average Price (good example) * * @param array $productIds product ids * * @return float */ public function getAveragePrice(array $productIds) { if (empty($productIds)) { return 0; } $products = Mage::getResourceModel('catalog/product_collection') ->addAttributeToSelect('price') ->addAttributeToFilter('entity_id', array('in' => $productIds)); $select = $products->getSelect() ->reset(Zend_Db_Select::COLUMNS) ->columns('price'); $prices = $products->getConnection()->fetchCol($select); return array_sum($prices) / count($prices); }

It is also a usual problem that a second query is initiated when the product is already in the shopping cart. The quote model ensures that the items related products are already present, so there is no need for subsequent model loads.

/** * get Quote Weight (bad example) * * @return float */ public function getQuoteWeightBadExample() { $quoteItems = Mage::getSingleton('checkout/cart')->getQuote()->getAllItems(); $quoteWeight = 0; /** @var Mage_Sales_Model_Quote_Item $quoteItem */ foreach ($quoteItems as $quoteItem) { $product = Mage::getModel('catalog/product')->load($quoteItem->getProductId()); $quoteWeight += $product->getWeight() * $quoteItem->getQty(); } return $quoteWeight; } /** * get Quote Weight (good example) * * @return float */ public function getQuoteWeight() { $quoteItems = Mage::getSingleton('checkout/cart')->getQuote()->getAllItems(); $quoteWeight = 0; /** @var Mage_Sales_Model_Quote_Item $quoteItem */ foreach ($quoteItems as $quoteItem) { $quoteWeight += $quoteItem->getProduct()->getWeight() * $quoteItem->getQty(); } return $quoteWeight; }

Eliminate recurring SQL queries

Of course, there are justifiable cases when we need to repeat the same query, e.g. reloading after modification for checking purposes. But many times there are planning or developing errors in the background. Let’s see what the most common mistakes are. We don’t store the return value of a method that is used several times:

/** * get Feature Categories (bad example) * * @return Mage_Catalog_Model_Resource_Category_Collection * @throws Mage_Core_Exception */ public function getFeatureCategoriesBadExample() { $categories = Mage::getModel('catalog/category')->getCollection() ->addAttributeToSelect('*') ->addAttributeToFilter('name', array('like' => '%feature%')) ->load(); return $categories; }

If we use the same method in 10 different places on a single page, then we make 9 unnecessary queries using the MySQL server! So it is wise to store the results in a class variable when calling the method the first time and later use the stored items without using extra resources.

/** * Local cache for feature categories * * @var null|Mage_Catalog_Model_Resource_Category_Collection */ protected $_featureCategories = null; /** * get Feature Categories (good example) * * @return Mage_Catalog_Model_Resource_Category_Collection * @throws Mage_Core_Exception */ public function getFeatureCategories() { if (!is_null($this->_featureCategories)) { return $this->_featureCategories; } $this->_featureCategories = Mage::getModel('catalog/category')->getCollection() ->addAttributeToSelect('*') ->addAttributeToFilter('name', array('like' => '%feature%')) ->load(); return $this->_featureCategories; }

Another common mistake is using model instead of singleton. It can cause performance problems right away that a class is present in multiple copies instead of one, but if more complex procedures are run, the situation can get much graver. In the following example you can see an extended shopping cart. I inserted a category collection load in its constructor.

/** * Class My_Module_Model_Checkout_Cart */ class My_Module_Model_Checkout_Cart extends Mage_Checkout_Model_Cart { /** @var Mage_Catalog_Model_Resource_Category_Collection */ protected $_quoteCategories; /** * Constructor */ public function __construct() { parent::__construct(); $categoryIds = array(); $quoteItems = $this->getQuote()->getAllItems(); /** @var Mage_Sales_Model_Quote_Item $quoteItem */ foreach ($quoteItems as $quoteItem) { $product = $quoteItem->getProduct(); $categoryIds = array_merge($categoryIds, $product->getCategoryIds()); } $this->_quoteCategories = Mage::getModel('catalog/category')->getCollection() ->addAttributeToSelect('*') ->addAttributeToFilter('entity_id', array('in' => array_unique($categoryIds))) ->load(); } }

It can work fine if we handle this extended class properly.

// bad example $productIds = Mage::getModel('my_module/checkout_cart')->getProductIds(); $itemsQty = Mage::getModel('my_module/checkout_cart')->getItemsQty(); // good example $productIds = Mage::getSingleton('my_module/checkout_cart')->getProductIds(); $itemsQty = Mage::getSingleton('my_module/checkout_cart')->getItemsQty();

In the above example, wrongly, the class is present in more copies and thus the category query in the constructor will run in each case. The situation is the same if there are resource-demanding processes with different methods. Here, even if we use one class variable for caching, like in the previous example, the time consuming code lines are executed repeatedly since we have stored the previous calculations in another copy of the class. In the example below, which gives the correct solution, the object is present in one copy only and therefore there won’t be any unnecessary calculations. If, for some reason, you cannot use singleton, you can also use Magento Helpers, which are singleton classes, or Mage::registry for storing temporary data. These are very simple practices, but if you do not pay enough attention to them, the number of SQL queries may grow significantly.

Fixing long runtime SQL queries

Creating appropriate table indexes

Many times it well may be that the corresponding fields of a given table are not indexed. Here caution is needed because the more indexes you use, the longer the writing time will be, but searches and ordering will be considerably faster. It is very important to define the structure of the table and the indexes optimally. You can add indexes to the tables with the help of the installer integrated in the module.

$installer = $this; $installer->startSetup(); $tableName = $installer->getTable('my_module/model'); if ($installer->getConnection()->isTableExists($tableName)) { $table = $installer->getConnection(); try { $table->addIndex( $installer->getIdxName( 'my_module/model', array( 'column1', 'column2', ), Varien_Db_Adapter_Interface::INDEX_TYPE_INDEX ), array( 'column1', 'column2', ), array('type' => Varien_Db_Adapter_Interface::INDEX_TYPE_INDEX) ); } catch (Exception $e) { Mage::logException($e); } } $installer->endSetup();

Extending indexing of the product flat tables

When there are many products, queries executed from product flat tables may be slower if you use filtering or ordering which is not field-indexed by Magento. You cannot index flat tables using the installer since Magento discards and re-creates these during indexing. However, you can modify the default indexes of the flat table with an observer. To make it work, you need to add an observer to the catalog_product_add_indexes event.

<events> <catalog_product_flat_prepare_indexes> <observers> <my_module_catalog_product_flat_prepare_indexes> <type>singleton</type> <class>my_module/observer</class> <method>catalogProductFlatPrepareIndexes</method> </my_module_catalog_product_flat_prepare_indexes> </observers> </catalog_product_flat_prepare_indexes> </events>

/** * Add indexes to product flat table * * @param Varien_Event_Observer $observer observer * * @return void */ public function catalogProductFlatPrepareIndexes(Varien_Event_Observer $observer) { /** @var Varien_Object $indexesObject */ $indexesObject = $observer->getIndexes(); /** @var array $indexes */ $indexes = $indexesObject->getIndexes(); $indexes['IDX_MY_ATTRIBUTE'] = array( 'type' => Varien_Db_Adapter_Interface::INDEX_TYPE_INDEX, 'fields' => array('my_attribute') ); $indexesObject->setIndexes($indexes); }

The method above is always run when Magento re-creates the flat table due to the re-indexing process.

Eliminating resource-demanding SQL joins

In some cases, a slow-speed query cannot be fixed with using indexes only because we connect several large tables and therefore, inevitably, the MySQL server has to deal with huge amounts of data. Let’s suppose we would like to execute an ordering on the product list page based on inventory volume and rating. In this case we apply the following method:

$collection->joinField( 'quantity', 'cataloginventory/stock_item', 'qty', 'product_id=entity_id', '{{table}}.stock_id=1', 'left' ); $collection->joinField( 'rating_summary', 'review_entity_summary', 'rating_summary', 'entity_pk_value=entity_id', array( 'entity_type' => 1, 'store_id' => Mage::app()->getStore()->getId() ), 'left' ); $collection->setOrder($attribute, $direction);

Depending on the number of products and ratings, immense amounts of data can stack up and structuring these can take up a considerable amount of time. A great number of simple methods can be used in terms of My SQL queries. Now I’d like to mention that join is not always needed, only in those cases when we’d really use it.

if ($attribute == 'quantity') { $collection->joinField( 'quantity', 'cataloginventory/stock_item', 'qty', 'product_id=entity_id', '{{table}}.stock_id=1', 'left' ); } if ($attribute == 'rating_summary') { $collection->joinField( 'rating_summary', 'review_entity_summary', 'rating_summary', 'entity_pk_value=entity_id', array( 'entity_type' => 1, 'store_id' => Mage::app()->getStore()->getId() ), 'left' ); } $collection->setOrder($attribute, $direction);

With this simple trick we prevented connecting two large tables to the product collection. Now, connecting takes place only in the case of such tables that are truly needed.

Performance improvement of Magento Blocks

Whenever possible, it is recommended to use caching of Magento blocks. You can segment these cache data based on user groups and can also combine more segmentations. .

/** * construct * * @return void */ protected function _construct() { $this->addData( array( 'cache_lifetime' => 3600, 'cache_key' => 'MY_MODULE_' . $this->getExampleModel()->getId(), 'cache_tags' => array(My_Module_Model_Example::CACHE_TAG) ) ); }

It’s worth using the so-called object cache for those methods that are called several times and it is not always needed to run the codes within them.

/** * get Category Collection * * @return Mage_Catalog_Model_Resource_Category_Collection|mixed * @throws Mage_Core_Exception */ public function getCategoryCollection() { if ($this->hasData('category_collection')) { return $this->getData('category_collection'); } $collection = Mage::getModel('catalog/category')->getCollection() ->addAttributeToSelect('*') ->addAttributeToFilter('parent_id', array('eq' => Mage::app()->getStore()->getRootCategoryId())); $this->setData('category_collection', $collection); return $collection; }

Other useful development suggestions for better performance

Simple SQL queries

If you want to collect identifiers from a collection, it is better to solve this without a cycle:

// bad example $ids = array(); $products = Mage::getModel('catalog/product')->getCollection() ->addAttributeToFilter('sku', array('like' => 'test-%')); foreach ($products as $product) { $ids[] = $product->getId(); } // good example $ids = Mage::getModel('catalog/product')->getCollection() ->addAttributeToFilter('sku', array('like' => 'test-%')) ->getAllIds();

The getAllIds method is included in every Magento collection. If it is not the identifiers that you need, but another field, and that one only, then you can apply the following solution:

// bad example $result = array(); $products = Mage::getModel('catalog/product')->getCollection() ->addAttributeToSelect('my_attribute') ->addAttributeToFilter('sku', array('like' => 'test-%')); foreach ($products as $product) { $result[] = $product->getData('my_attribute'); } // good example $collection = Mage::getResourceModel('catalog/product_collection') ->addAttributeToSelect('test') ->addAttributeToFilter('sku', array('like' => 'test-%')); $select = $collection->getSelect() ->reset(Zend_Db_Select::COLUMNS) ->columns('test') ->group('test'); $result = $collection->getConnection()->fetchCol($select);

If you just want to check if a value exists in the table:

// bad example $firstItem = Mage::getModel('catalog/product')->getCollection() ->addAttributeToFilter('hello', array('gt' => 3)) ->getFirstItem(); $hasData = $firstItem->getId() != null; // good example $size = Mage::getResourceModel('catalog/product_collection') ->addAttributeToFilter('hello', array('gt' => 3)) ->getSize(); $hasData = $size > 0;

Simplify whenever possible

Again, simple things, but they can help a lot with shortening runtimes and having shorter codes also makes life easier. For example, if you need only the identifier of the logged-in user:

// less effective $customerId = Mage::getSingleton('customer/session')->getCustomer()->getId(); // a little shorter $customerId = Mage::getSingleton('customer/session')->getCustomerId();

Similarly, the products in the shopping cart and their identifiers are handled as follows:

$quoteItems = Mage::getSingleton('checkout/cart')->getQuote()->getAllItems(); foreach ($quoteItems as $item) { // when only product ID is needed // it's a little longer $productId = $item->getProduct()->getId(); // more effective $productId = $item->getProductId(); // if the product is needed // this is a really bad solution $product = Mage::getModel('catalog/product')->load($item->getProductId()); // this is the right solution $product = $item->getProduct(); }