huge size for catalog_product_index_eav table

[eversun]

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? 

[Mukesh Tiwari]

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.

[eversun]

 @Mukesh Tiwari, 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

[Mukesh Tiwari]

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.

[eversun]

@Mukesh Tiwari, 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? 

[Mukesh Tiwari]

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.

[eversun]

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? 

[eversun]

@Mukesh Tiwari 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.

[Mukesh Tiwari]

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.