27

Yes, flat tables are still a thing. You can enable flat tables for products and categories from stores->configuration->catalog->storefront. Why do we need flat tables? They do offer significant performance boost for large catalog data. One of our client has only 5 categories and around 250 products so it doesn't really matter if you have flat on or off. It ...


20

You need to enable visible_on_front to push into the Catalog Flat tables. Category flat tables are affected by used_in_product_listing. As for the limit in MySQL I believe that is a separate question and should be migrated out as a separate Q&A.


13

What version of Magento is this? These are my results for Magento 1.9: Flat catalog enabled: Flat catalog is indexed: Some data set in a specific store view: Code used: <?php require_once 'app/Mage.php'; Mage::app('admin'); $collection = Mage::getResourceModel('catalog/product_collection') ->addAttributeToSelect('*') ...


13

take a look at this method Mage_Catalog_Model_Resource_Product_Collection::_construct protected function _construct() { if ($this->isEnabledFlat()) { $this->_init('catalog/product', 'catalog/product_flat'); } else { $this->_init('catalog/product'); } $this->_initTables(); } this means that the collection ...


12

If flat catalog is enabled, then in any product list the flat tables will be used. The flat tables are generated one per store view when rebuilding the flat index. They contain all the attributes marked as 'Used in product listing', one column for each attribute. This is a big boost in performance because instead of a lot of joins to get the product ...


11

Only the attributes that are marked to be used in product listing are copyed to the flat tables. Mark it like this and reindex everything.


10

So I consider these to be two bugs in Magento. First one is the fact that you can't set store ID on catalog/product collection. And the second is that you absolutely can't get resource model as non-singleton. So stupid workaround is to instantiate model twice. The first time the store ID can be set and the second instantiation will use it: Mage::...


10

If you have PHPMyAdmin installed on your server you can take a look at Magento's tables. You'll see that there are a lot starting with catalog_product_... and catalog_category_.... All these are used for each product and category so that means a lot of complicated and heavy queries for every page you call. To lighten the load on MySQL and speed up your ...


9

Starting with Magento CE 1.8 the flat table indexes are only shown on the Index Management page if flat tables are enabled. Enable flat tables in System > Configuration > Catalog > Catalog > Frontend: Enjoy the flat table indexes in System > Index Management:


9

The object which is responsible to determine if flat index is available (class Magento\Catalog\Model\Indexer\Product\Flat\State) is an immutable shared instance. But it is possible to use our own instance, using virtual types. This is my di.xml: <virtualType name="disabledFlatStateProductCollectionFactory" type="Magento\Catalog\Model\ResourceModel\...


8

$_category = Mage::getModel('catalog/category')->load(CATEGORY-ID); $_categories = $_category ->getCollection() ->addAttributeToSelect(array('name', 'image', 'description')) ->addIdFilter($_category->getChildren()); foreach ($_categories as $_category): echo $...


7

Based on magento documentation for flat table mentioned in https://docs.magento.com/m2/ce/user_guide/catalog/catalog-flat.html Starting with Magento 2.3.0+, the use of a flat catalog is no longer a best practice and is not recommended. Continued use of this feature is known to cause performance degradation and other indexing issues


6

This should work. $categoryId = '46'; //replace with your own $category = Mage::getModel('catalog/category')->load($categoryId); $positions = Mage::getResourceModel('catalog/category')->getProductsPosition($category); This ignores the 'Flat categories' flag. It goes directly to the category resource model. You can even skip the category loading for ...


6

It is not depending on what your rows really hold for data but what is possible. According to official MySQL documentation https://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html The maximum row size constrains the number (and possibly size) of columns because the total length of all columns cannot exceed this size. For example, utf8 characters ...


6

I think Below code solve your concern $collection = Mage::getModel('catalog/category') ->load($categoryId) ->getProductCollection(); After that you can put your filters and all which attribute to load and all


6

