Technology

Optimizing Magento series – part 1: Flat tables

Posted by Pixafy Team

Optimize Magento Series (Part 1): Flat Tables  |  Pixafy.com

In a previous article, I talked a little bit about Magento full-page cache (FPC), how it behaves and functions, and how to overcome some of the nuances you get when enabling it. It’s no question that full-page caching is crucial to any Magento store under high traffic and ultimately, focusing on getting it to work should be top priority for optimizing any Magento store.

This tutorial series is going to build upon some of the topics discussed earlier and touch on other popular ways to speed up Magento – I hope to cover topics such as block caching, indexing, and server caching theories. In this article we’re going to cover what flat tables are and how to use them in an existing Magento store.

What are flat tables?

Let’s assume you already have a store setup on your development environment. The base store contains some categories, a few products associated with each category, and a couple of happy customers. By default, Magento disables the use of flat tables. The important thing to understand is how flat tables work and what might have to change in the code to make them work.

Flat tables are essentially one big table of products or categories in a given store including all attributes and fields associated with that product or category, respectively. If you remember how EAV works, Magento stores all of a product’s data across a multitude of tables – this is extremely efficient when sticking to an agile development structure but can be quite slow if put into a production environment. When flat tables are enabled, Magento will continue to store a product’s attributes as EAV so the store must be re-indexed in order for changes to appear on the front-end. Every store in Magento has it’s own flat table of products.

Since Magento is referring to a different table in the database for categories and products, it’s safe to assume that a different resource model is being used to fetch the information. In the core source code, we can refer to the file app/code/core/Mage/Catalog/Model/Category.php

protected function _construct()

{

if (Mage::helper('catalog/category_flat')->isEnabled()) {

$this->_init('catalog/category_flat');

$this->_useFlatResource = true;

} else {

$this->_init('catalog/category');

}

}

Upon instantiation, the category model is telling Magento that it will be using a different resource to fetch information. We’re actually going to use the model Mage_Catalog_Model_Resource_Category_Flat_Collection in place of Mage_Catalog_Model_Resource_Category_Collection. The product collection is slightly different although the concept is the same. You can read more about it here, but in essence, the product resource model changes to Mage_Catalog_Model_Resource_Product_Flat while in flat mode as opposed to Mage_Catalog_Model_Resource_Product.

In an ideal world, we developers shouldn’t care whether or not our store is being run in flat mode or not, but the reality is that we must care. Since we’re using two new models to fetch data, some of the methods and functionality available without flat mode are not made available to us with flat mode enabled.

Flat table differences and nuances

Let’s dive into a working example. We’re going to work with a custom module that outputs all categories in a list and subsequently lists each product associated with that category. While simple in nature, this is the perfect example to look at how we will be handling the change from EAV calls to flat table calls. You can download the module here:

download-icon  [DOWNLOAD] Pixafy Module: CustomCatalog.zip

Let’s run a few tests. Install the module above to your test store and open it up in a web browser. Navigate to customcatalog/catalog/view. You should see a list of all categories and associated products in your store. Login to the backend and navigate to System > Configuration > Catalog > Frontend. There are two settings that are interesting to us: Use Flat Catalog Category and Use Flat Catalog Product. Go ahead and change both options to “Yes” and save.

For our test, clear the cache but DO NOT index yet. Navigate back to customcatalog/catalog/view and notice what happened. We only see two categories, “Root Catalog” and “Default Category”. This is good! Since we haven’t indexed our store yet, Magento has no knowledge of what categories or products are associated with this store. If you observe your database tables you will notice two new tables called catalog_category_flat_store_1 and catalog_product_flat_1, respectively representing the categories and products where store_id would equal 1. Looking at the structure of catalog_product_flat_1 you will notice all the product attributes neatly structured into a single table.

Go ahead and re-index your store and refresh the front-end page. The results should look exactly as they were before when flat catalog was disabled.

So when do we have to worry about changing our code? The answer is not all that complex, but the theory as to why is a whole different situation. The answer lies within Mage_Catalog_Model_ResourceProduct_Flat and Mage_Catalog_Model_Resource_Category_Flat_Collection.

Looking at the models shows all the different methods we can use when calling a catalog model or collection. These differ slightly from those used in Mage_Catalog_Model_Resource_Category_Collection and Mage_Catalog_Model_Resource_Product which means that we need to take a little precaution before making the switch to flat tables.

Looking at the differences between the files (and with some experimentation), I found a few functions that differ between the two modes.

Catalog/category collection

Function Name Flat Catalog Enabled Flat Disabled
setLoadProductCount($flag) No Yes
setProductStoreId($storeId) No Yes
addParentPathFilter($parent) Yes No
addStoreFilter() Yes No
addSortedField($sorted) Yes No

If your code contains one or more of these functions, you should put in place a check or condition for flat mode enabled to avoid complications arising. You can use the example condition given below:

if (Mage::helper('catalog/category_flat')->isEnabled()) {

// Using flat tables

} else {

// Not using flat tables

}

Flat table benchmarking

So how much does “flat” mode improve Magento speed? The answer is (not surprisingly) quite a bit! The quick tests below prove that by using flat tables in Magento, we can improve our store’s speed by an average of 50 ms (using a local development machine) – and that’s just with a few products for a single customer!

customcatalog/catalog/view

  • 3 products, 3 categories, full-page cache off
Flat Mode Run 1 Run 2 Run 3 Run 4 Run 5 Run 6 Run 7 Run 8 Run 9 Run 10 Avg.
On 952 ms 941 ms 970 ms 971 ms 955 ms 958 ms 975 ms 978 ms 937 ms 943 ms 955.3 ms
Off 1050 ms 1040 ms 1050 ms 1010 ms 1030 ms 1000 ms 990 ms 1010 ms 1040 ms 1010 ms 1023.0 ms


catalog/category/view/id/3

  • 2 products, single category, full-page cache off
Flat Mode Run 1 Run 2 Run 3 Run 4 Run 5 Run 6 Run 7 Run 8 Run 9 Run 10 Avg.
On 1020 ms 1030 ms 1070 ms 997 ms 1060 ms 999 ms 1030 ms 1060 ms 998 ms 1010 ms 1027.4 ms
Off 1090 ms 1050 ms 1090 ms 1060 ms 1080 ms 1040 ms 1040 ms 1040 ms 1080 ms 1020 ms 1059.0 ms

Wrapping up

Why run these tests with full-page cache off? Well, we want to actually see how much more data Magento is processing. If we enable full-page cache, we’re essentially storing all the data Magento loaded earlier and therefore the backend isn’t really doing much work anymore. If you run the test on catalog/category/view/id/3 with FPC on vs. off, the results are practically the same whether the flat catalog is enabled or not (my development environment got around 21 ms average).

I hope this helps anyone struggling to get flat catalog enabled. If you spend the time to write checks for yourself and account for all the different ways Magento can be configured, you’ll spend more time actually writing modules and less time debugging existing ones.

As always, I welcome any feedback, questions, or comments you may have. Happy hacking!

Questions or comments on Magento? Share them below, or tweet us @Pixafy