My Magento store has around 60,000 products. We use flat tables. We are in the process of separating them into different attribute sets, and are adding lots of new attributes to assist with filtering and comparing, etc.
Recently we ran into a database limit from having too many attributes while indexing our flat product catalog:
SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535.
Now I know there are ways to adjust Magento to handle this, but the way I see it is that this problem will resurface again and again as the number of attributes in our system increases (which it will).
New question: Should I disable the flat product catalog and run my site off EAV? At what point (if at all) do the flat tables lose their performance gains over EAV? Does anyone have any useful performance benchmarks comparing the two at different database sizes?
Best Answer
In Product list page magento uses product flat table for better performance ,
Because at list page we have very big number of product that is why people uses flat table instead of eav But in your case you have big number of product and indexing causing issue that is why you cannot updating the flat table.
So that's why you want to disable the flat catalog.
My suggestion is that if you do same then load small number of product at list page like load 15-20 product max to per page then only you get good page speed Otherwise ti take too long to load page and sometime you may face issue like server time out.
So if you disable product then reduce number of product per page at category listing.
You can see different in performance by installing this extension in your local(developing) system
https://www.mgt-commerce.com/magento-developer-toolbar.html