Wednesday 28 August 2013

Speed up Magento 1.6 Checkout - 124 sec to 4 sec!

I had serious performance Issues with my Magento CE 1.6.
Reason: Wrong and missing indexes. Magento fixed them is 1.6.2.
Here are the relevant DB changes. You might check if it helps you.

I reduced the checkout time for 38 lines with 73 items total from 123 sec to 4 sec !!!!

I also posted to stackoverflow.com if you want to see more response to the script:

Here it comes:


/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/* Foreign Keys must be dropped in the target to ensure that requires changes can be done*/

ALTER TABLE `core_url_rewrite`
DROP FOREIGN KEY `FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID`  ,
DROP FOREIGN KEY `FK_CORE_URL_REWRITE_STORE_ID_CORE_STORE_STORE_ID`  ;

/* Alter table in target */
ALTER TABLE `catalog_category_entity_varchar`

DROP KEY `MAGMI_CCEV_OPTIMIZATION_IDX` ;

ALTER TABLE `catalog_product_bundle_stock_index`
DROP KEY `PRIMARY`, ADD PRIMARY KEY(`entity_id`,`website_id`,`stock_id`,`option_id`) ;

/* Alter table in target */

ALTER TABLE `catalog_product_entity_media_gallery`

DROP KEY `MAGMI_CPEM_OPTIMIZATION_IDX` ;

/* Alter table in target */

ALTER TABLE `core_url_rewrite`

CHANGE `id_path` `id_path` varchar(255)  COLLATE utf8_general_ci NULL COMMENT 'Id Path' after `store_id` ,

CHANGE `request_path` `request_path` varchar(255)  COLLATE utf8_general_ci NULL COMMENT 'Request Path' after `id_path` ,

CHANGE `target_path` `target_path` varchar(255)  COLLATE utf8_general_ci NULL COMMENT 'Target Path' after `request_path` ,

CHANGE `is_system` `is_system` smallint(5) unsigned   NULL DEFAULT 1 COMMENT 'Defines is Rewrite System' after `target_path` ,

CHANGE `options` `options` varchar(255)  COLLATE utf8_general_ci NULL COMMENT 'Options' after `is_system` ,

CHANGE `description` `description` varchar(255)  COLLATE utf8_general_ci NULL COMMENT 'Deascription' after `options` ,

CHANGE `category_id` `category_id` int(10) unsigned   NULL COMMENT 'Category Id' after `description` ,

CHANGE `product_id` `product_id` int(10) unsigned   NULL COMMENT 'Product Id' after `category_id` ,

ADD KEY `FK_CORE_URL_REWRITE_PRODUCT_ID_CATALOG_CATEGORY_ENTITY_ENTITY_ID`(`product_id`) ,

DROP KEY `FK_CORE_URL_REWRITE_PRODUCT_ID_CATALOG_PRODUCT_ENTITY_ENTITY_ID` ,

ADD CONSTRAINT `FK_CORE_URL_REWRITE_PRODUCT_ID_CATALOG_CATEGORY_ENTITY_ENTITY_ID`

FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE ,

DROP FOREIGN KEY `FK_CORE_URL_REWRITE_PRODUCT_ID_CATALOG_PRODUCT_ENTITY_ENTITY_ID`  ;

/* Alter table in target */

ALTER TABLE `eav_attribute`

DROP KEY `MAGMI_EA_CODE_OPTIMIZATION_IDX` ;

/* Alter table in target */

ALTER TABLE `eav_attribute_option_value`

DROP KEY `MAGMI_EAOV_OPTIMIZATION_IDX` ;

/* The foreign keys that were dropped are now re-created*/

ALTER TABLE `core_url_rewrite`

ADD CONSTRAINT `FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID`

FOREIGN KEY (`category_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE ,

ADD CONSTRAINT `FK_CORE_URL_REWRITE_STORE_ID_CORE_STORE_STORE_ID`

FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE ;


/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

No comments:

Post a Comment

Thanks for commenting. Your comments are reviewed before listed here.