Same SQL stall on server but work on local ( on slower machine )

Question

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 PROCESSLISTsay the query is in state 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 and extra value are not the same on live/local for table core_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'

Answer 1

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

 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 ...

IS same as:

 LEFT JOIN `core_url_rewrite` AS `ur`
    ON main_table.entity_id = ur.product_id 
WHERE ...
AND ur.category_id IS NULL AND ur.store_id = 2 AND ur.is_system = 1

On live server, I would add 2 index on table :

ALTER TABLE `core_url_rewrite` ADD INDEX `idx_storeid_is_system_cat_id` (`store_id`,`is_system`,`category_id`);
ALTER TABLE `core_url_rewrite` ADD INDEX `idx_storeid_is_system_prod_id` (`store_id`,`is_system`,`product_id`);

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).