Periodically Maintenance for OpenMage Database

[addison74]

I would like to share with you the SQL query I am using from time to time to clean up some tables. See bellow.

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE `your_database_name`.`adminnotification_inbox`;

TRUNCATE TABLE `your_database_name`.`catalog_compare_item`;

## After this operation you have to go in Backend and reindex 'Catalog URL Rewrites'
TRUNCATE TABLE `your_database_name`.`core_url_rewrite`;

TRUNCATE TABLE `your_database_name`.`catalog_category_flat_store_1`;

TRUNCATE TABLE `your_database_name`.`cron_schedule`;

TRUNCATE TABLE `your_database_name`.`dataflow_batch_export`;
TRUNCATE TABLE `your_database_name`.`dataflow_batch_import`;

TRUNCATE TABLE `your_database_name`.`index_event`;

TRUNCATE TABLE `your_database_name`.`log_quote`;
TRUNCATE TABLE `your_database_name`.`log_summary`;
TRUNCATE TABLE `your_database_name`.`log_url`;
TRUNCATE TABLE `your_database_name`.`log_url_info`;
TRUNCATE TABLE `your_database_name`.`log_visitor`;
TRUNCATE TABLE `your_database_name`.`log_visitor_info`;
TRUNCATE TABLE `your_database_name`.`log_visitor_online`;

TRUNCATE TABLE `your_database_name`.`report_compared_product_index`;
TRUNCATE TABLE `your_database_name`.`report_event`;
TRUNCATE TABLE `your_database_name`.`report_viewed_product_aggregated_daily`;
TRUNCATE TABLE `your_database_name`.`report_viewed_product_aggregated_monthly`;
TRUNCATE TABLE `your_database_name`.`report_viewed_product_aggregated_yearly`;
TRUNCATE TABLE `your_database_name`.`report_viewed_product_index`;

DELETE FROM sales_flat_quote WHERE updated_at < DATE_SUB(Now(),INTERVAL 60 DAY);
## Query to delete the orders which were not converted.
## DELETE FROM sales_flat_quote WHERE entity_id NOT IN (SELECT quote_id FROM sales_flat_order);

SET FOREIGN_KEY_CHECKS = 1;

[m-overlund]

Note: Truncating core_url_rewrite will result in removing custom URL rewrites. Only is_system = 1 should entries should be removed