MySQL Bugs: #72242: Bug query optimizer when multiple conditions are included in ON section

Bug #72242	Bug query optimizer when multiple conditions are included in ON section
Submitted:	4 Apr 2014 18:34	Modified:	10 Jul 2014 11:54
Reporter:	Jared Kipe	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.6.16, 5.6.17	OS:	Any (tested on linux)
Assigned to:		CPU Architecture:	Any
Tags:	join, Optimizer, where

Description:
Basically a query with a left join, and conditions keep any index from being used, thus resulting in terrible performance.

https://gist.github.com/jaredkipe/4b752bd4ceca710e5839

Rewriting the query (from Magento in /app/code/core/Mage/Catalog/Model/Resource/Category/Flat.php ) resulted in a 710ms -> 10ms response reduction on that query.

More information.
https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1285365?comments=all

How to repeat:
Run query with several 'where' style statements on join clause instead of where.  No indexes used.

Suggested fix:
In MySQL 5.5 same query uses indexes, I'd like the same in 5.6

Please provide the complete test case here (create table, queries, real result
and expected one). Thanks in advance.

mysql> SHOW CREATE TABLE catalog_category_flat_store_1\G
*************************** 1. row ***************************
       Table: catalog_category_flat_store_1
Create Table: CREATE TABLE `catalog_category_flat_store_1` (
  `entity_id` int(10) unsigned NOT NULL COMMENT 'entity_id',
  `parent_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'parent_id',
  `created_at` timestamp NULL DEFAULT NULL COMMENT 'created_at',
  `updated_at` timestamp NULL DEFAULT NULL COMMENT 'updated_at',
  `path` varchar(255) NOT NULL DEFAULT '' COMMENT 'path',
  `position` int(11) NOT NULL DEFAULT '0' COMMENT 'position',
  `level` int(11) NOT NULL DEFAULT '0' COMMENT 'level',
  `children_count` int(11) NOT NULL DEFAULT '0' COMMENT 'children_count',
  `store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Id',
  `all_children` text COMMENT 'All Children',
  `available_sort_by` text COMMENT 'Available Product Listing Sort By',
  `children` text COMMENT 'Children',
  `custom_apply_to_products` int(11) DEFAULT NULL COMMENT 'Apply To Products',
  `custom_design` varchar(255) DEFAULT NULL COMMENT 'Custom Design',
  `custom_design_from` datetime DEFAULT NULL COMMENT 'Active From',
  `custom_design_to` datetime DEFAULT NULL COMMENT 'Active To',
  `custom_layout_update` text COMMENT 'Custom Layout Update',
  `custom_use_parent_settings` int(11) DEFAULT NULL COMMENT 'Use Parent Category Settings',
  `default_sort_by` varchar(255) DEFAULT NULL COMMENT 'Default Product Listing Sort By',
  `description` text COMMENT 'Description',
  `display_mode` varchar(255) DEFAULT NULL COMMENT 'Display Mode',
  `filter_price_range` decimal(12,4) DEFAULT NULL COMMENT 'Layered Navigation Price Step',
  `image` varchar(255) DEFAULT NULL COMMENT 'Image',
  `include_in_menu` int(11) DEFAULT NULL COMMENT 'Include in Navigation Menu',
  `is_active` int(11) DEFAULT NULL COMMENT 'Is Active',
  `is_anchor` int(11) DEFAULT NULL COMMENT 'Is Anchor',
  `landing_page` int(11) DEFAULT NULL COMMENT 'CMS Block',
  `meta_description` text COMMENT 'Meta Description',
  `meta_keywords` text COMMENT 'Meta Keywords',
  `meta_title` varchar(255) DEFAULT NULL COMMENT 'Page Title',
  `name` varchar(255) DEFAULT NULL COMMENT 'Name',
  `page_layout` varchar(255) DEFAULT NULL COMMENT 'Page Layout',
  `path_in_store` text COMMENT 'Path In Store',
  `thumbnail` varchar(255) DEFAULT NULL COMMENT 'Thumbnail Image',
  `url_key` varchar(255) DEFAULT NULL COMMENT 'URL Key',
  `url_path` varchar(255) DEFAULT NULL COMMENT 'Url Path',
  PRIMARY KEY (`entity_id`),
  KEY `IDX_CATALOG_CATEGORY_FLAT_STORE_1_STORE_ID` (`store_id`),
  KEY `IDX_CATALOG_CATEGORY_FLAT_STORE_1_PATH` (`path`),
  KEY `IDX_CATALOG_CATEGORY_FLAT_STORE_1_LEVEL` (`level`),
  CONSTRAINT `FK_CATALOG_CATEGORY_FLAT_STORE_1_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_CTGR_FLAT_STORE_1_ENTT_ID_CAT_CTGR_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Category Flat (Store 1)'

mysql> SHOW CREATE TABLE core_url_rewrite\G
*************************** 1. row ***************************
       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',
  `category_id` int(10) unsigned DEFAULT NULL COMMENT 'Category Id',
  `product_id` int(10) unsigned DEFAULT NULL COMMENT 'Product 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',
  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_PRODUCT_ID_CATALOG_PRODUCT_ENTITY_ENTITY_ID` (`product_id`),
  KEY `FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID` (`category_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_PRODUCT_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=543286 DEFAULT CHARSET=utf8 COMMENT='Url Rewrites'

SELECT `main_table`.`entity_id`, main_table.`name`, main_table.`path`, `main_table`.`is_active`, `main_table`.`is_anchor`, `url_rewrite`.`request_path` FROM `catalog_category_flat_store_1` AS `main_table`
 LEFT JOIN `core_url_rewrite` AS `url_rewrite` ON url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 AND url_rewrite.product_id IS NULL AND url_rewrite.store_id='1' AND url_rewrite.id_path LIKE 'category/%' WHERE (main_table.is_active = '1') AND (main_table.include_in_menu = '1') AND (main_table.path like '1/3/%') AND (`level` <= 4) ORDER BY `main_table`.`position` ASC;
 
 Runtime: 84 rows in set (0.71 sec)

710ms response time is the 'problem'.  Mainly because there are keys available, but they are not used.
10ms or less is the 'expectation' of that query.
 
 EXPLAIN gives 
 *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: main_table
         type: ALL
possible_keys: IDX_CATALOG_CATEGORY_FLAT_STORE_1_PATH,IDX_CATALOG_CATEGORY_FLAT_STORE_1_LEVEL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 193
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: url_rewrite
         type: ref
possible_keys: UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID,IDX_CORE_URL_REWRITE_ID_PATH,IDX_CORE_URL_REWRITE_STORE_ID,FK_CORE_URL_REWRITE_PRODUCT_ID_CATALOG_PRODUCT_ENTITY_ENTITY_ID,FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID
          key: FK_CORE_URL_REWRITE_PRODUCT_ID_CATALOG_PRODUCT_ENTITY_ENTITY_ID
      key_len: 5
          ref: const
         rows: 10
        Extra: Using where
2 rows in set (0.00 sec)

Hello Jared,

Thank you for the table schema.
But I can not repeat described behavior on latest MySQL version 5.6.17/18 and with dummy data.  Please could you provide the part of the data which would help us to reproduce the issue at our end? Please make it as private once you upload/post here.

Thanks,
Umesh

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Problem exists in 5.6.17-0ubuntu0.14.04.1

Note that core_url_rewrite is substantially larger than what I am able to upload.

With this size core_url_rewite, the 'bad' query takes 0.04 sec (40ms) and when 'modified' to be better, it takes 0.00 sec.

Hello Jared,

Thank you for the table schema/sample data.
Verified as described.

Thanks,
Umesh

// 5.5.38 - similar performance as that of 5.6.17

mysql> SELECT `main_table`.`entity_id`, main_table.`name`, main_table.`path`, `main_table`.`is_active`, `main_table`.`is_anchor`, `url_rewrite`.`request_path` FROM `catalog_category_flat_store_1` AS `main_table` LEFT JOIN `core_url_rewrite` AS `url_rewrite` ON url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 AND url_rewrite.product_id IS NULL AND url_rewrite.store_id='1' AND url_rewrite.id_path LIKE 'category/%' WHERE (main_table.is_active = '1') AND (main_table.include_in_menu = '1') AND (main_table.path like '1/3/%') AND (`level` <= 4) ORDER BY `main_table`.`position` ASC;
+-----------+-------------------------------------+------------+-----------+-----------+---------------------------------------+
| entity_id | name                                | path       | is_active | is_anchor | request_path                          |
+-----------+-------------------------------------+------------+-----------+-----------+---------------------------------------+
..
+-----------+-------------------------------------+------------+-----------+-----------+---------------------------------------+
84 rows in set (0.75 sec)

mysql> SELECT
    ->   `main_table`.`entity_id`,
    ->   main_table.`name`,
    ->   main_table.`path`,
    ->   `main_table`.`is_active`,
    ->   `main_table`.`is_anchor`,
    ->   `url_rewrite`.`request_path`
    -> FROM `catalog_category_flat_store_1` AS `main_table`
    -> LEFT JOIN `core_url_rewrite` AS `url_rewrite`
    -> ON
    ->   url_rewrite.category_id=main_table.entity_id
    -> WHERE
    ->   url_rewrite.is_system=1 AND
    ->   url_rewrite.product_id IS NULL AND
    ->   url_rewrite.store_id='1' AND
    ->   url_rewrite.id_path LIKE 'category/%' AND
    ->   (main_table.is_active = '1') AND
    ->   (main_table.include_in_menu = '1') AND
    ->   (main_table.path like '1/3/%') AND
    ->   (`level` <= 4)
    -> ORDER BY `main_table`.`position` ASC;
+-----------+----------------------------+------------+-----------+-----------+---------------------------------------+
| entity_id | name                       | path       | is_active | is_anchor | request_path                          |
+-----------+----------------------------+------------+-----------+-----------+---------------------------------------+
+-----------+----------------------------+------------+-----------+-----------+---------------------------------------+
81 rows in set (0.02 sec)

The two queries are not equivalent.

By moving conditions on right-hand table like "url_rewrite.is_system=1" from ON clause to WHERE clause, query will not return rows from left-hand table (main_table) which does not have a match in right-hand table.  (Since columns from url_rewrite will be NULL in such cases).  In other words, the result of LEFT JOIN will not be different from result of INNER JOIN.  For INNER JOIN, MySQL Optimizer may use join orders that are not possible for LEFT JOIN where left-hand table must be processed before right-hand table.   Hence, INNER JOIN queries may have better performance than OUTER JOIN.

In other words, you should not use LEFT JOIN unless really need it.

I am not saying they are equivalent.

I'm saying this is a performance regression from say ... MySQL 5.5.33 where on my full database there is almost a factor of 100x difference in performance. (same query 5.6.17, same data)

In this case, I 'fixed' it locally by changing core Magento code to rework the query.  Not everyone can/will do that, so for them this core query will be orders of magnitude slower if they 'upgrade'.

I just want to chime in that we've had several customers who had to rewrite core Magento code after they upgraded to MySQL 5.6.17. It took a long time to figure out what was wrong, and I don't think I would've found it without this bug report.

It was of course working fine under MySQL 5.5, but we needed some of the replication features from 5.6.

What was originally described in this bug report is that INNER JOIN in 5.6 is faster than LEFT JOIN in 5.6.  This is not necessarily a bug since, as I noted, there a ways to execute INNER JOIN that are not available for LEFT JOIN.

However, the real problem seems to be a performance regression from 5.5 to 5.6.  That is, that the LEFT JOIN query executes faster in 5.5 than in 5.6.  

Please, could you provide the original query plan (EXPLAIN output) from MySQL 5.5?  Also, optimizer trace from MySQL 5.6 would be useful to identify why 5.6 uses a different query plan.

Waiting on reporter for the requested data - Changing status to "Need Feedback".

Thanks,
Umesh

We are also affected by this. The query stays for a long time in "Creating sort index" state.

Given the same table definitions as provided by Jared above.

This query takes 30 seconds on 5.6 while on 5.5 it takes 0.9 seconds.

SELECT SQL_NO_CACHE `main_table`.`entity_id`, main_table.`name`, main_table.`path`, `main_table`.`is_active`, `main_table`.`is_anchor`, `url_rewrite`.`request_path` FROM `catalog_category_flat_store_1` AS `main_table`  LEFT JOIN `core_url_rewrite` AS `url_rewrite` ON url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 AND url_rewrite.product_id IS NULL AND url_rewrite.store_id='1' AND url_rewrite.id_path LIKE 'category/%' WHERE (main_table.is_active = '1') AND (main_table.include_in_menu = '1') AND (main_table.path like '1/2/%') ORDER BY `main_table`.`position` ASC;

In MySQL 5.6 the query is in "Creating sort index" state for those 30 seconds. However there is a way to fix it and get the same results performance wise as MySQL 5.5.
When I use0 the Mysql5.6 optimizer flag "use_index_extensions=off", I get the same 5.5 ~0.9 second performance on this query in 5.6.

MySQL 5.6 Explain:

mysql> EXPLAIN EXTENDED SELECT `main_table`.`entity_id`, main_table.`name`, main_table.`path`, `main_table`.`is_active`, `main_table`.`is_anchor`, `url_rewrite`.`request_path` FROM `catalog_category_flat_store_1` AS `main_table`  LEFT JOIN `core_url_rewrite` AS `url_rewrite` ON url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 AND url_rewrite.product_id IS NULL AND url_rewrite.store_id='1' AND url_rewrite.id_path LIKE 'category/%' WHERE (main_table.is_active = '1') AND (main_table.include_in_menu = '1') AND (main_table.path like '1/2/%') ORDER BY `main_table`.`position` ASC\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: main_table
         type: ALL
possible_keys: IDX_CATALOG_CATEGORY_FLAT_STORE_1_PATH
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1826
     filtered: 97.10
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: url_rewrite
         type: ref
possible_keys: UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID,IDX_CORE_URL_REWRITE_ID_PATH,IDX_CORE_URL_REWRITE_STORE_ID,FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID,FK_CORE_URL_REWRITE_PRD_ID_CAT_CTGR_ENTT_ENTT_ID
          key: FK_CORE_URL_REWRITE_PRD_ID_CAT_CTGR_ENTT_ENTT_ID
      key_len: 5
          ref: const
         rows: 28
     filtered: 100.00
        Extra: Using where
2 rows in set, 1 warning (0.00 sec)

I see no mention that it uses extended indexes, yet if I disable the use_extented_indexes optimizer feature I receive the same performance for that query as in 5.5 (from 30 seconds to ~0.9 seconds).

The core_url_rewrite has about 300.000 rows, hence my hypothesis is that an index merge takes longer than to just answer the query without index_extension.

mysql> show create table core_url_rewrite\G;
*************************** 1. row ***************************
       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_PRD_ID_CAT_CTGR_ENTT_ENTT_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_PRD_ID_CAT_CTGR_ENTT_ENTT_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=15348498 DEFAULT CHARSET=utf8 COMMENT='Url Rewrites'

Here is the excerpt from the slow query log on 5.6 with index_extensions enabled:

# Time: 140610 11:32:47
# User@Host: root[root] @ localhost []  Id:    44
# Schema: customer_database  Last_errno: 0  Killed: 0
# Query_time: 29.301761  Lock_time: 0.000199  Rows_sent: 1761  Rows_examined: 22206224  Rows_affected: 0
# Bytes_sent: 168425
use customer_database;
SET timestamp=1402392767;
SELECT SQL_NO_CACHE `main_table`.`entity_id`, main_table.`name`, main_table.`path`, `main_table`.`is_active`, `main_table`.`is_anchor`, `url_rewrite`.`request_path` FROM `catalog_category_flat_store_1` AS `main_table`  LEFT JOIN `core_url_rewrite` AS `url_rewrite` ON url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 AND url_rewrite.product_id IS NULL AND url_rewrite.store_id='1' AND url_rewrite.id_path LIKE 'category/%' WHERE (main_table.is_active = '1') AND (main_table.include_in_menu = '1') AND (main_table.path like '1/2/%') ORDER BY `main_table`.`position` ASC;

Here is the excerpt from the slow query log on 5.6 with index_extensions disabled (with cold cache, hence why it took 5 secs):

# Time: 140610 11:53:46
# User@Host: root[root] @ localhost []  Id:    56
# Schema: customer_database  Last_errno: 0  Killed: 0
# Query_time: 5.277473  Lock_time: 0.000251  Rows_sent: 1761  Rows_examined: 514741  Rows_affected: 0
# Bytes_sent: 168425
use customer_database;
SET timestamp=1402394026;
SELECT SQL_NO_CACHE `main_table`.`entity_id`, main_table.`name`, main_table.`path`, `main_table`.`is_active`, `main_table`.`is_anchor`, `url_rewrite`.`request_path` FROM `catalog_category_flat_store_1` AS `main_table`  LEFT JOIN `core_url_rewrite` AS `url_rewrite` ON url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 AND url_rewrite.product_id IS NULL AND url_rewrite.store_id='1' AND url_rewrite.id_path LIKE 'category/%' WHERE (main_table.is_active = '1') AND (main_table.include_in_menu = '1') AND (main_table.path like '1/2/%') ORDER BY `main_table`.`position` ASC;

As you can see the performance of this query increases alot if use_index_extensions=off is used.

If you need any additional info please let me know!

For the record: 5.6.15 was used for this demo

Hi,

Thanks for following up on this bug report.

When I try both the original query and the latest query on MySQL 5.6.18 with the submitted example data, I get the same plan both with and without turning off use_index_extensions.  Can you post the output of EXPLAIN also with use_index_extensions=off?   Since I am not able to reproduce this myself, optimizer trace for both settings of use_index_extensions.  (See http://dev.mysql.com/doc/internals/en/optimizer-tracing-typical-usage.html)
An alternative is to upload a larger sample database where it is possible to reproduce this behavior.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".