enterprise-support team mailing list archive

[Muhammad Irfan <muhammad.irfan@xxxxxxxxxxx>]

I am not able to reproduce it with test data i used. Can you please provide some data for the involved tables to recheck it.
Below is in details.

        mysql [localhost] {msandbox} (test) > SHOW VARIABLES LIKE '%version%';
        +-------------------------+-----------------------------------------------------------------+
        | Variable_name           | Value                                                           |
        +-------------------------+-----------------------------------------------------------------+
        | innodb_version          | 5.6.15-rel63.0                                                  |
        | protocol_version        | 10                                                              |
        | slave_type_conversions  |                                                                 |
        | version                 | 5.6.15-rel63.0                                                  |
        | version_comment         | Percona Server with XtraDB (GPL), Release rel63.0, Revision 519 |
        | version_compile_machine | x86_64                                                          |
        | version_compile_os      | Linux                                                           |
        +-------------------------+-----------------------------------------------------------------+
         
        mysql> EXPLAIN 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\G
        *************************** 1. row ***************************
                   id: 1
          select_type: SIMPLE
                table: main_table
                 type: range
        possible_keys: IDX_CATALOG_CATEGORY_FLAT_STORE_1_PATH,IDX_CATALOG_CATEGORY_FLAT_STORE_1_LEVEL
                  key: IDX_CATALOG_CATEGORY_FLAT_STORE_1_PATH
              key_len: 767
                  ref: NULL
                 rows: 1
                Extra: Using index condition; 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: IDX_CORE_URL_REWRITE_STORE_ID
              key_len: 2
                  ref: const
                 rows: 1
                Extra: Using where
        2 rows in set (0.00 sec)
         
        Execution: Empty set (0.00 sec)
         
        mysql [localhost] {msandbox} (test) > show variables like '%version%';
        +-------------------------+-----------------------------------------------------------------+
        | Variable_name           | Value                                                           |
        +-------------------------+-----------------------------------------------------------------+
        | innodb_version          | 5.5.32-rel31.0                                                  |
        | protocol_version        | 10                                                              |
        | slave_type_conversions  |                                                                 |
        | version                 | 5.5.32-rel31.0                                                  |
        | version_comment         | Percona Server with XtraDB (GPL), Release rel31.0, Revision 549 |
        | version_compile_machine | x86_64                                                          |
        | version_compile_os      | Linux                                                           |
        +-------------------------+-----------------------------------------------------------------+
         
        mysql> EXPLAIN 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\G
        *************************** 1. row ***************************
                   id: 1
          select_type: SIMPLE
                table: main_table
                 type: range
        possible_keys: IDX_CATALOG_CATEGORY_FLAT_STORE_1_PATH,IDX_CATALOG_CATEGORY_FLAT_STORE_1_LEVEL
                  key: IDX_CATALOG_CATEGORY_FLAT_STORE_1_PATH
              key_len: 767
                  ref: NULL
                 rows: 1
                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: IDX_CORE_URL_REWRITE_STORE_ID
              key_len: 2
                  ref: const
                 rows: 1
                Extra:
        2 rows in set (0.03 sec)
         
        Execution: Empty set (0.00 sec)


** Changed in: percona-server/5.6
       Status: New => Invalid

-- 
You received this bug notification because you are a member of Ubuntu
Server/Client Support Team, which is subscribed to MySQL.
Matching subscriptions: Ubuntu Server/Client Support Team
https://bugs.launchpad.net/bugs/1285365

Title:
  Bug query optimizer when multiple conditions are included in ON
  section

To manage notifications about this bug go to:
https://bugs.launchpad.net/mysql-server/+bug/1285365/+subscriptions