MySQL Queries Gone Wild
For various reasons, Magento can sometime spurn queries for MySQL that can cause severe slowdown or stoppage for your Magento store. We provide you with some nifty solutions we have discoverd over the years.
Accessing MySQL on STRATUS
To access your Magento MySQL instance on STRATUS:
- Access your Magento install via SSH.
- Navigate to the Magento web root (typically,
n98-magerun db:console(Magento 1) or
n98-magerun2 db:console(Magento 2).
- Run the query you desire.
To Get Table Sizes
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;
To "Blackhole" Log Tables
There are a number of log-related tables that get bloated very quickly. These tables are rarely referred to by Magento store owners. If you don't anticipate needing the data from these tables, then you should empty and "blackhole" them.
delete from log_url; delete from log_url_info; delete from log_visitor; delete from log_visitor_info; delete from report_event; alter table log_visitor engine=BLACKHOLE; alter table log_visitor_info engine=BLACKHOLE; alter table log_url engine=BLACKHOLE; alter table log_url_info engine=BLACKHOLE; alter table report_event engine=BLACKHOLE;
Magento 1 Viewed Products Index Table
If you are using recently viewed products on your store, then this will not apply. However, if you don't use recently viewed products, this is another table that, by blackholing it, it can greatly reduce the size of your MySQL database.
DELETE from report_viewed_product_index; ALTER TABLE report_viewed_product_index ENGINE=blackhole;
Magento 1 URL Rewrite Table
There are bugs in Magento 1 that cause the
core_url_rewrite table to over-inflate with duplicate entries. It is recommended that this table should be periodically cleaned out.
TRUNCATE TABLE core_url_rewrite;
Magento 1 Dataflow Batch Tables
This table store pre-written results for DataFlow export and import. There is a bug in some versions of Magento where these tables are not truncated appropriately before and after an import /export. These tables can be truncated, however they should not be blackholed.
TRUNCATE TABLE dataflow_batch_export; TRUNCATE TABLE dataflow_batch_import;
Magento 1 Sales Flat Quote Table
This table contains a record for every shopping cart created in a Magento store, whether the cart was abandoned or converted to a purchase. Each row represents one cart.
Due to the potential size of this table, may Magento stores delete records from this table based on a certain criteria (e.g., all non-converted carts after 60 days).
To delete all carts older than 60 days:
DELETE FROM sales_flat_quote WHERE updated_at < DATE_SUB(Now(),INTERVAL 60 DAY) AN is_active=1;
Magento 2 Customer Visitor Table
This table has been known to get quite large and slow down a Magento store. You can delete all customer data older than 3 days, for example, with:
DELETE FROM customer_visitor WHERE last_visit_at < DATE_SUB(NOW(), INTERVAL 3 DAY);