Magento Database Cleaning

Magento is an excellent platform, but it unfortunately falls short when it comes to having an efficient database. When selling a myriad of products, it’s a smart idea to have a sizable database. Managing the database by performing regular log cleanings can drastically improve the performance of your website along with its latency. The following is a walk through of how to tidy up a Magento database that has become over encumbered.

For starters, it is imperative that you make a back up of all your files prior to doing anything in case something goes wrong. You can either perform an export of your database using phpMyAdmin, or you can submit a technical support request so that we can do it for you.

Magento manages several logging tables. These tables make logs of things like which products have been compared, customer accesses and other ancillary logs. Magento has a self cleaning program to clean this out regularly, but this feature is inactive by default, and most consumers wont turn it on. Here are three ways to clean out these tables. You can either access the “Log Cleaning” feature in the Magento Admin, or you could just manually do so via phpMyAdmin.

The tables below are managed by the Magento Log Cleaning function:

log_customer
log_visitor
log_visitor_info
log_url
log_url_info
log_quote
report_viewed_product_index
report_compared_product_index
report_event
catalog_compare_item

Log Cleaning through the Admin

1. While in the Magento Admin click on System, then click configuration.
2. In the menu on the left under Advanced click System.
3. Where it says “Log Cleaning”, select yes to enable log cleaning, and configure the save your logs for 15 days. Then click save.

Manual Cleaning through phpMyAdmin

This is by far the most effective way to clean the logs for individuals that are more comfortable working with databases. It’s definitely quicker than the Magento tool, and it allows one to clean some of the tables that aren’t included in the Magento tool.

-Open the database in PhpMyAdmin in the cPanel Control Panel

-In the right frame, click on the boxes for the following tables:

dataflow_batch_export
dataflow_batch_import
log_customer
log_quote
log_summary
log_summary_type
log_url
log_url_info
log_visitor
log_visitor_info
log_visitor_online
report_viewed_product_index
report_compared_product_index
report_event

Look to the bottom of the page, then click the drop down box that says “with selected” and click empty.

A confirmation screen will appear, click yes, and this will truncate all the tables you just selected

Note: It can be very easy to mistakenly delete a database instead of emptying it, you should be very careful and make sure that you are selecting the correct option.

Make sure you perform this regularly. We’ve seen a 3+ gigabyte database drop to less than 100 megabytes after proper log cleaning has been performed. This kind of maintenance is very important for the performance and health of your website and needs to be done periodically.

For an automated method to clean this up regularly, you can setup a cron job by following our instructions on our post about speeding up Magento commerce.

How To Setup Multiple Stores With Magento

Magento Multi-Store & Setting Up Multiple Magento Stores Using One Admin Area

If you want to setup multiple Magento stores and multiple domains that share the same admin area, the tutorial below will assist you in doing so. This is very useful if you have a lot of stores that share the same or related products and you wish to manage it from one central location. You will also be able to track your sales and customers without having to login to the admin area of each website.

With the latest release of Magento Commerce it is much easier to set this up than it was in previous versions. Before it required you to modify the index.php file to handle your different domains pointing at different stores and various other complicated modifications. With Magento 1.4.x it is now much easier. The new index php contains the following code:

$mageRunCode = isset($_SERVER['MAGE_RUN_CODE']) ? $_SERVER['MAGE_RUN_CODE'] : '';
$mageRunType = isset($_SERVER['MAGE_RUN_TYPE']) ? $_SERVER['MAGE_RUN_TYPE'] : 'store';

It checks two variables(code & type) and then uses them to run Magento. With the new code you’re able to set which store is supposed to be running under your main domain directly within your virtual host definition(root access required) or for our shared hosting and cPanel hosting customers you can actually now use .htaccess to accomplish this.

Creating your additional stores

If you haven’t already done so, the first thing you will need to do is setup your second store in the Magento admin area:

1. Login to the Magento admin area.
2. Go to the Catalog tab, then select Manage Categories.
3. Click the ‘Add Root Category’ button on the left.
4. On the right, for the Name, enter your secondwebsite.com. In the dropdown set Yes for both ‘Is Active’ and ‘Is Anchor’.
5. Click on ‘Save Category’.
6. Next you should go to the ‘System’ tab and select ‘Manage Stores’.
7. Click the ‘Create Website’ button.
8. For the Name enter your Secondwebsite.com, and for the ‘Code’ you should enter secondwebsite. You’ll be using this value later so make sure your note it down somewhere.
9. Click ‘Save Website’.
10. In the same area, click on the ‘Create Store’ button.
11. For the Website, select your Secondwebsite.com from the dropdown. For the ‘Name’, you can enter Secondary Store. For the Root Category, select your now created Secondwebsite.com from the dropdown.
12. Click the ‘Save Store’ button.
13. Next click on the ‘Create Store View’ button.
14. For the ‘Store’, select Secondary Store from the dropdown menu, make sure it’s for the created Secondwebsite.com. For the Name, you can enter English. For the ‘Code’, you should enter secondwebsite_en. For the Status, select ‘Enabled’ from the dropdown menu.
15. Click on ‘Save Store View’.
16. Next you will need to go to the ‘System’ tab and select ‘Configuration’
17. For ‘Current Configuration Scope’ (located near the top left), change the dropdown menu from ‘Default Config’ to Secondwebsite.com.
18. Select ‘Web’ from the left sidebar under the ‘Genera’l headline.
19. For both the Unsecure and Secure sections, uncheck the Use default box next to the Base URL item, and enter the URL for your store, e.g. secondwebsite.com. Make sure to include the trailing backslash.
20. Click the ‘Save Config’ button.

You’re almost done so hang in there!

Once you are finished creating the store, if you have not added your second domain as an add-on domain you will need to login to your cPanel account with us and create it as an Add-on domain now. Once you have done this you then need to copy your index.php and .htaccess file to the created directory for your add-on domain.

After copying over your index.php file, you should replace the following lines of code in the index.php file located only in the new add-on directory:

Replace this line:

$mageFilename = ‘app/Mage.php’;

With the following:

$mageFilename = ‘../public_html/app/Mage.php’;

Next you will need to either use the VirtualHost method(VPS and dedicated servers) or the .htacess method (for regular magento hosting / shared hosting accounts).

Using VirtualHost:

To set this up using the Virtualhost method you should simply add the following lines within your VirtualHost definition file:

SetEnv MAGE_RUN_CODE "base"  #Put your main/base website or store code here.
SetEnv MAGE_RUN_TYPE "secondwebsite"  #Here you should put 'secondwebsite' if the URL of the additional store you are adding is secondwebsite.com.

Using the .htaccess method:

If you do not have access to the virtual host file and are not a VPS or dedicated server customer, you can still use .htaccess for this.

In your .htaccess code you will need to define your main and secondary websites. For example if the URL of your main/base website is base.com and the URL of your second website is secondwebsite.com you should put the following code in your .htaccess


SetEnvIf Host .*base.* MAGE_RUN_CODE="base";
SetEnvIf Host .*secondwebsite.* MAGE_RUN_TYPE="secondwebsite";


You are, of course, not limited to using just 2 websites. To add additional stores you would simply add the additional lines of necessary code:


SetEnvIf Host .*thirdwebsite.* MAGE_RUN_TYPE="thirdwebsite";


etc.

You should now be all setup using Magento multi-stores and have multiple stores with one main admin area in Magento, without messing around with the code and the other previous lengthy methods .

If you have any questions, additions or issues with this feel free to comment.