Magento flat product

Question

when trying to enable and reindex Product Flat Data - getting error from magento

The Flat Catalog module has a limit of 64 filterable and/or sortable attributes. Currently there are 521 of them. Please reduce the number of filterable/sortable attributes in order to use this module.

I can't understand what this means and from where magento gets this values. In attributes I have only 321 different attributes so from where magento gets value of 521 currently used and where it takes limit for 64 of them???

thnx.

Answer 1

The method Mage_Catalog_Model_Resource_Product_Flat_Indexer::prepareFlatTable() is throwing this exception, when your product has more than

<global>
    <catalog>
        <product>
            <flat>
                <max_index_count>64</max_index_count>
            </flat>
        </product>
    </catalog>
</global>

indexes. This maximum usually is defined in app/code/core/Mage/Catalog/etc/config.xml and matches the maximum number of 64 keys allowed by default mySQL installations, before they error with

ERROR 1069: Too many keys specified. Max 64 keys allowed

Your problem is not how many attributes your products have, but how many of them are filterable and/or sortable (except all your attributes are, of course).

Trace Mage_Catalog_Model_Resource_Product_Flat_Indexer::getFlatIndexes() to find out how Magento reaches a total of 521 indexes.

For a quick check you can also have a look at your catalog_eav_attribute table and check how many attributes are filterable (is_filterable = 1) and/or sortable (used_for_sort_by = 1)

Answer 2

The error you are getting is coming from the following file:

/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Flat|Indexer.php

It appears that this limit is set in the Catalog config.xml file which is located in the following file:

/app/code/core/Mage/Catalog/etc|config.xml

Doing a search for "max_index_count" you will find that this value is set to 64.

I am not sure why this limit is in place, but I have to imagine that they had a good reason for setting it to 64. On a DEVELOPMENT environment (I would suggest you do not try this on your live site before you understand what could happen) you could change this value and see what happens.

The number of indexes you have (the 521) is coming from the getFlatIndexes function in the Indexer.php file. I am guessing you are getting to 521 because you have your 321 + some additional ones that Magento uses by default.

If I had to guess, this limit is put in place to keep the flat catalog tables from getting too large horizontally. I am guessing there is a speed issue when the table gets too large.