0

We have a CE 1.9.2.4 store which has been live around 3 weeks.

Prior to launch, we used Cart2Cart to import the client's historical customers & their orders from his old CRE Loaded site.

The sites response time is now very slow and we think it's down to the size of the database (890mb).

Is there any way to clean up the database? Purge old orders? Make things work quicker?

1

To clean DB use:

SET foreign_key_checks = 0;

TRUNCATE core_cache;
TRUNCATE core_cache_option;
TRUNCATE core_cache_tag;
TRUNCATE core_session;

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;
TRUNCATE index_process_event;
TRUNCATE catalog_compare_item;

SET foreign_key_checks = 1;

Also you can update mysql.cnf file for better performance, look into this https://github.com/magenx/Magento-mysql/blob/master/my.cnf/my.cnf

  • Thank you for your reply Ahmed. Please forgive my questions, I am way out of my comfort zone here. Is the above an SQL query to be run all in one go? Or do I manually select the relavant tables and select truncate? What does the SET foreign_key_checks at the start and end do? What does truncate actually do? I'm guessing I can make a copy of the database, do the above, then select the copy in local.xml to switch to it? Thank you again and sorry for all the questions. Regards Steve – LordSprocket Aug 24 '16 at 4:21
  • Steve, This query will clean the DB, truncate will empty the table values. and you can it via PHPMyAdmin docs.nexcess.net/article/… please make sure you have DB backup first – Ahmed Elawadi Aug 24 '16 at 8:10
  • Thank you Ahmed, I'll give this a whirl overnight. I don't suppose you'd be 'for hire' with a view to assisting with this issue? My hosts reckon the PHP is getting 'hammered' at every page load, I wonder if the database is either too large or messed up somehow following the Cart2Cart import? Thanks Steve – LordSprocket Aug 24 '16 at 10:46
  • Thank you Ahmed, what's the best way to contact you? – LordSprocket Aug 24 '16 at 12:19

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.