cancel
Showing results for 
Search instead for 
Did you mean: 

huge size for catalog_product_index_eav table

huge size for catalog_product_index_eav table

I only have about 30k records of products, however, there're over 8M records in catalog_product_index_eav table, now add/edit product become very slow. Could anyone tell what is the catalog_product_index_eav table for? anyway to reduce the amount of records and improve the performance? 

8 REPLIES

Re: huge size for catalog_product_index_eav table

Hi @eversun

 

Before doing following take full database backup of your site and try it first on the development or staging site.

You should truncate catalog_product_index_eav table then reindex your magento site. This table should be repopulated with the data after reindexing.

 

NOTE : Do not delete catalog_product_index_eav  table just truncate it.

 

---
Problem Solved Click Accept as Solution!:Magento Community India Forum

Re: huge size for catalog_product_index_eav table

 @muk_t, thanks for your answer, I also noticed that there're over 1.3 million records in catalog_product_flat_cl table, and 500k records in following tables, is it safe to truncate all these tables? or anyway to reduce the size?

 

catalog_product_index_price_cl

catalog_product_entity_varchar

Re: huge size for catalog_product_index_eav table

Hi @eversun

 

You can truncate  catalog_product_flat_cl and catalog_product_index_price_cl tables but do not truncate  catalog_product_entity_varchar  other wise you will loose all the products data.

 

Refer following link for more information : List of tables to safely truncate in Magento?

 

Important : 

1) Before doing any of the mentioned steps take a full database backup of your site.

2) First perform all these tasks on the staging or development server.

3) Once after doing this test your site.

 

 

---
Problem Solved Click Accept as Solution!:Magento Community India Forum

Re: huge size for catalog_product_index_eav table

@muk_t, thanks for your quick response! I'm currently experiencing slowness in editing/save products in admin panel, I only have 30k records though.. would removing of the index make it faster to save product if I want to add a bunch of products? any recommendation? 

Re: huge size for catalog_product_index_eav table

Hi @eversun

 

For admin slowness I recommend you to check your custom modules. If any of the custom module has a database table for admin related events or changes. Try to check the table size , if table size is too much for such custom table then based on data in it you should remove old entries. This also helps to improve Magento speed in admin side.

Like some extensions keep record of custom email notifications sent, some custom log tables etc.

 

Try following query to find out the individual table sizes in MB in a MySQL database

SELECT table_name, table_rows, data_length, index_length, 
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES where table_schema = "databasename";

So you will come to know about the table size.

---
Problem Solved Click Accept as Solution!:Magento Community India Forum

Re: huge size for catalog_product_index_eav table

there's no custom module at all.. after I truncate the table catalog_product_index_eav_idx, the reindex taking long time(about 1.5 hours already), and the size has reached over 8M and keep running...  is it normal behavior? 

Re: huge size for catalog_product_index_eav table

@muk_t a few more question, what does the two table: catalog_product_index_eav_idx and catalog_product_index_eav do? are they used for search purpose only? can I just truncate these two tables if I don't search the products or category? how would it impact the system if I leave these two tables empty? After reindex, these two tables have over 15M records each, and catalog_product_flat_cl table have over 1.3M records.

Re: huge size for catalog_product_index_eav table

Hi @eversun

Do not truncate the catalog_product_index_eav_idx and catalog_product_index_eav tables.

Depending on number of products and number of stores your tables may have huge amount of data.

---
Problem Solved Click Accept as Solution!:Magento Community India Forum