What's The Difference Flat tables vs EAV?

[silas3]

Can someone explain the relationship between ‘Flat Tables’, and indexes? The index called ‘Flat Index’ in this post https://magento.stackexchange.com/questions/22157/how-indexing-works-in-magento/22166 sounds exactly like a flat table.

So my two quandaries are, if there is a ‘Flat Index’ generated from EAV structure, what is the point of having a flat table?

If the ‘Flat Index’ is a separate table from  catalog_category_flat_store_1, when you’re running with ‘Flat tables’ does it still create and use the ‘flat index’ table ?

[Tom Robertshaw · Accepted solution]

Hi @silas3. With "Flat Catalog" setting enabled, Magento will use the catalog_category_flat_store_n and catalog_product_flat_store_n tables. This is what marius is referring to as Flat Index (put more verbosely the Flat Catalog Index). This makes fetching catalog data faster when compared to fetching from EAV.

The process of updating these tables is called indexing. If I understand your question correctly, you're right that an index is synonymous with a flat table. All of the indexes in Magento are aimed to reduce the number of tables that data is spread across, (sacrificing normalisation and speed of table structure modification like adding a product attribute, for speed of access). 

[silas3]

Thanks for replying.

I'm still not sure why, if when using EAV the indexing process creates a flattened table, why would you switch to 'running on flat tables'? (as the indexing process does that anyway)

[silas3]

Sorry, misread your answer.

Right, the 'Flat Index' IS the table generated when running flats.

During the reindex process when running on flats, does the indexer work from EAV data into the 'Flat Index' and all the other index tables as if it was running in EAV normally do you know?