Magento Stack Exchange is a question and answer site for users of the Magento e-Commerce platform. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Can you please tell me that which tables are containing junk data??

My webstore database is increasing daily. So i want to reduce my database size.

share|improve this question
up vote 2 down vote accepted

Most likely it will be log tables if you have log enabled. But to be sure run this query it will give you the largest tables and their sizes in your db:

SELECT CONCAT(table_schema, '.', table_name),
   CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
   CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
   CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
   CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
   ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  10;

If you want to disable logging to the database then you need to add this to your local.xml:

<frontend>
<events>
    <controller_action_predispatch>
        <observers><log><type>disabled</type></log></observers>
    </controller_action_predispatch>
    <controller_action_postdispatch>
        <observers><log><type>disabled</type></log></observers>
    </controller_action_postdispatch>
    <customer_login>
        <observers><log><type>disabled</type></log></observers>
    </customer_login>
    <customer_logout>
        <observers><log><type>disabled</type></log></observers>
    </customer_logout>
    <sales_quote_save_after>
        <observers><log><type>disabled</type></log></observers>
    </sales_quote_save_after>
    <checkout_quote_destroy>
        <observers><log><type>disabled</type></log></observers>
    </checkout_quote_destroy>
</events>

Then to go System > Configuration > Advanced and set Mage_Log to Disable

Finally, flush your Magento caches under System > Cache Management

share|improve this answer

Optimize Magento database

Attention: Always backup your data before performing any operations on the database.

Log cleaning

Magento maintains several tables for logging. These tables log things such as customer accesses and frequently-compared products. Magento has a mechanism for cleaning these logs regularly, but unfortunately, this feature is disabled by default and most customers do not enable it. There are three ways to clean out these tables: via log cleaning in the Magento Admin, via log.php in the ../shell directory, and manually via phpMyAdmin or MySQL client.

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 via administrator interface

1.From the Magento administrator interface, go to System > Configuration.

2.In the left menu under Advanced, click System.

3.Under Log Cleaning, change Enable Log Cleaning to Yes and configure the Save Log for 15 days: pic here 4.Click Save Config.

Manual cleaning via phpMyAdmin

This is the most efficient way to clean the logs for those more comfortable working with databases. It is faster than the built-in Magento tools and it allows you to clean other tables not included in those tools. This procedure will consolidate the data inside those tables, often decreasing database size by as much as 95% and greatly reducing query times.

  1. Open the database in phpMyAdmin.

  2. Run the following query:

set foreign_key_checks=0;
TRUNCATE dataflow_batch_export;
TRUNCATE dataflow_batch_import;
TRUNCATE log_customer;
TRUNCATE log_quote;
TRUNCATE log_summary;
TRUNCATE log_summary_type;
TRUNCATE log_url;
TRUNCATE log_url_info;
TRUNCATE log_visitor;
TRUNCATE log_visitor_info;
TRUNCATE log_visitor_online;
TRUNCATE report_viewed_product_index;
TRUNCATE report_compared_product_index;
TRUNCATE report_event;
TRUNCATE index_event;

3.At the bottom of the page, click the drop-down box With Selected and select Empty.

4.A confirmation screen will appear. Click Yes. This will truncate all of the selected tables.
5.Click the Structure tab at the top of the page.
6.Select the same tables as you did in step 2, then under the With Selected drop-down menu, select Optimize.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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