Convert Database To InnoDB

Last modified by Crucial on Mon, January 26, 2009 16:04
Source|Old Revisions  |  Back To Group

This is an old revision of the document!


You can run the following query in phpMyAdmin to convert the necessary tables in your existing Magento database to use the InnoDB storage engine:

ALTER TABLE `admin_assert` ENGINE=InnoDB;
ALTER TABLE `admin_role` ENGINE=InnoDB;
ALTER TABLE `admin_rule` ENGINE=InnoDB;
ALTER TABLE `admin_user` ENGINE=InnoDB;
ALTER TABLE `adminnotification_inbox` ENGINE=InnoDB;
ALTER TABLE `api_assert` ENGINE=InnoDB;
ALTER TABLE `api_role` ENGINE=InnoDB;
ALTER TABLE `api_rule` ENGINE=InnoDB;
ALTER TABLE `api_user` ENGINE=InnoDB;
ALTER TABLE `catalog_category_entity` ENGINE=InnoDB;
ALTER TABLE `catalog_category_entity_datetime` ENGINE=InnoDB;
ALTER TABLE `catalog_category_entity_decimal` ENGINE=InnoDB;
ALTER TABLE `catalog_category_entity_int` ENGINE=InnoDB;
ALTER TABLE `catalog_category_entity_text` ENGINE=InnoDB;
ALTER TABLE `catalog_category_entity_varchar` ENGINE=InnoDB;
ALTER TABLE `catalog_category_product` ENGINE=InnoDB;
ALTER TABLE `catalog_category_product_index` ENGINE=InnoDB;
ALTER TABLE `catalog_compare_item` ENGINE=InnoDB;
ALTER TABLE `catalog_product_bundle_option` ENGINE=InnoDB;
ALTER TABLE `catalog_product_bundle_option_value` ENGINE=InnoDB;
ALTER TABLE `catalog_product_bundle_selection` ENGINE=InnoDB;
ALTER TABLE `catalog_product_enabled_index` ENGINE=InnoDB;
ALTER TABLE `catalog_product_entity` ENGINE=InnoDB;
ALTER TABLE `catalog_product_entity_datetime` ENGINE=InnoDB;
ALTER TABLE `catalog_product_entity_decimal` ENGINE=InnoDB;
ALTER TABLE `catalog_product_entity_gallery` ENGINE=InnoDB;
ALTER TABLE `catalog_product_entity_int` ENGINE=InnoDB;
ALTER TABLE `catalog_product_entity_media_gallery` ENGINE=InnoDB;
ALTER TABLE `catalog_product_entity_media_gallery_value` ENGINE=InnoDB;
ALTER TABLE `catalog_product_entity_text` ENGINE=InnoDB;
ALTER TABLE `catalog_product_entity_tier_price` ENGINE=InnoDB;
ALTER TABLE `catalog_product_entity_varchar` ENGINE=InnoDB;
ALTER TABLE `catalog_product_link` ENGINE=InnoDB;
ALTER TABLE `catalog_product_link_attribute` ENGINE=InnoDB;
ALTER TABLE `catalog_product_link_attribute_decimal` ENGINE=InnoDB;
ALTER TABLE `catalog_product_link_attribute_int` ENGINE=InnoDB;
ALTER TABLE `catalog_product_link_attribute_varchar` ENGINE=InnoDB;
ALTER TABLE `catalog_product_link_type` ENGINE=InnoDB;
ALTER TABLE `catalog_product_option` ENGINE=InnoDB;
ALTER TABLE `catalog_product_option_price` ENGINE=InnoDB;
ALTER TABLE `catalog_product_option_title` ENGINE=InnoDB;
ALTER TABLE `catalog_product_option_type_price` ENGINE=InnoDB;
ALTER TABLE `catalog_product_option_type_title` ENGINE=InnoDB;
ALTER TABLE `catalog_product_option_type_value` ENGINE=InnoDB;
ALTER TABLE `catalog_product_super_attribute` ENGINE=InnoDB;
ALTER TABLE `catalog_product_super_attribute_label` ENGINE=InnoDB;
ALTER TABLE `catalog_product_super_attribute_pricing` ENGINE=InnoDB;
ALTER TABLE `catalog_product_super_link` ENGINE=InnoDB;
ALTER TABLE `catalog_product_website` ENGINE=InnoDB;
ALTER TABLE `catalogindex_aggregation` ENGINE=InnoDB;
ALTER TABLE `catalogindex_aggregation_tag` ENGINE=InnoDB;
ALTER TABLE `catalogindex_aggregation_to_tag` ENGINE=InnoDB;
ALTER TABLE `catalogindex_eav` ENGINE=InnoDB;
ALTER TABLE `catalogindex_minimal_price` ENGINE=InnoDB;
ALTER TABLE `catalogindex_price` ENGINE=InnoDB;
ALTER TABLE `cataloginventory_stock` ENGINE=InnoDB;
ALTER TABLE `cataloginventory_stock_item` ENGINE=InnoDB;
ALTER TABLE `catalogrule` ENGINE=InnoDB;
ALTER TABLE `catalogrule_affected_product` ENGINE=InnoDB;
ALTER TABLE `catalogrule_product` ENGINE=InnoDB;
ALTER TABLE `catalogrule_product_price` ENGINE=InnoDB;
ALTER TABLE `catalogsearch_fulltext` ENGINE=MyISAM;
ALTER TABLE `catalogsearch_query` ENGINE=InnoDB;
ALTER TABLE `catalogsearch_result` ENGINE=InnoDB;
ALTER TABLE `checkout_agreement` ENGINE=InnoDB;
ALTER TABLE `checkout_agreement_store` ENGINE=InnoDB;
ALTER TABLE `chronopay_api_debug` ENGINE=InnoDB;
ALTER TABLE `cms_block` ENGINE=InnoDB;
ALTER TABLE `cms_block_store` ENGINE=InnoDB;
ALTER TABLE `cms_page` ENGINE=InnoDB;
ALTER TABLE `cms_page_store` ENGINE=InnoDB;
ALTER TABLE `core_config_data` ENGINE=InnoDB;
ALTER TABLE `core_email_template` ENGINE=InnoDB;
ALTER TABLE `core_flag` ENGINE=InnoDB;
ALTER TABLE `core_layout_link` ENGINE=InnoDB;
ALTER TABLE `core_layout_update` ENGINE=InnoDB;
ALTER TABLE `core_resource` ENGINE=InnoDB;
ALTER TABLE `core_session` ENGINE=InnoDB;
ALTER TABLE `core_store` ENGINE=InnoDB;
ALTER TABLE `core_store_group` ENGINE=InnoDB;
ALTER TABLE `core_translate` ENGINE=InnoDB;
ALTER TABLE `core_url_rewrite` ENGINE=InnoDB;
ALTER TABLE `core_website` ENGINE=InnoDB;
ALTER TABLE `cron_schedule` ENGINE=InnoDB;
ALTER TABLE `customer_address_entity` ENGINE=InnoDB;
ALTER TABLE `customer_address_entity_datetime` ENGINE=InnoDB;
ALTER TABLE `customer_address_entity_decimal` ENGINE=InnoDB;
ALTER TABLE `customer_address_entity_int` ENGINE=InnoDB;
ALTER TABLE `customer_address_entity_text` ENGINE=InnoDB;
ALTER TABLE `customer_address_entity_varchar` ENGINE=InnoDB;
ALTER TABLE `customer_entity` ENGINE=InnoDB;
ALTER TABLE `customer_entity_datetime` ENGINE=InnoDB;
ALTER TABLE `customer_entity_decimal` ENGINE=InnoDB;
ALTER TABLE `customer_entity_int` ENGINE=InnoDB;
ALTER TABLE `customer_entity_text` ENGINE=InnoDB;
ALTER TABLE `customer_entity_varchar` ENGINE=InnoDB;
ALTER TABLE `customer_group` ENGINE=InnoDB;
ALTER TABLE `cybermut_api_debug` ENGINE=InnoDB;
ALTER TABLE `cybersource_api_debug` ENGINE=InnoDB;
ALTER TABLE `dataflow_batch` ENGINE=InnoDB;
ALTER TABLE `dataflow_batch_export` ENGINE=InnoDB;
ALTER TABLE `dataflow_batch_import` ENGINE=InnoDB;
ALTER TABLE `dataflow_import_data` ENGINE=InnoDB;
ALTER TABLE `dataflow_profile` ENGINE=InnoDB;
ALTER TABLE `dataflow_profile_history` ENGINE=InnoDB;
ALTER TABLE `dataflow_session` ENGINE=InnoDB;
ALTER TABLE `design_change` ENGINE=InnoDB;
ALTER TABLE `directory_country` ENGINE=InnoDB;
ALTER TABLE `directory_country_format` ENGINE=InnoDB;
ALTER TABLE `directory_country_region` ENGINE=InnoDB;
ALTER TABLE `directory_country_region_name` ENGINE=InnoDB;
ALTER TABLE `directory_currency_rate` ENGINE=InnoDB;
ALTER TABLE `downloadable_link` ENGINE=InnoDB;
ALTER TABLE `downloadable_link_price` ENGINE=InnoDB;
ALTER TABLE `downloadable_link_purchased` ENGINE=InnoDB;
ALTER TABLE `downloadable_link_purchased_item` ENGINE=InnoDB;
ALTER TABLE `downloadable_link_title` ENGINE=InnoDB;
ALTER TABLE `downloadable_sample` ENGINE=InnoDB;
ALTER TABLE `downloadable_sample_title` ENGINE=InnoDB;
ALTER TABLE `eav_attribute` ENGINE=InnoDB;
ALTER TABLE `eav_attribute_group` ENGINE=InnoDB;
ALTER TABLE `eav_attribute_option` ENGINE=InnoDB;
ALTER TABLE `eav_attribute_option_value` ENGINE=InnoDB;
ALTER TABLE `eav_attribute_set` ENGINE=InnoDB;
ALTER TABLE `eav_entity` ENGINE=InnoDB;
ALTER TABLE `eav_entity_attribute` ENGINE=InnoDB;
ALTER TABLE `eav_entity_datetime` ENGINE=InnoDB;
ALTER TABLE `eav_entity_decimal` ENGINE=InnoDB;
ALTER TABLE `eav_entity_int` ENGINE=InnoDB;
ALTER TABLE `eav_entity_store` ENGINE=InnoDB;
ALTER TABLE `eav_entity_text` ENGINE=InnoDB;
ALTER TABLE `eav_entity_type` ENGINE=InnoDB;
ALTER TABLE `eav_entity_varchar` ENGINE=InnoDB;
ALTER TABLE `eway_api_debug` ENGINE=InnoDB;
ALTER TABLE `flo2cash_api_debug` ENGINE=InnoDB;
ALTER TABLE `gift_message` ENGINE=InnoDB;
ALTER TABLE `googlebase_attributes` ENGINE=InnoDB;
ALTER TABLE `googlebase_items` ENGINE=InnoDB;
ALTER TABLE `googlebase_types` ENGINE=InnoDB;
ALTER TABLE `googlecheckout_api_debug` ENGINE=InnoDB;
ALTER TABLE `googleoptimizer_code` ENGINE=InnoDB;
ALTER TABLE `ideal_api_debug` ENGINE=InnoDB;
ALTER TABLE `log_customer` ENGINE=MyISAM;
ALTER TABLE `log_quote` ENGINE=MyISAM;
ALTER TABLE `log_summary` ENGINE=MyISAM;
ALTER TABLE `log_summary_type` ENGINE=MyISAM;
ALTER TABLE `log_url` ENGINE=MyISAM;
ALTER TABLE `log_url_info` ENGINE=MyISAM;
ALTER TABLE `log_visitor` ENGINE=MyISAM;
ALTER TABLE `log_visitor_info` ENGINE=MyISAM;
ALTER TABLE `newsletter_problem` ENGINE=InnoDB;
ALTER TABLE `newsletter_queue` ENGINE=InnoDB;
ALTER TABLE `newsletter_queue_link` ENGINE=InnoDB;
ALTER TABLE `newsletter_queue_store_link` ENGINE=InnoDB;
ALTER TABLE `newsletter_subscriber` ENGINE=InnoDB;
ALTER TABLE `newsletter_template` ENGINE=InnoDB;
ALTER TABLE `oscommerce_import` ENGINE=InnoDB;
ALTER TABLE `oscommerce_import_type` ENGINE=InnoDB;
ALTER TABLE `oscommerce_orders` ENGINE=MyISAM;
ALTER TABLE `oscommerce_orders_products` ENGINE=MyISAM;
ALTER TABLE `oscommerce_orders_status_history` ENGINE=MyISAM;
ALTER TABLE `oscommerce_orders_total` ENGINE=MyISAM;
ALTER TABLE `oscommerce_ref` ENGINE=InnoDB;
ALTER TABLE `paybox_api_debug` ENGINE=InnoDB;
ALTER TABLE `paybox_question_number` ENGINE=InnoDB;
ALTER TABLE `paygate_authorizenet_debug` ENGINE=MyISAM;
ALTER TABLE `paypal_api_debug` ENGINE=MyISAM;
ALTER TABLE `paypaluk_api_debug` ENGINE=InnoDB;
ALTER TABLE `poll` ENGINE=InnoDB;
ALTER TABLE `poll_answer` ENGINE=InnoDB;
ALTER TABLE `poll_store` ENGINE=InnoDB;
ALTER TABLE `poll_vote` ENGINE=InnoDB;
ALTER TABLE `product_alert_price` ENGINE=InnoDB;
ALTER TABLE `product_alert_stock` ENGINE=MyISAM;
ALTER TABLE `protx_api_debug` ENGINE=InnoDB;
ALTER TABLE `rating` ENGINE=InnoDB;
ALTER TABLE `rating_entity` ENGINE=InnoDB;
ALTER TABLE `rating_option` ENGINE=InnoDB;
ALTER TABLE `rating_option_vote` ENGINE=InnoDB;
ALTER TABLE `rating_option_vote_aggregated` ENGINE=InnoDB;
ALTER TABLE `rating_store` ENGINE=InnoDB;
ALTER TABLE `rating_title` ENGINE=InnoDB;
ALTER TABLE `report_event` ENGINE=InnoDB;
ALTER TABLE `report_event_types` ENGINE=InnoDB;
ALTER TABLE `review` ENGINE=InnoDB;
ALTER TABLE `review_detail` ENGINE=InnoDB;
ALTER TABLE `review_entity` ENGINE=InnoDB;
ALTER TABLE `review_entity_summary` ENGINE=InnoDB;
ALTER TABLE `review_status` ENGINE=InnoDB;
ALTER TABLE `review_store` ENGINE=InnoDB;
ALTER TABLE `sales_flat_order_item` ENGINE=InnoDB;
ALTER TABLE `sales_flat_quote` ENGINE=InnoDB;
ALTER TABLE `sales_flat_quote_address` ENGINE=InnoDB;
ALTER TABLE `sales_flat_quote_address_item` ENGINE=InnoDB;
ALTER TABLE `sales_flat_quote_item` ENGINE=InnoDB;
ALTER TABLE `sales_flat_quote_item_option` ENGINE=InnoDB;
ALTER TABLE `sales_flat_quote_payment` ENGINE=InnoDB;
ALTER TABLE `sales_flat_quote_shipping_rate` ENGINE=InnoDB;
ALTER TABLE `sales_order` ENGINE=InnoDB;
ALTER TABLE `sales_order_datetime` ENGINE=InnoDB;
ALTER TABLE `sales_order_decimal` ENGINE=InnoDB;
ALTER TABLE `sales_order_entity` ENGINE=InnoDB;
ALTER TABLE `sales_order_entity_datetime` ENGINE=InnoDB;
ALTER TABLE `sales_order_entity_decimal` ENGINE=InnoDB;
ALTER TABLE `sales_order_entity_int` ENGINE=InnoDB;
ALTER TABLE `sales_order_entity_text` ENGINE=InnoDB;
ALTER TABLE `sales_order_entity_varchar` ENGINE=InnoDB;
ALTER TABLE `sales_order_int` ENGINE=InnoDB;
ALTER TABLE `sales_order_tax` ENGINE=InnoDB;
ALTER TABLE `sales_order_text` ENGINE=InnoDB;
ALTER TABLE `sales_order_varchar` ENGINE=InnoDB;
ALTER TABLE `salesrule` ENGINE=InnoDB;
ALTER TABLE `salesrule_customer` ENGINE=InnoDB;
ALTER TABLE `sendfriend_log` ENGINE=MyISAM;
ALTER TABLE `shipping_tablerate` ENGINE=InnoDB;
ALTER TABLE `sitemap` ENGINE=InnoDB;
ALTER TABLE `strikeiron_tax_rate` ENGINE=InnoDB;
ALTER TABLE `tag` ENGINE=InnoDB;
ALTER TABLE `tag_relation` ENGINE=InnoDB;
ALTER TABLE `tag_summary` ENGINE=InnoDB;
ALTER TABLE `tax_calculation` ENGINE=InnoDB;
ALTER TABLE `tax_calculation_rate` ENGINE=InnoDB;
ALTER TABLE `tax_calculation_rate_title` ENGINE=InnoDB;
ALTER TABLE `tax_calculation_rule` ENGINE=InnoDB;
ALTER TABLE `tax_class` ENGINE=InnoDB;
ALTER TABLE `weee_discount` ENGINE=InnoDB;
ALTER TABLE `weee_tax` ENGINE=InnoDB;
ALTER TABLE `wishlist` ENGINE=InnoDB;
ALTER TABLE `wishlist_item` ENGINE=InnoDB;

This is the default table setup for a fresh install of Magento. If you run an older version of Magento, some of these tables might not exist, so you will need to modify the above and remove any tables that aren’t in your database.

For a complete list of tables that should not be using InnoDB, please see this article.




 

Magento 2 GitHub Repository

Magento Job Board - Some sort of tag line goes here

Latest Posts| View all Jobs