I am familiar with the core data tables for products and categories, i.e. catalog_category_entity_.. and catalog_product_entity_..

I know also that there are some tables that flatten indexes and this happens when you index products or categories. Question #1: Do they all end in _idx? Can someone help me identify them?

Reason: my company has a very large database of products and export is taking a while. I am considering exporting from production with all the index tables blank, and building them on the receiving end in QA.

Question #2: Will this work? Question #3: Will this provide a significant space/time savings?


Magento uses these *_idx tables, but also catalog_product_flat_* and catalog_category_flat_* tables. There is a flat table for every store you have. In these cases, the "*" will be replaced by the store ID.

If you want to reduce the size of the database export, I would exclude the data of the log tables and the report tables. Also, when you need the database only for testing purposes, you might also want to exclude the customer and order data. This should make a pretty big difference.

To export the database, you might want to use n98-magerun. There is a function there, which you can use to export your database, but it will exclude the tables you add as parameters. Personally I always use this command when I need to make an export of my database for development/testing purposes:

n98-magerun --compress="gz" --strip="@development" ../backup.sql.gz

This will create an export, it will strip all the data you don't need for development purposes and it will compress it using GZip, which makes the file a lot smaller.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.