When a product collection is loaded, the fact that it uses EAV or flat tables is determined by this result \Magento\Catalog\Model\ResourceModel\Product\Collection::isEnabledFlat(). You can write an around or after plugin, that returns false if you are in the context of a certain store view. Or even better, the values for flat flag are stored (cached) in ...


5

Another way to add attributes to flat tables is to set them in your config.xml frontend section ... <product> <collection> <attributes> <attribute_code/> </attributes> </collection> </product>


5

You can use getChildren to get child category data in your block even Use Flat Catalog Category is enabled. For example namespace Custom\Module\Block\World; class Index extends \Magento\Framework\View\Element\Template { protected $_categoryFactory; protected $_category; protected $_categoryHelper; protected $_categoryRepository; public ...


5

There are 2 data models in magento: eav (default) and flat. EAV is a very angile model it allows user to add custom attributes as many is he likes, but the problem is that it stores the data in many tables and in order to get the data your query will have many JOINs which is a bad performance. Flat model takes the attributes you have created with system ...


4

You are probably doing this in the frontend or at least not in the admin. The flat categories are not to be written to, other then the indexer, for exactly the reason you are now seeing. The flat tables are a different representation of the category EAV model, optimized for speed. But the authoritative table, still is catalog_category_entity. So you should ...


4

You can do this by adding image attribute at frontend/product/collection/attributes node at config.xml <frontend> <product> <collection> <attributes> <image/> </attributes> </collection> </product> </frontend> Explanation: Let explain: ...


4

If your data exceeds the row size available, your options are limited. I've documented this personally here, https://www.sonassi.com/knowledge-base/magento-kb/mysql-limitations-on-the-flat-catalogue-in-magento/ Ultimately, at 4,000 products - I'd expect to see negligible improvements when using catalog_product_flat anyway - so I wouldn't think its worth ...


4

This is mostly due to any of the below two reasons Server timed out and closed the connection. fix: try increasing the wait_timeout variable in your mysqld’s my.cnf/my.ini configuration file. Server dropped an incorrect or too large packet. fix : increase the maximal packet size limit by increasing the value of max_allowed_packet in my.cnf/my.ini file. ...


4

Well, I do not know whether the error is 100 % resolved or not. But based on what you told me here, and in this post : https://dba.stackexchange.com/questions/40899/mysql-error-reading-communication-packets, I make this changes in the MySQL Config File: max-allowed-packet = 1024M wait-timeout = 7200 innodb-log-file-size ...


3

Interestingly, the used flat table is set once and never changed which works for EAV since table name doesn't change but not for flat since the table name includes the store ID. A workaround would be to make a helper that would swap out the table in the FROM part of the query. Here's an example of such a helper: class My_Module_Helper_Data extends ...


3

It turned out that somehow the tables got corrupted and instead of "int" it said "varchar" in the eav_attribute table for "backend_type" - therefore the indexer was searching in the wrong table (catalog_product_entity_int vs catalog_product_entity_varchar)


3

In config.xml of the Mage_Catalog module there is this: <max_index_count>64</max_index_count>. You can try to increase this limit, but I don't know the implications. You can do that by adding this in the config.xml from any one of your modules inside the <global> tag. <catalog> <product> <flat> &...


3

This Method exists at least in EE 1.12 not. So I think your Mage_Catalog_Model_Category class is not from actual version.


3

Your attribute needs a source model to provide the select statement. For example see Mage_Eav_Model_Entity_Attribute_Source_Table::getFlatUpdateSelect(): /** * Retrieve Select For Flat Attribute update * * @param int $store * @return Varien_Db_Select|null */ public function getFlatUpdateSelect($store) { return Mage::getResourceModel('eav/...


3

Your EAV attribute is used as a column of flat table (catalog_product_flat_*) if: Use In Layered Navigation ->not No Use for Promo Rule Conditions ->yes Used in Product Listing ->yes Used for Sorting in Product Listing ->yes Attribute type is 'static' And attribute is system attribute like [status, tax_class_id, price, weight] Flat tables have great ...


Only top voted, non community-wiki answers of a minimum length are eligible