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.