Cleaning Up Your Magento Database: Deleting Multi Stores
Are you facing issues with a large, bloated Magento database due to multiple stores? You’re not alone. As your business grows, so does the database size when using multi-store setup. In this post, we’ll guide you through splitting your existing installations into separate backends and databases while cleaning up leftover data.
The Multi-Store Challenge: A Large Database
With around 7 stores running off a single backend and database, it’s natural for indexing to become slow and the overall performance to degrade. This is because Magento stores all store-specific data in the same schema, leading to a massive database size over time. In your case, a 1GB database indicates that you’ve outgrown the single-installation approach.
Splitting Magento Installations: The First Step
You took the right initiative by duplicating your Magento installation and deleting unnecessary store views. This manual process helps in creating separate instances for each desired store. Remember to configure your web server to point to the appropriate index.php file for each domain.
However, as you correctly pointed out, Magento doesn’t automatically delete the database entries for these deleted stores. This leftover data contributes significantly to the database size and needs to be cleaned up.
Identifying Leftover Data in Your Database
Magento uses various tables to store store-specific information. Some of the key tables include:
core_store: Stores basic information about each store view.catalog_product_entity_int: Product attributes linked to specific stores.catalog_category_entity: Category table with store-specific data.
Deleting Leftover Data: SQL Queries
To clean up leftover data, you can use SQL queries to delete the entries related to the stores you’ve removed. Here are some examples:
1. Delete Store Information
You can start by deleting the entry from core_store for the store view you no longer need.
DELETE FROM core_store WHERE code = 'removed_view_code';
Replace 'removed_view_code' with the actual code of the store view you want to delete.
2. Delete Product and Category Links
Next, you’ll need to remove the product attributes and category links associated with the deleted store views:
DELETE FROM catalog_product_entity_int WHERE attribute_id IN (SELECT attribute_id FROM eav_attribute WHERE is_global = 0)
AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product')
AND store_id IN (
SELECT store_id FROM core_store_group
JOIN core_store ON core_store.group_id = core_store_group.group_id
WHERE code = 'removed_view_code'
);
This query deletes the product attributes that are not global and linked to the deleted store.
For categories, you can use a similar approach:
DELETE FROM catalog_category_entity WHERE entity_id IN (
SELECT e.entity_id
FROM catalog_category_entity as e
JOIN core_store_group AS g ON (g.group_id = e.group_id)
JOIN core_store AS s ON (s.group_id = g.group_id)
WHERE s.code = 'removed_view_code'
);
Conclusion: A Thorough Database Cleanup
Cleaning up your Magento database involves a systematic approach to deleting the appropriate tables and entries. By using SQL queries, you can remove leftover data without affecting your existing stores.
Remember that it’s crucial to backup your database before performing any deletions to avoid data loss. Always test your cleanup process on a staging environment or development setup first.
As your business continues to grow, consider exploring platform-as-a-service (PaaS) solutions like Magento Commerce Cloud offered by Adobe. These solutions provide scalability and performance optimized for multi-store setups without the need for manual database management.
Don’t hesitate to reach out if you require further assistance with your Magento setup or have any other questions about e-commerce development.