Is it safe to truncate these tables?

Question

I have Magento 1.9 installed, and have approximately 3,000 products. We've been doing a ton of CSV importing, and I'm getting concerned about the size of the database.

In particular, these 3 tables concern me:

1. catalog_product_index_price - 442,442 rows
2. catalog_product_index_price_idx - 442,442 rows
3. core_url_rewrite - 270,749 rows

I have truncated the core_url_rewrite table in another Magento system, and everything was fine once it reindexed.

But I'm not sure about catalog_product_index_price and catalog_product_price_idx - can I truncate those 2 tables? Will they rebuild themselves after we reindex?

Please advise.

Answer 1

in theory, if you reindex everything after you truncate these tables everything should be OK (but you might lose some custom url rewrites that are not linked to products or categories).

The table catalog_product_index_price_idx will be truncated anyway when the price reindex starts. See here how _idx tables work.

but I bet that if you truncate the catalog_product_index_price and you reindex, you will end up with the same number of rows. If you don't you might have an issue with the foreign keys of your database.

Anyway...before trying to truncate them, you should backup your database.

Answer 2

core_url_rewrite table should not be truncated unless you understand what you are doing.
magento uses it to keep track of old urls (url_key specifically) and what the new one should be.
if you create a product (or category) and just put in a name like "My Item", magento will add a record to that table that essentially says, 'if the url is "/my-item.html" then show item id 1.
at this point, if you empty that table and then rebuild the indexes, that will work just fine and nothing will not work. but, let's assume that same "My Item" above. let's say you change the name of that item to "More Specific Item Name", magento will change the url_key for that item to be "more-specific-item-name" and now the records in the core_url_rewrite table will be

for the url "/more-specific-item-name.html" they want to see item 1 for the url "/my-item.html" they want to see item 1

all good, both the old and the new url will work to get to that item. this is good for google or other web crawlers and it is also good for user bookmarks or forum posts, etc that reference the old url directly. if at this point, you truncate the core_url_rewrite and rebuild the indexes, you'll lose the old url of "/my-item.html" as magento will no longer know what it should show for that url and the visitor (and google or other web crawlers) will get a 404.

to further complicate this, magento, by default, sets the url_key based on the name of the item or category. should you have duplicates, it appends sequential numbers at the end. so, if you have a category called "Pants" and a product called "Pants", then 1 url will be "pants.html" and the other "pants-1.html" magento will only know which one is which by the existing core_url_rewrite data and should you clear out that table, then it could regenerate them in reverse order.

all that said, i make sure i clear out that table before i move a site live because with a lot of product or category name changes will really bloat that table, but once the site is live, i leave it alone unless i have a very good reason.

the other 2 pricing index tables should rebuild exactly the same after you clear them, so no worries on that.