I have this strange issue that because of my limited knowledge about Mysql I cannot understand, I will really appreciate if somebody can enlighten me and point me to how to debug the issue.
Scenario:
I have a Live Magento store and a local replication of it (same code/DB)
On both enviroments Apache
service and MySql
service are on same machine (the main difference is than on the live server the site is inside a chroot).
I have replicated the MySql
live configuration on my local environment but there are still 2 main diff:
MySql
version- this option
table_cache = 2048
is not working on my local
Live MySql
:
mysql Ver 14.14 Distrib 5.5.40, for debian-linux-gnu (x86_64) using readline 6.2
Local MySql
:
mysql Ver 14.14 Distrib 5.6.25, for debian-linux-gnu (x86_64) using EditLine wrapper
The issue
I have this query that retrieve products and do some joins (see below).
My local machine is much slower than live server anyway the same SQL on my local takes about 4 seconds on live server it basically nerver ends:
SHOW PROCESSLIST
say the query is instate
sending data
I have found out that the main bottleneck is the join between catalog_product_entity
(rows 21,113) on the core_url_rewrite
table (rows 892,997).
SELECT
`main_table`.`entity_id`,
`ur`.`request_path` AS `url`
FROM `catalog_product_entity` AS `main_table`
INNER JOIN `catalog_product_website` AS `w`
ON main_table.entity_id = w.product_id
LEFT JOIN `core_url_rewrite` AS `ur`
ON main_table.entity_id = ur.product_id AND ur.category_id IS NULL AND ur.store_id = 2 AND ur.is_system = 1
WHERE (w.website_id = '1')
I have tried to limit
the query, the result is that if I limit
it to 10 items the first 3/4 pages can be retrieved while after that it start to hang. ( every time I increment the limit
by 10, or the number of pages retrieved, the query take 10/20 sec. more on the live server)
MORE INFOS
Runnig the SQL with EXPLAIN
command:
Even if the table structure is the same, I can see some difference here but I'm not sure if they are meaningful info:
ref
andextra
value are not the same on live/local for tablecore_url_rewrite
LIVE SERVER
[id] => 1
[select_type] => SIMPLE
[table] => w
[type] => ref
[possible_keys] => PRIMARY,IDX_CATALOG_PRODUCT_WEBSITE_WEBSITE_ID
[key] => IDX_CATALOG_PRODUCT_WEBSITE_WEBSITE_ID
[key_len] => 2
[ref] => const
[rows] => 10868
[Extra] => Using index
[id] => 1
[select_type] => SIMPLE
[table] => main_table
[type] => eq_ref
[possible_keys] => PRIMARY
[key] => PRIMARY
[key_len] => 4
[ref] => LIVE.w.product_id
[rows] => 1
[Extra] =>
[id] => 1
[select_type] => SIMPLE
[table] => ur
[type] => ref
[possible_keys] => IDX_CORE_URL_REWRITE_STORE_ID,FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID,FK_CORE_URL_REWRITE_PRODUCT_ID_CATALOG_CATEGORY_ENTITY_ENTITY_ID
[key] => FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID
[key_len] => 5
[ref] => const
[rows] => 10
[Extra] =>
LOCAL
[id] => 1
[select_type] => SIMPLE
[table] => w
[type] => ref
[possible_keys] => PRIMARY,IDX_CATALOG_PRODUCT_WEBSITE_WEBSITE_ID
[key] => IDX_CATALOG_PRODUCT_WEBSITE_WEBSITE_ID
[key_len] => 2
[ref] => const
[rows] => 10622
[Extra] => Using index
[id] => 1
[select_type] => SIMPLE
[table] => main_table
[type] => eq_ref
[possible_keys] => PRIMARY
[key] => PRIMARY
[key_len] => 4
[ref] => PJ_LOCAL.w.product_id
[rows] => 1
[Extra] =>
[id] => 1
[select_type] => SIMPLE
[table] => ur
[type] => ref
[possible_keys] => IDX_CORE_URL_REWRITE_STORE_ID,FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID,FK_CORE_URL_REWRITE_PRODUCT_ID_CATALOG_CATEGORY_ENTITY_ENTITY_ID
[key] => FK_CORE_URL_REWRITE_PRODUCT_ID_CATALOG_CATEGORY_ENTITY_ENTITY_ID
[key_len] => 5
[ref] => PJ_LOCAL.w.product_id
[rows] => 22
[Extra] => Using where
INDEX INFO
TABLE catalog_product_entity
[Table] => catalog_product_entity
[Non_unique] => 0
[Key_name] => PRIMARY
[Seq_in_index] => 1
[Column_name] => entity_id
[Collation] => A
[Cardinality] => 21455
[Sub_part] =>
[Packed] =>
[Null] =>
[Index_type] => BTREE
[Comment] =>
[Index_comment] =>
[Table] => catalog_product_entity
[Non_unique] => 1
[Key_name] => IDX_CATALOG_PRODUCT_ENTITY_ENTITY_TYPE_ID
[Seq_in_index] => 1
[Column_name] => entity_type_id
[Collation] => A
[Cardinality] => 2
[Sub_part] =>
[Packed] =>
[Null] =>
[Index_type] => BTREE
[Comment] =>
[Index_comment] =>
[Table] => catalog_product_entity
[Non_unique] => 1
[Key_name] => IDX_CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID
[Seq_in_index] => 1
[Column_name] => attribute_set_id
[Collation] => A
[Cardinality] => 16
[Sub_part] =>
[Packed] =>
[Null] =>
[Index_type] => BTREE
[Comment] =>
[Index_comment] =>
[Table] => catalog_product_entity
[Non_unique] => 1
[Key_name] => IDX_CATALOG_PRODUCT_ENTITY_SKU
[Seq_in_index] => 1
[Column_name] => sku
[Collation] => A
[Cardinality] => 21455
[Sub_part] =>
[Packed] =>
[Null] => YES
[Index_type] => BTREE
[Comment] =>
[Index_comment] =>
TABLE core_url_rewrite
[Table] => core_url_rewrite
[Non_unique] => 0
[Key_name] => PRIMARY
[Seq_in_index] => 1
[Column_name] => url_rewrite_id
[Collation] => A
[Cardinality] => 851468
[Sub_part] =>
[Packed] =>
[Null] =>
[Index_type] => BTREE
[Comment] =>
[Index_comment] =>
[Table] => core_url_rewrite
[Non_unique] => 0
[Key_name] => UNQ_CORE_URL_REWRITE_REQUEST_PATH_STORE_ID
[Seq_in_index] => 1
[Column_name] => request_path
[Collation] => A
[Cardinality] => 851468
[Sub_part] =>
[Packed] =>
[Null] => YES
[Index_type] => BTREE
[Comment] =>
[Index_comment] =>
[Table] => core_url_rewrite
[Non_unique] => 0
[Key_name] => UNQ_CORE_URL_REWRITE_REQUEST_PATH_STORE_ID
[Seq_in_index] => 2
[Column_name] => store_id
[Collation] => A
[Cardinality] => 851468
[Sub_part] =>
[Packed] =>
[Null] =>
[Index_type] => BTREE
[Comment] =>
[Index_comment] =>
[Table] => core_url_rewrite
[Non_unique] => 0
[Key_name] => UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID
[Seq_in_index] => 1
[Column_name] => id_path
[Collation] => A
[Cardinality] => 851468
[Sub_part] =>
[Packed] =>
[Null] => YES
[Index_type] => BTREE
[Comment] =>
[Index_comment] =>
[Table] => core_url_rewrite
[Non_unique] => 0
[Key_name] => UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID
[Seq_in_index] => 2
[Column_name] => is_system
[Collation] => A
[Cardinality] => 851468
[Sub_part] =>
[Packed] =>
[Null] => YES
[Index_type] => BTREE
[Comment] =>
[Index_comment] =>
[Table] => core_url_rewrite
[Non_unique] => 0
[Key_name] => UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID
[Seq_in_index] => 3
[Column_name] => store_id
[Collation] => A
[Cardinality] => 851468
[Sub_part] =>
[Packed] =>
[Null] =>
[Index_type] => BTREE
[Comment] =>
[Index_comment] =>
[Table] => core_url_rewrite
[Non_unique] => 1
[Key_name] => IDX_CORE_URL_REWRITE_TARGET_PATH_STORE_ID
[Seq_in_index] => 1
[Column_name] => target_path
[Collation] => A
[Cardinality] => 85146
[Sub_part] =>
[Packed] =>
[Null] => YES
[Index_type] => BTREE
[Comment] =>
[Index_comment] =>
[Table] => core_url_rewrite
[Non_unique] => 1
[Key_name] => IDX_CORE_URL_REWRITE_TARGET_PATH_STORE_ID
[Seq_in_index] => 2
[Column_name] => store_id
[Collation] => A
[Cardinality] => 85146
[Sub_part] =>
[Packed] =>
[Null] =>
[Index_type] => BTREE
[Comment] =>
[Index_comment] =>
[Table] => core_url_rewrite
[Non_unique] => 1
[Key_name] => IDX_CORE_URL_REWRITE_ID_PATH
[Seq_in_index] => 1
[Column_name] => id_path
[Collation] => A
[Cardinality] => 851468
[Sub_part] =>
[Packed] =>
[Null] => YES
[Index_type] => BTREE
[Comment] =>
[Index_comment] =>
[Table] => core_url_rewrite
[Non_unique] => 1
[Key_name] => IDX_CORE_URL_REWRITE_STORE_ID
[Seq_in_index] => 1
[Column_name] => store_id
[Collation] => A
[Cardinality] => 2
[Sub_part] =>
[Packed] =>
[Null] =>
[Index_type] => BTREE
[Comment] =>
[Index_comment] =>
[Table] => core_url_rewrite
[Non_unique] => 1
[Key_name] => FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID
[Seq_in_index] => 1
[Column_name] => category_id
[Collation] => A
[Cardinality] => 104
[Sub_part] =>
[Packed] =>
[Null] => YES
[Index_type] => BTREE
[Comment] =>
[Index_comment] =>
[Table] => core_url_rewrite
[Non_unique] => 1
[Key_name] => FK_CORE_URL_REWRITE_PRODUCT_ID_CATALOG_CATEGORY_ENTITY_ENTITY_ID
[Seq_in_index] => 1
[Column_name] => product_id
[Collation] => A
[Cardinality] => 38703
[Sub_part] =>
[Packed] =>
[Null] => YES
[Index_type] => BTREE
[Comment] =>
[Index_comment] =>
CREATE TABLE INFOS
[Table] => catalog_product_entity
[Create Table] => CREATE TABLE `catalog_product_entity` (
`entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
`entity_type_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type ID',
`attribute_set_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute Set ID',
`type_id` varchar(32) NOT NULL DEFAULT 'simple' COMMENT 'Type ID',
`sku` varchar(64) DEFAULT NULL COMMENT 'SKU',
`has_options` smallint(6) NOT NULL DEFAULT '0' COMMENT 'Has Options',
`required_options` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Required Options',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Creation Time',
`updated_at` timestamp NULL DEFAULT NULL COMMENT 'Update Time',
`absolute_price` tinyint(1) NOT NULL DEFAULT '0',
`absolute_weight` tinyint(1) NOT NULL DEFAULT '0',
`sku_policy` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`entity_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_ENTITY_TYPE_ID` (`entity_type_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID` (`attribute_set_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_SKU` (`sku`),
CONSTRAINT `FK_CAT_PRD_ENTT_ATTR_SET_ID_EAV_ATTR_SET_ATTR_SET_ID` FOREIGN KEY (`attribute_set_id`) REFERENCES `eav_attribute_set` (`attribute_set_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_CAT_PRD_ENTT_ENTT_TYPE_ID_EAV_ENTT_TYPE_ENTT_TYPE_ID` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=34952 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Table'
[Table] => core_url_rewrite
[Create Table] => CREATE TABLE `core_url_rewrite` (
`url_rewrite_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Rewrite Id',
`store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Id',
`id_path` varchar(255) DEFAULT NULL COMMENT 'Id Path',
`request_path` varchar(255) DEFAULT NULL COMMENT 'Request Path',
`target_path` varchar(255) DEFAULT NULL COMMENT 'Target Path',
`is_system` smallint(5) unsigned DEFAULT '1' COMMENT 'Defines is Rewrite System',
`options` varchar(255) DEFAULT NULL COMMENT 'Options',
`description` varchar(255) DEFAULT NULL COMMENT 'Deascription',
`category_id` int(10) unsigned DEFAULT NULL COMMENT 'Category Id',
`product_id` int(10) unsigned DEFAULT NULL COMMENT 'Product Id',
PRIMARY KEY (`url_rewrite_id`),
UNIQUE KEY `UNQ_CORE_URL_REWRITE_REQUEST_PATH_STORE_ID` (`request_path`,`store_id`),
UNIQUE KEY `UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID` (`id_path`,`is_system`,`store_id`),
KEY `IDX_CORE_URL_REWRITE_TARGET_PATH_STORE_ID` (`target_path`,`store_id`),
KEY `IDX_CORE_URL_REWRITE_ID_PATH` (`id_path`),
KEY `IDX_CORE_URL_REWRITE_STORE_ID` (`store_id`),
KEY `FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID` (`category_id`),
KEY `FK_CORE_URL_REWRITE_PRODUCT_ID_CATALOG_CATEGORY_ENTITY_ENTITY_ID` (`product_id`),
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,
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,
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
) ENGINE=InnoDB AUTO_INCREMENT=3143682 DEFAULT CHARSET=utf8 COMMENT='Url Rewrites'
Best Answer
This is probably related to indexes and Mysql 5.6 handles certain query differently:
as you can see your local version for table
core_url_rewrite
Explain shows [Extra] => Using where (your live does not).Your left join is probably causing the issue.
Try to run the query without the left join on both server and see if you get the same performance.
If you do, you know the left JOIN is cause Mysql to run the query differently on both server. (i'm assuming you have the same amount of data on both).
I don't see catalog_product_website table definition listed. Apparently it is using IDX_CATALOG_PRODUCT_WEBSITE_WEBSITE_ID (which includes the website_id).
Your left join
IS same as:
On live server, I would add 2 index on table :
and see which one gets picked up. (Mysql indexes can be tricky)
Optimizing the table could also fix the issue. Sometimes indexes are thrown out of place or get fragmented (if data is often updated / deleted).