List:Commits« Previous MessageNext Message »
From:Sergey Glukhov Date:October 24 2012 8:47am
Subject:bzr push into mysql-trunk branch (sergey.glukhov:4808 to 4809) WL#6266
View as plain text  
 4809 Sergey Glukhov	2012-10-24
      WL#6266 Make use of hidden key parts(addon)
      -- added new optimizer switch 'extended_secondary_keys'
      -- added uniqueness of extended secondary keys
     @ mysql-test/include/innodb_pk_extension.inc
        test case
     @ mysql-test/r/index_merge_myisam.result
        result fix, new optimizer switch
     @ mysql-test/r/innodb_pk_extension.result
        result fix
     @ mysql-test/r/mysqld--help-notwin.result
        result fix, new optimizer switch
     @ mysql-test/r/optimizer_switch.result
        result fix, new optimizer switch
     @ mysql-test/t/innodb_pk_extension.test
        test case update
     @ sql/handler.cc
        removed unnecessary change, gcov shows
        that this code is not covered by mtr
        at all.
     @ sql/opt_range.cc
        KEY::actual_key_parts is replaced with
        real_key_parts() function.
     @ sql/opt_sum.cc
        KEY::actual_key_parts is replaced with
        real_key_parts() function.
     @ sql/sql_optimizer.cc
        KEY::actual_key_parts is replaced with
        real_key_parts() function.
     @ sql/sql_planner.cc
        KEY::actual_key_parts is replaced with
        real_key_parts() function.
     @ sql/sql_priv.h
        added new optimize switch OPTIMIZER_SWITCH_EXTENDED_SECONDARY_KEYS
     @ sql/sql_select.cc
        create_ref_for_key():
        keyinfo->user_defined_key_parts is replaced with real_key_parts()
        keyinfo->flags is replaced with real_key_flags().
        added functions real_key_parts(), real_key_flags().
     @ sql/sql_select.h
        added functions real_key_parts(), real_key_flags().
     @ sql/sql_table.cc
        added initialization of
        keyinfo->actual_flags.
     @ sql/sql_tmp_table.cc
        added initialization of
        keyinfo->actual_flags, keyinfo->table.
     @ sql/structs.h
        updatet comments.
        added KEY::actual_flags.
     @ sql/sys_vars.cc
        added new optimize switch OPTIMIZER_SWITCH_EXTENDED_SECONDARY_KEYS
     @ sql/table.cc
        added initialization of KEY::actual_flags.
        calculate_key_len() function:
        key_info->actual_key_parts is replaced with real_key_parts().
        table->s->key_info is replaced with table->key_info,
        the only difference is that table->key_info struct
        has intialized KEY::table which is necessary for 
        real_key_parts(), real_key_flags() functions.

    added:
      mysql-test/include/innodb_pk_extension.inc
    modified:
      mysql-test/r/index_merge_myisam.result
      mysql-test/r/innodb_pk_extension.result
      mysql-test/r/mysqld--help-notwin.result
      mysql-test/r/optimizer_switch.result
      mysql-test/t/innodb_pk_extension.test
      sql/handler.cc
      sql/opt_range.cc
      sql/opt_sum.cc
      sql/sql_optimizer.cc
      sql/sql_planner.cc
      sql/sql_priv.h
      sql/sql_select.cc
      sql/sql_select.h
      sql/sql_table.cc
      sql/sql_tmp_table.cc
      sql/structs.h
      sql/sys_vars.cc
      sql/table.cc
 4808 Sergey Glukhov	2012-10-24
      WL#6266 Make use of hidden key parts
     @ mysql-test/suite/innodb/r/innodb_mysql.result
        result fix, more optimal plan.
     @ mysql-test/suite/opt_trace/r/range_no_prot.result
        result fix, conditions are extended with PK parts.
     @ mysql-test/suite/opt_trace/r/range_ps_prot.result
        result fix, conditions are extended with PK parts.
     @ sql/abstract_query_plan.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
     @ sql/event_db_repository.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
     @ sql/ha_partition.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
     @ sql/handler.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
        replaced key_info->key_parts with key_info->actual_key_parts.
     @ sql/item_func.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
     @ sql/item_subselect.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
     @ sql/key.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
     @ sql/log_event.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
     @ sql/log_event_old.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
     @ sql/opt_range.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
        SQL_SELECT::test_quick_select(), get_quick_select(),
        get_best_group_min_max(), get_field_keypart():
        replaced key_info->key_parts with key_info->actual_key_parts.
        is_key_scan_ror(), changed nparts argument to avoid type casting.
     @ sql/opt_sum.cc
        find_key_for_maxmin():
        replaced key_info->key_parts with key_info->actual_key_parts.
     @ sql/rpl_info_table_access.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
     @ sql/sql_handler.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
     @ sql/sql_optimizer.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
        make_join_statistics(), add_key_part():
        replaced key_info->key_parts with key_info->actual_key_parts.
     @ sql/sql_partition.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
     @ sql/sql_planner.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
        Optimize_table_order::best_access_path():
        replaced key_info->key_parts with key_info->actual_key_parts.
     @ sql/sql_select.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
     @ sql/sql_show.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
     @ sql/sql_table.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
        mysql_prepare_create_table():
        added initialization of key_info->actual_key_parts.
     @ sql/sql_tmp_table.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
        create_tmp_table(), create_duplicate_weedout_tmp_table():
        added initialization of key_info->actual_key_parts.
     @ sql/sql_update.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
     @ sql/sql_view.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
     @ sql/structs.h
        KEY::key_parts renamed to KEY::user_defiend_key_parts
        added actual_key_parts, hidden_key_parts to KEY struct.
     @ sql/table.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
        added new function add_pk_parts_to_sk().
        open_binary_frm() function
        added code which extends secondary keys for InnoDB tables.
        open_table_from_share() function
        added code which copies extended parts from TABLE_SHARE to TABLE struct.
        calculate_key_len()
        replaced key_info->key_parts with key_info->actual_key_parts.
        TABLE::add_tmp_key()
        added initialization of key_info->actual_key_parts.
     @ sql/unireg.cc
        KEY::key_parts renamed to KEY::user_defined_key_parts.
     @ storage/archive/ha_archive.cc
        KEY::key_parts renamed to KEY::user_defiend_key_parts
     @ storage/federated/ha_federated.cc
        KEY::key_parts renamed to KEY::user_defiend_key_parts
     @ storage/heap/ha_heap.cc
        KEY::key_parts renamed to KEY::user_defiend_key_parts
     @ storage/innobase/handler/handler0alter.cc
        KEY::key_parts renamed to KEY::user_defiend_key_parts
     @ storage/myisam/ha_myisam.cc
        KEY::key_parts renamed to KEY::user_defiend_key_parts

    added:
      mysql-test/r/innodb_pk_extension.result
      mysql-test/t/innodb_pk_extension.test
    modified:
      mysql-test/suite/innodb/r/innodb_mysql.result
      mysql-test/suite/opt_trace/r/range_no_prot.result
      mysql-test/suite/opt_trace/r/range_ps_prot.result
      sql/abstract_query_plan.cc
      sql/event_db_repository.cc
      sql/ha_partition.cc
      sql/handler.cc
      sql/item_func.cc
      sql/item_subselect.cc
      sql/key.cc
      sql/log_event.cc
      sql/log_event_old.cc
      sql/opt_explain.cc
      sql/opt_range.cc
      sql/opt_sum.cc
      sql/rpl_info_table_access.cc
      sql/sql_handler.cc
      sql/sql_optimizer.cc
      sql/sql_partition.cc
      sql/sql_planner.cc
      sql/sql_select.cc
      sql/sql_show.cc
      sql/sql_table.cc
      sql/sql_tmp_table.cc
      sql/sql_update.cc
      sql/sql_view.cc
      sql/structs.h
      sql/table.cc
      sql/unireg.cc
      storage/archive/ha_archive.cc
      storage/federated/ha_federated.cc
      storage/heap/ha_heap.cc
      storage/innobase/handler/ha_innodb.cc
      storage/innobase/handler/handler0alter.cc
      storage/myisam/ha_myisam.cc
=== added file 'mysql-test/include/innodb_pk_extension.inc'
--- a/mysql-test/include/innodb_pk_extension.inc	1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/innodb_pk_extension.inc	2012-10-24 08:43:38 +0000
@@ -0,0 +1,253 @@
+--source include/have_innodb.inc
+
+CREATE TABLE t1
+(
+ pk_1 INT,
+ pk_2 INT,
+ f1 DATETIME,
+ f2 INT,
+ PRIMARY KEY(pk_1, pk_2),
+ KEY k1(f1),
+ KEY k2(f2)
+) ENGINE = InnoDB;
+
+INSERT INTO t1 VALUES
+(1, 1, '2000-01-01', 1), (1, 2, '2000-01-02', 2), (1, 3, '2000-01-03', 3), (1, 4, '2000-01-04', 4), (1, 5, '2000-01-05', 5),
+(2, 1, '2000-01-01', 6), (2, 2, '2000-01-02', 7), (2, 3, '2000-01-03', 8), (2, 4, '2000-01-04', 9), (2, 5, '2000-01-05', 10),
+(3, 1, '2000-01-01', 11), (3, 2, '2000-01-02', 12), (3, 3, '2000-01-03', 13), (3, 4, '2000-01-04', 14), (3, 5, '2000-01-05', 15),
+(4, 1, '2000-01-01', 16), (4, 2, '2000-01-02', 17), (4, 3, '2000-01-03', 18), (4, 4, '2000-01-04', 19), (4, 5, '2000-01-05', 20),
+(5, 1, '2000-01-01', 21), (5, 2, '2000-01-02', 22), (5, 3, '2000-01-03', 23), (5, 4, '2000-01-04', 24), (5, 5, '2000-01-05', 25),
+(6, 1, '2000-01-06', 26), (6, 2, '2000-01-06', 27), (6, 3, '2000-01-03', 28), (6, 4, '2000-01-06', 29), (6, 5, '2000-01-06', 30),
+(7, 1, '2000-01-06', 31), (7, 2, '2000-01-06', 32), (7, 3, '2000-01-03', 33), (7, 4, '2000-01-06', 34), (7, 5, '2000-01-06', 35),
+(8, 1, '2000-01-06', 36), (8, 2, '2000-01-06', 37), (8, 3, '2000-01-03', 38), (8, 4, '2000-01-06', 39), (8, 5, '2000-01-06', 40),
+(9, 1, '2000-01-06', 41), (9, 2, '2000-01-06', 42), (9, 3, '2000-01-03', 43), (9, 4, '2000-01-06', 44), (9, 5, '2000-01-06', 45);
+
+INSERT INTO t1 VALUES
+(11, 1, '2000-01-01', 1), (11, 2, '2000-01-02', 2), (11, 3, '2000-01-03', 3), (11, 4, '2000-01-04', 4), (11, 5, '2000-01-05', 5),
+(12, 1, '2000-01-01', 6), (12, 2, '2000-01-02', 7), (12, 3, '2000-01-03', 8), (12, 4, '2000-01-04', 9), (12, 5, '2000-01-05', 10),
+(13, 1, '2000-01-01', 11), (13, 2, '2000-01-02', 12), (13, 3, '2000-01-03', 13), (13, 4, '2000-01-04', 14), (13, 5, '2000-01-05', 15),
+(14, 1, '2000-01-01', 16), (14, 2, '2000-01-02', 17), (14, 3, '2000-01-03', 18), (14, 4, '2000-01-04', 19), (14, 5, '2000-01-05', 20),
+(15, 1, '2000-01-01', 1), (15, 2, '2000-01-02', 2), (15, 3, '2000-01-03', 3), (15, 4, '2000-01-04', 4), (15, 5, '2000-01-05', 5),
+(16, 1, '2000-01-06', 6), (16, 2, '2000-01-06', 7), (16, 3, '2000-01-03', 8), (16, 4, '2000-01-06', 9), (16, 5, '2000-01-06', 10),
+(17, 1, '2000-01-06', 31), (17, 2, '2000-01-06', 32), (17, 3, '2000-01-03', 33), (17, 4, '2000-01-06', 34), (17, 5, '2000-01-06', 35),
+(18, 1, '2000-01-06', 36), (18, 2, '2000-01-06', 37), (18, 3, '2000-01-03', 38), (18, 4, '2000-01-06', 39), (18, 5, '2000-01-06', 40),
+(19, 1, '2000-01-06', 1), (19, 2, '2000-01-06', 2), (19, 3, '2000-01-03', 3), (19, 4, '2000-01-06', 4), (19, 5, '2000-01-06', 5);
+
+
+INSERT INTO t1 VALUES
+(21, 1, '2000-01-01', 1), (21, 2, '2000-01-02', 2), (31, 3, '2000-01-03', 3), (41, 4, '2000-01-04', 4), (51, 5, '2000-01-05', 5),
+(22, 1, '2000-01-01', 6), (22, 2, '2000-01-02', 7), (32, 3, '2000-01-03', 8), (42, 4, '2000-01-04', 9), (52, 5, '2000-01-05', 10),
+(23, 1, '2000-01-01', 11), (23, 2, '2000-01-02', 12), (33, 3, '2000-01-03', 13), (43, 4, '2000-01-04', 14), (53, 5, '2000-01-05', 15),
+(24, 1, '2000-01-01', 16), (24, 2, '2000-01-02', 17), (34, 3, '2000-01-03', 18), (44, 4, '2000-01-04', 19), (54, 5, '2000-01-05', 20),
+(25, 1, '2000-01-01', 1), (25, 2, '2000-01-02', 2), (35, 3, '2000-01-03', 3), (45, 4, '2000-01-04', 4), (55, 5, '2000-01-05', 5),
+(26, 1, '2000-01-06', 6), (26, 2, '2000-01-06', 7), (36, 3, '2000-01-03', 8), (46, 4, '2000-01-06', 9), (56, 5, '2000-01-06', 10),
+(27, 1, '2000-01-06', 31), (27, 2, '2000-01-06', 32), (37, 3, '2000-01-03', 33), (47, 4, '2000-01-06', 34), (57, 5, '2000-01-06', 35),
+(28, 1, '2000-01-06', 36), (28, 2, '2000-01-06', 37), (38, 3, '2000-01-03', 38), (48, 4, '2000-01-06', 39), (58, 5, '2000-01-06', 40),
+(29, 1, '2000-01-06', 1), (29, 2, '2000-01-06', 2), (39, 3, '2000-01-03', 3), (49, 4, '2000-01-06', 4), (59, 5, '2000-01-06', 5);
+
+INSERT INTO t1 SELECT pk_1 + 60, pk_2, f1, f2 FROM t1;
+INSERT INTO t1 SELECT pk_1 + 120, pk_2, f1, f2 FROM t1;
+INSERT INTO t1 SELECT pk_1 + 240, pk_2, f1, f2 FROM t1;
+INSERT INTO t1 SELECT pk_1, pk_2 + 10, f1, f2 FROM t1;
+
+ANALYZE TABLE t1;
+
+--echo #
+--echo # REF access optimization
+--echo #
+
+--replace_column 9 #
+EXPLAIN SELECT count(*) FROM t1 WHERE pk_1 = 3 and f1 = '2000-01-03';
+FLUSH STATUS;
+SELECT count(*) FROM t1 WHERE pk_1 = 3 and f1 = '2000-01-03';
+SHOW STATUS LIKE 'handler_read%';
+
+--replace_column 9 #
+EXPLAIN SELECT pk_2 FROM t1 WHERE pk_1 = 3 and f1 = '2000-01-03';
+FLUSH STATUS;
+SELECT pk_2 FROM t1 WHERE pk_1 = 3 and f1 = '2000-01-03';
+SHOW STATUS LIKE 'handler_read%';
+
+--echo #
+--echo # RANGE access optimization
+--echo #
+
+--replace_column 9 #
+EXPLAIN SELECT count(*) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
+FLUSH STATUS;
+SELECT count(*) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
+SHOW STATUS LIKE 'handler_read%';
+
+--replace_column 9 #
+EXPLAIN SELECT pk_1, pk_2 FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
+FLUSH STATUS;
+SELECT pk_1, pk_2 FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
+SHOW STATUS LIKE 'handler_read%';
+
+--echo #
+--echo # MAX/MIN optimization
+--echo #
+
+--replace_column 9 #
+EXPLAIN SELECT MIN(pk_1) FROM t1 WHERE f1 = '2000-01-03';
+FLUSH STATUS;
+SELECT MIN(pk_1) FROM t1 WHERE f1 = '2000-01-03';
+SHOW STATUS LIKE 'handler_read%';
+
+--replace_column 9 #
+EXPLAIN SELECT MIN(pk_1) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
+FLUSH STATUS;
+SELECT MIN(pk_1) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
+SHOW STATUS LIKE 'handler_read%';
+
+--replace_column 9 #
+EXPLAIN SELECT MAX(pk_1) FROM t1 WHERE f1 = '2000-01-03';
+FLUSH STATUS;
+SELECT MAX(pk_1) FROM t1 WHERE f1 = '2000-01-03';
+SHOW STATUS LIKE 'handler_read%';
+
+--replace_column 9 #
+EXPLAIN SELECT MAX(pk_1) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
+FLUSH STATUS;
+SELECT MAX(pk_1) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
+SHOW STATUS LIKE 'handler_read%';
+
+--echo #
+--echo # Loose index scan
+--echo #
+
+--replace_column 9 #
+EXPLAIN SELECT MIN(pk_1) FROM t1 WHERE f2 BETWEEN 13 AND 14 GROUP BY f2;
+FLUSH STATUS;
+SELECT MIN(pk_1) FROM t1 WHERE f2 BETWEEN 13 AND 14 GROUP BY f2;
+SHOW STATUS LIKE 'handler_read%';
+
+--replace_column 9 #
+EXPLAIN SELECT MIN(pk_1) FROM t1 WHERE f2 IN (1, 2) GROUP BY f2;
+FLUSH STATUS;
+SELECT MIN(pk_1) FROM t1 WHERE f2 IN (1, 2) GROUP BY f2;
+SHOW STATUS LIKE 'handler_read%';
+
+--echo #
+--echo # JOIN optimization
+--echo #
+
+--replace_column 9 #
+EXPLAIN SELECT count(*) FROM t1 AS t1 JOIN t1 AS t2
+ON t2.pk_1 = t1.pk_1 WHERE t1.f1 = '2000-01-03' AND t2.f1 = '2000-01-03';
+FLUSH STATUS;
+SELECT count(*) FROM t1 AS t1 JOIN t1 AS t2
+ON t2.pk_1 = t1.pk_1 WHERE t1.f1 = '2000-01-03' AND t2.f1 = '2000-01-03';
+SHOW STATUS LIKE 'handler_read%';
+
+--echo #
+--echo # Optimization of sorting
+--echo #
+
+--replace_column 9 #
+EXPLAIN SELECT f1, pk_1, pk_2 FROM t1 WHERE pk_1 = 3 AND f1 = '2000-01-03'
+ORDER BY pk_2 DESC LIMIT 5;
+FLUSH STATUS;
+SELECT f1, pk_1, pk_2 FROM t1 WHERE pk_1 = 3 AND f1 = '2000-01-03'
+ORDER BY pk_2 DESC LIMIT 5;
+SHOW STATUS LIKE 'handler_read%';
+
+--replace_column 9 #
+EXPLAIN SELECT f1, pk_1, pk_2 FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03'
+ORDER BY pk_2 DESC LIMIT 5;
+FLUSH STATUS;
+SELECT f1, pk_1, pk_2 FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03'
+ORDER BY pk_2 DESC LIMIT 5;
+SHOW STATUS LIKE 'handler_read%';
+
+DROP TABLE t1;
+
+--echo #
+--echo # Max key part limitation
+--echo #
+
+CREATE TABLE t1
+(
+  f1 INT, f2 INT, f3 INT, f4 INT, f5 INT, f6 INT, f7 INT, f8 INT, f9 INT, f10 INT,
+  f11 INT, f12 INT, f13 INT, f14 INT, f15 INT, f16 INT, f17 INT, f18 INT,
+  PRIMARY KEY (f1, f2, f3, f4, f5, f6, f7, f8, f9, f10),
+  KEY k1 (f11, f12, f13, f14, f15, f16, f17)
+) ENGINE = InnoDB;
+
+--replace_column 9 #
+EXPLAIN SELECT f17 FROM t1 FORCE INDEX (k1) WHERE
+f1 = 0 AND f2 = 0 AND f3 = 0 AND f4 = 0 AND f5 = 0 AND
+f6 = 0 AND f7 = 0 AND f8 = 0 AND f9 = 0 AND f10 = 0 AND
+f11 = 0 AND f12 = 0 AND f13 = 0 AND f14 = 0 AND
+f15 = 0 AND f16 = 0 AND f17 = 0;
+
+--replace_column 9 #
+EXPLAIN SELECT f17 FROM t1 FORCE INDEX (k1) WHERE
+f1 = 0 AND f2 = 0 AND f3 = 0 AND f4 = 0 AND f5 = 0 AND
+f6 = 0 AND f7 = 0 AND f8 = 0 AND f9 = 0 AND
+f11 = 0 AND f12 = 0 AND f13 = 0 AND f14 = 0 AND
+f15 = 0 AND f16 = 0 AND f17 = 0;
+
+DROP TABLE t1;
+
+--echo #
+--echo # Max key length limitation
+--echo #
+
+CREATE TABLE t1
+(
+  f1 VARCHAR(500), f2 VARCHAR(500), f3 VARCHAR(500),
+  f4 VARCHAR(500), f5 VARCHAR(500), f6 VARCHAR(500),
+  f7 VARCHAR(500),
+  PRIMARY KEY (f1, f2, f3, f4),
+  KEY k1 (f5, f6, f7)
+) ENGINE = InnoDB;
+
+--replace_column 9 #
+EXPLAIN SELECT f5 FROM t1 FORCE INDEX (k1) WHERE
+f1 = 'a' AND f2 = 'a' AND f3 = 'a' AND f4 = 'a' AND
+f5 = 'a' AND f6 = 'a' AND f7 = 'a';
+
+--replace_column 9 #
+EXPLAIN SELECT f5 FROM t1 FORCE INDEX (k1) WHERE
+f1 = 'a' AND f2 = 'a' AND f3 = 'a' AND
+f5 = 'a' AND f6 = 'a' AND f7 = 'a';
+
+--replace_column 9 #
+EXPLAIN SELECT f5 FROM t1 FORCE INDEX (k1) WHERE
+f1 = 'a' AND f2 = 'a' AND f4 = 'a' AND
+f5 = 'a' AND f6 = 'a' AND f7 = 'a';
+
+DROP TABLE t1;
+
+--echo #
+--echo # Unique extended key
+--echo #
+
+CREATE TABLE t1
+(
+  pk INT NOT NULL auto_increment,
+  f1 INT NOT NULL,
+  KEY (f1),
+  PRIMARY KEY (pk)
+) ENGINE = INNODB;
+
+CREATE TABLE t2
+(
+  f1 INT,
+  f2 INT
+) ENGINE = INNODB;
+
+INSERT INTO t1(f1) VALUES (1),(2);
+INSERT INTO t1(f1) SELECT f1 + 2 FROM t1;
+INSERT INTO t1(f1) SELECT f1 + 4 FROM t1;
+ANALYZE TABLE t1;
+
+INSERT INTO t2 VALUES (1,1), (2,2);
+
+EXPLAIN SELECT t2.f1 FROM t2 JOIN t1 IGNORE INDEX(primary) ON t2.f1 = t1.pk and t2.f2 = t1.f1;
+FLUSH STATUS;
+SELECT t2.f1 FROM t2 JOIN t1 IGNORE INDEX(primary) ON t2.f1 = t1.pk and t2.f2 = t1.f1;
+SHOW STATUS LIKE 'Handler_read%';
+
+DROP TABLE t1, t2;

=== modified file 'mysql-test/r/index_merge_myisam.result'
--- a/mysql-test/r/index_merge_myisam.result	2012-09-13 08:12:30 +0000
+++ b/mysql-test/r/index_merge_myisam.result	2012-10-24 08:43:38 +0000
@@ -1544,19 +1544,19 @@ DROP TABLE t1,t2;
 #
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,extended_secondary_keys=on
 set optimizer_switch='index_merge=off,index_merge_union=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on
+index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,extended_secondary_keys=on
 set optimizer_switch='index_merge_union=on';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on
+index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,extended_secondary_keys=on
 set optimizer_switch='default,index_merge_sort_union=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,extended_secondary_keys=on
 set optimizer_switch=4;
 set optimizer_switch=NULL;
 ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'NULL'
@@ -1582,21 +1582,21 @@ set optimizer_switch=default;
 set optimizer_switch='index_merge=off,index_merge_union=off,default';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on
+index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,extended_secondary_keys=on
 set optimizer_switch=default;
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,extended_secondary_keys=on
 set @@global.optimizer_switch=default;
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,extended_secondary_keys=on
 #
 # Check index_merge's @@optimizer_switch flags
 #
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,extended_secondary_keys=on
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1 (a int, b int, c int, filler char(100), 
@@ -1706,5 +1706,5 @@ id	select_type	table	type	possible_keys	
 set optimizer_switch=default;
 show variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on
+optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,extended_secondary_keys=on
 drop table t0, t1;

=== modified file 'mysql-test/r/innodb_pk_extension.result'
--- a/mysql-test/r/innodb_pk_extension.result	2012-10-24 07:49:04 +0000
+++ b/mysql-test/r/innodb_pk_extension.result	2012-10-24 08:43:38 +0000
@@ -1,6 +1,10 @@
 #
 #  WL#6266 Make use of hidden key parts
 #
+#
+# Optimizer switch extended_secondary_keys=on
+#
+set optimizer_switch= "extended_secondary_keys=on";
 CREATE TABLE t1
 (
 pk_1 INT,
@@ -347,3 +351,436 @@ f5 = 'a' AND f6 = 'a' AND f7 = 'a';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	k1	k1	2513	const,const,const,const,const	#	Using where; Using index
 DROP TABLE t1;
+#
+# Unique extended key
+#
+CREATE TABLE t1
+(
+pk INT NOT NULL auto_increment,
+f1 INT NOT NULL,
+KEY (f1),
+PRIMARY KEY (pk)
+) ENGINE = INNODB;
+CREATE TABLE t2
+(
+f1 INT,
+f2 INT
+) ENGINE = INNODB;
+INSERT INTO t1(f1) VALUES (1),(2);
+INSERT INTO t1(f1) SELECT f1 + 2 FROM t1;
+INSERT INTO t1(f1) SELECT f1 + 4 FROM t1;
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+INSERT INTO t2 VALUES (1,1), (2,2);
+EXPLAIN SELECT t2.f1 FROM t2 JOIN t1 IGNORE INDEX(primary) ON t2.f1 = t1.pk and t2.f2 = t1.f1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t1	eq_ref	f1	f1	8	test.t2.f2,test.t2.f1	1	Using index
+FLUSH STATUS;
+SELECT t2.f1 FROM t2 JOIN t1 IGNORE INDEX(primary) ON t2.f1 = t1.pk and t2.f2 = t1.f1;
+f1
+1
+2
+SHOW STATUS LIKE 'Handler_read%';
+Variable_name	Value
+Handler_read_first	1
+Handler_read_key	3
+Handler_read_last	0
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	3
+DROP TABLE t1, t2;
+#
+# Optimizer switch extended_secondary_keys=off
+#
+set optimizer_switch= "extended_secondary_keys=off";
+CREATE TABLE t1
+(
+pk_1 INT,
+pk_2 INT,
+f1 DATETIME,
+f2 INT,
+PRIMARY KEY(pk_1, pk_2),
+KEY k1(f1),
+KEY k2(f2)
+) ENGINE = InnoDB;
+INSERT INTO t1 VALUES
+(1, 1, '2000-01-01', 1), (1, 2, '2000-01-02', 2), (1, 3, '2000-01-03', 3), (1, 4, '2000-01-04', 4), (1, 5, '2000-01-05', 5),
+(2, 1, '2000-01-01', 6), (2, 2, '2000-01-02', 7), (2, 3, '2000-01-03', 8), (2, 4, '2000-01-04', 9), (2, 5, '2000-01-05', 10),
+(3, 1, '2000-01-01', 11), (3, 2, '2000-01-02', 12), (3, 3, '2000-01-03', 13), (3, 4, '2000-01-04', 14), (3, 5, '2000-01-05', 15),
+(4, 1, '2000-01-01', 16), (4, 2, '2000-01-02', 17), (4, 3, '2000-01-03', 18), (4, 4, '2000-01-04', 19), (4, 5, '2000-01-05', 20),
+(5, 1, '2000-01-01', 21), (5, 2, '2000-01-02', 22), (5, 3, '2000-01-03', 23), (5, 4, '2000-01-04', 24), (5, 5, '2000-01-05', 25),
+(6, 1, '2000-01-06', 26), (6, 2, '2000-01-06', 27), (6, 3, '2000-01-03', 28), (6, 4, '2000-01-06', 29), (6, 5, '2000-01-06', 30),
+(7, 1, '2000-01-06', 31), (7, 2, '2000-01-06', 32), (7, 3, '2000-01-03', 33), (7, 4, '2000-01-06', 34), (7, 5, '2000-01-06', 35),
+(8, 1, '2000-01-06', 36), (8, 2, '2000-01-06', 37), (8, 3, '2000-01-03', 38), (8, 4, '2000-01-06', 39), (8, 5, '2000-01-06', 40),
+(9, 1, '2000-01-06', 41), (9, 2, '2000-01-06', 42), (9, 3, '2000-01-03', 43), (9, 4, '2000-01-06', 44), (9, 5, '2000-01-06', 45);
+INSERT INTO t1 VALUES
+(11, 1, '2000-01-01', 1), (11, 2, '2000-01-02', 2), (11, 3, '2000-01-03', 3), (11, 4, '2000-01-04', 4), (11, 5, '2000-01-05', 5),
+(12, 1, '2000-01-01', 6), (12, 2, '2000-01-02', 7), (12, 3, '2000-01-03', 8), (12, 4, '2000-01-04', 9), (12, 5, '2000-01-05', 10),
+(13, 1, '2000-01-01', 11), (13, 2, '2000-01-02', 12), (13, 3, '2000-01-03', 13), (13, 4, '2000-01-04', 14), (13, 5, '2000-01-05', 15),
+(14, 1, '2000-01-01', 16), (14, 2, '2000-01-02', 17), (14, 3, '2000-01-03', 18), (14, 4, '2000-01-04', 19), (14, 5, '2000-01-05', 20),
+(15, 1, '2000-01-01', 1), (15, 2, '2000-01-02', 2), (15, 3, '2000-01-03', 3), (15, 4, '2000-01-04', 4), (15, 5, '2000-01-05', 5),
+(16, 1, '2000-01-06', 6), (16, 2, '2000-01-06', 7), (16, 3, '2000-01-03', 8), (16, 4, '2000-01-06', 9), (16, 5, '2000-01-06', 10),
+(17, 1, '2000-01-06', 31), (17, 2, '2000-01-06', 32), (17, 3, '2000-01-03', 33), (17, 4, '2000-01-06', 34), (17, 5, '2000-01-06', 35),
+(18, 1, '2000-01-06', 36), (18, 2, '2000-01-06', 37), (18, 3, '2000-01-03', 38), (18, 4, '2000-01-06', 39), (18, 5, '2000-01-06', 40),
+(19, 1, '2000-01-06', 1), (19, 2, '2000-01-06', 2), (19, 3, '2000-01-03', 3), (19, 4, '2000-01-06', 4), (19, 5, '2000-01-06', 5);
+INSERT INTO t1 VALUES
+(21, 1, '2000-01-01', 1), (21, 2, '2000-01-02', 2), (31, 3, '2000-01-03', 3), (41, 4, '2000-01-04', 4), (51, 5, '2000-01-05', 5),
+(22, 1, '2000-01-01', 6), (22, 2, '2000-01-02', 7), (32, 3, '2000-01-03', 8), (42, 4, '2000-01-04', 9), (52, 5, '2000-01-05', 10),
+(23, 1, '2000-01-01', 11), (23, 2, '2000-01-02', 12), (33, 3, '2000-01-03', 13), (43, 4, '2000-01-04', 14), (53, 5, '2000-01-05', 15),
+(24, 1, '2000-01-01', 16), (24, 2, '2000-01-02', 17), (34, 3, '2000-01-03', 18), (44, 4, '2000-01-04', 19), (54, 5, '2000-01-05', 20),
+(25, 1, '2000-01-01', 1), (25, 2, '2000-01-02', 2), (35, 3, '2000-01-03', 3), (45, 4, '2000-01-04', 4), (55, 5, '2000-01-05', 5),
+(26, 1, '2000-01-06', 6), (26, 2, '2000-01-06', 7), (36, 3, '2000-01-03', 8), (46, 4, '2000-01-06', 9), (56, 5, '2000-01-06', 10),
+(27, 1, '2000-01-06', 31), (27, 2, '2000-01-06', 32), (37, 3, '2000-01-03', 33), (47, 4, '2000-01-06', 34), (57, 5, '2000-01-06', 35),
+(28, 1, '2000-01-06', 36), (28, 2, '2000-01-06', 37), (38, 3, '2000-01-03', 38), (48, 4, '2000-01-06', 39), (58, 5, '2000-01-06', 40),
+(29, 1, '2000-01-06', 1), (29, 2, '2000-01-06', 2), (39, 3, '2000-01-03', 3), (49, 4, '2000-01-06', 4), (59, 5, '2000-01-06', 5);
+INSERT INTO t1 SELECT pk_1 + 60, pk_2, f1, f2 FROM t1;
+INSERT INTO t1 SELECT pk_1 + 120, pk_2, f1, f2 FROM t1;
+INSERT INTO t1 SELECT pk_1 + 240, pk_2, f1, f2 FROM t1;
+INSERT INTO t1 SELECT pk_1, pk_2 + 10, f1, f2 FROM t1;
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+#
+# REF access optimization
+#
+EXPLAIN SELECT count(*) FROM t1 WHERE pk_1 = 3 and f1 = '2000-01-03';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	PRIMARY,k1	PRIMARY	4	const	#	Using where
+FLUSH STATUS;
+SELECT count(*) FROM t1 WHERE pk_1 = 3 and f1 = '2000-01-03';
+count(*)
+2
+SHOW STATUS LIKE 'handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	1
+Handler_read_last	0
+Handler_read_next	10
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	0
+EXPLAIN SELECT pk_2 FROM t1 WHERE pk_1 = 3 and f1 = '2000-01-03';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	PRIMARY,k1	PRIMARY	4	const	#	Using where
+FLUSH STATUS;
+SELECT pk_2 FROM t1 WHERE pk_1 = 3 and f1 = '2000-01-03';
+pk_2
+3
+13
+SHOW STATUS LIKE 'handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	1
+Handler_read_last	0
+Handler_read_next	10
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	0
+#
+# RANGE access optimization
+#
+EXPLAIN SELECT count(*) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY,k1	PRIMARY	4	NULL	#	Using where
+FLUSH STATUS;
+SELECT count(*) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
+count(*)
+6
+SHOW STATUS LIKE 'handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	1
+Handler_read_last	0
+Handler_read_next	30
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	0
+EXPLAIN SELECT pk_1, pk_2 FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY,k1	PRIMARY	4	NULL	#	Using where
+FLUSH STATUS;
+SELECT pk_1, pk_2 FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
+pk_1	pk_2
+3	3
+3	13
+4	3
+4	13
+5	3
+5	13
+SHOW STATUS LIKE 'handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	1
+Handler_read_last	0
+Handler_read_next	30
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	0
+#
+# MAX/MIN optimization
+#
+EXPLAIN SELECT MIN(pk_1) FROM t1 WHERE f1 = '2000-01-03';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	k1	k1	6	const	#	Using index
+FLUSH STATUS;
+SELECT MIN(pk_1) FROM t1 WHERE f1 = '2000-01-03';
+MIN(pk_1)
+1
+SHOW STATUS LIKE 'handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	1
+Handler_read_last	0
+Handler_read_next	432
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	0
+EXPLAIN SELECT MIN(pk_1) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY,k1	PRIMARY	4	NULL	#	Using where
+FLUSH STATUS;
+SELECT MIN(pk_1) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
+MIN(pk_1)
+3
+SHOW STATUS LIKE 'handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	1
+Handler_read_last	0
+Handler_read_next	30
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	0
+EXPLAIN SELECT MAX(pk_1) FROM t1 WHERE f1 = '2000-01-03';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	k1	k1	6	const	#	Using index
+FLUSH STATUS;
+SELECT MAX(pk_1) FROM t1 WHERE f1 = '2000-01-03';
+MAX(pk_1)
+459
+SHOW STATUS LIKE 'handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	1
+Handler_read_last	0
+Handler_read_next	432
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	0
+EXPLAIN SELECT MAX(pk_1) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY,k1	PRIMARY	4	NULL	#	Using where
+FLUSH STATUS;
+SELECT MAX(pk_1) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
+MAX(pk_1)
+5
+SHOW STATUS LIKE 'handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	1
+Handler_read_last	0
+Handler_read_next	30
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	0
+#
+# Loose index scan
+#
+EXPLAIN SELECT MIN(pk_1) FROM t1 WHERE f2 BETWEEN 13 AND 14 GROUP BY f2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	k2	k2	5	NULL	#	Using where; Using index
+FLUSH STATUS;
+SELECT MIN(pk_1) FROM t1 WHERE f2 BETWEEN 13 AND 14 GROUP BY f2;
+MIN(pk_1)
+3
+3
+SHOW STATUS LIKE 'handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	1
+Handler_read_last	0
+Handler_read_next	96
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	0
+EXPLAIN SELECT MIN(pk_1) FROM t1 WHERE f2 IN (1, 2) GROUP BY f2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	k2	k2	5	NULL	#	Using where; Using index
+FLUSH STATUS;
+SELECT MIN(pk_1) FROM t1 WHERE f2 IN (1, 2) GROUP BY f2;
+MIN(pk_1)
+1
+1
+SHOW STATUS LIKE 'handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	2
+Handler_read_last	0
+Handler_read_next	224
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	0
+#
+# JOIN optimization
+#
+EXPLAIN SELECT count(*) FROM t1 AS t1 JOIN t1 AS t2
+ON t2.pk_1 = t1.pk_1 WHERE t1.f1 = '2000-01-03' AND t2.f1 = '2000-01-03';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	PRIMARY,k1	k1	6	const	#	Using index
+1	SIMPLE	t2	ref	PRIMARY,k1	k1	6	const	#	Using where; Using index
+FLUSH STATUS;
+SELECT count(*) FROM t1 AS t1 JOIN t1 AS t2
+ON t2.pk_1 = t1.pk_1 WHERE t1.f1 = '2000-01-03' AND t2.f1 = '2000-01-03';
+count(*)
+864
+SHOW STATUS LIKE 'handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	433
+Handler_read_last	0
+Handler_read_next	187056
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	0
+#
+# Optimization of sorting
+#
+EXPLAIN SELECT f1, pk_1, pk_2 FROM t1 WHERE pk_1 = 3 AND f1 = '2000-01-03'
+ORDER BY pk_2 DESC LIMIT 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	PRIMARY,k1	PRIMARY	4	const	#	Using where
+FLUSH STATUS;
+SELECT f1, pk_1, pk_2 FROM t1 WHERE pk_1 = 3 AND f1 = '2000-01-03'
+ORDER BY pk_2 DESC LIMIT 5;
+f1	pk_1	pk_2
+2000-01-03 00:00:00	3	13
+2000-01-03 00:00:00	3	3
+SHOW STATUS LIKE 'handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	1
+Handler_read_last	0
+Handler_read_next	0
+Handler_read_prev	10
+Handler_read_rnd	0
+Handler_read_rnd_next	0
+EXPLAIN SELECT f1, pk_1, pk_2 FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03'
+ORDER BY pk_2 DESC LIMIT 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY,k1	PRIMARY	4	NULL	#	Using where; Using filesort
+FLUSH STATUS;
+SELECT f1, pk_1, pk_2 FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03'
+ORDER BY pk_2 DESC LIMIT 5;
+f1	pk_1	pk_2
+2000-01-03 00:00:00	3	13
+2000-01-03 00:00:00	4	13
+2000-01-03 00:00:00	5	13
+2000-01-03 00:00:00	3	3
+2000-01-03 00:00:00	4	3
+SHOW STATUS LIKE 'handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	1
+Handler_read_last	0
+Handler_read_next	30
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	0
+DROP TABLE t1;
+#
+# Max key part limitation
+#
+CREATE TABLE t1
+(
+f1 INT, f2 INT, f3 INT, f4 INT, f5 INT, f6 INT, f7 INT, f8 INT, f9 INT, f10 INT,
+f11 INT, f12 INT, f13 INT, f14 INT, f15 INT, f16 INT, f17 INT, f18 INT,
+PRIMARY KEY (f1, f2, f3, f4, f5, f6, f7, f8, f9, f10),
+KEY k1 (f11, f12, f13, f14, f15, f16, f17)
+) ENGINE = InnoDB;
+EXPLAIN SELECT f17 FROM t1 FORCE INDEX (k1) WHERE
+f1 = 0 AND f2 = 0 AND f3 = 0 AND f4 = 0 AND f5 = 0 AND
+f6 = 0 AND f7 = 0 AND f8 = 0 AND f9 = 0 AND f10 = 0 AND
+f11 = 0 AND f12 = 0 AND f13 = 0 AND f14 = 0 AND
+f15 = 0 AND f16 = 0 AND f17 = 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	k1	k1	35	const,const,const,const,const,const,const	#	Using where; Using index
+EXPLAIN SELECT f17 FROM t1 FORCE INDEX (k1) WHERE
+f1 = 0 AND f2 = 0 AND f3 = 0 AND f4 = 0 AND f5 = 0 AND
+f6 = 0 AND f7 = 0 AND f8 = 0 AND f9 = 0 AND
+f11 = 0 AND f12 = 0 AND f13 = 0 AND f14 = 0 AND
+f15 = 0 AND f16 = 0 AND f17 = 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	k1	k1	35	const,const,const,const,const,const,const	#	Using where; Using index
+DROP TABLE t1;
+#
+# Max key length limitation
+#
+CREATE TABLE t1
+(
+f1 VARCHAR(500), f2 VARCHAR(500), f3 VARCHAR(500),
+f4 VARCHAR(500), f5 VARCHAR(500), f6 VARCHAR(500),
+f7 VARCHAR(500),
+PRIMARY KEY (f1, f2, f3, f4),
+KEY k1 (f5, f6, f7)
+) ENGINE = InnoDB;
+EXPLAIN SELECT f5 FROM t1 FORCE INDEX (k1) WHERE
+f1 = 'a' AND f2 = 'a' AND f3 = 'a' AND f4 = 'a' AND
+f5 = 'a' AND f6 = 'a' AND f7 = 'a';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	k1	k1	1509	const,const,const	#	Using where; Using index
+EXPLAIN SELECT f5 FROM t1 FORCE INDEX (k1) WHERE
+f1 = 'a' AND f2 = 'a' AND f3 = 'a' AND
+f5 = 'a' AND f6 = 'a' AND f7 = 'a';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	k1	k1	1509	const,const,const	#	Using where; Using index
+EXPLAIN SELECT f5 FROM t1 FORCE INDEX (k1) WHERE
+f1 = 'a' AND f2 = 'a' AND f4 = 'a' AND
+f5 = 'a' AND f6 = 'a' AND f7 = 'a';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	k1	k1	1509	const,const,const	#	Using where; Using index
+DROP TABLE t1;
+#
+# Unique extended key
+#
+CREATE TABLE t1
+(
+pk INT NOT NULL auto_increment,
+f1 INT NOT NULL,
+KEY (f1),
+PRIMARY KEY (pk)
+) ENGINE = INNODB;
+CREATE TABLE t2
+(
+f1 INT,
+f2 INT
+) ENGINE = INNODB;
+INSERT INTO t1(f1) VALUES (1),(2);
+INSERT INTO t1(f1) SELECT f1 + 2 FROM t1;
+INSERT INTO t1(f1) SELECT f1 + 4 FROM t1;
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+INSERT INTO t2 VALUES (1,1), (2,2);
+EXPLAIN SELECT t2.f1 FROM t2 JOIN t1 IGNORE INDEX(primary) ON t2.f1 = t1.pk and t2.f2 = t1.f1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t1	ref	f1	f1	4	test.t2.f2	1	Using where; Using index
+FLUSH STATUS;
+SELECT t2.f1 FROM t2 JOIN t1 IGNORE INDEX(primary) ON t2.f1 = t1.pk and t2.f2 = t1.f1;
+f1
+1
+2
+SHOW STATUS LIKE 'Handler_read%';
+Variable_name	Value
+Handler_read_first	1
+Handler_read_key	3
+Handler_read_last	0
+Handler_read_next	2
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	3
+DROP TABLE t1, t2;
+set optimizer_switch=default;

=== modified file 'mysql-test/r/mysqld--help-notwin.result'
--- a/mysql-test/r/mysqld--help-notwin.result	2012-10-09 12:52:55 +0000
+++ b/mysql-test/r/mysqld--help-notwin.result	2012-10-24 08:43:38 +0000
@@ -493,8 +493,9 @@ The following options may be given as th
  engine_condition_pushdown, index_condition_pushdown, mrr,
  mrr_cost_based, materialization, semijoin, loosescan,
  firstmatch, subquery_materialization_cost_based,
- block_nested_loop, batched_key_access} and val is one of
- {on, off, default}
+ block_nested_loop, batched_key_access,
+ extended_secondary_keys} and val is one of {on, off,
+ default}
  --optimizer-trace=name 
  Controls tracing of the Optimizer:
  optimizer_trace=option=val[,option=val...], where option
@@ -1115,7 +1116,7 @@ old-passwords 0
 old-style-user-limits FALSE
 optimizer-prune-level 1
 optimizer-search-depth 62
-optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on
+optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,extended_secondary_keys=on
 optimizer-trace 
 optimizer-trace-features greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
 optimizer-trace-limit 1

=== modified file 'mysql-test/r/optimizer_switch.result'
--- a/mysql-test/r/optimizer_switch.result	2012-07-10 11:59:40 +0000
+++ b/mysql-test/r/optimizer_switch.result	2012-10-24 08:43:38 +0000
@@ -3,47 +3,47 @@ BUG#37120 optimizer_switch allowable val
 
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,extended_secondary_keys=on
 set optimizer_switch='default';
 set optimizer_switch='materialization=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,extended_secondary_keys=on
 set optimizer_switch='default';
 set optimizer_switch='semijoin=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=off,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=off,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,extended_secondary_keys=on
 set optimizer_switch='default';
 set optimizer_switch='loosescan=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=off,firstmatch=on,subquery_materialization_cost_based=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=off,firstmatch=on,subquery_materialization_cost_based=on,extended_secondary_keys=on
 set optimizer_switch='default';
 set optimizer_switch='semijoin=off,materialization=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=off,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=off,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,extended_secondary_keys=on
 set optimizer_switch='default';
 set optimizer_switch='materialization=off,semijoin=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=off,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=off,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,extended_secondary_keys=on
 set optimizer_switch='default';
 set optimizer_switch='semijoin=off,materialization=off,loosescan=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=on,subquery_materialization_cost_based=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=on,subquery_materialization_cost_based=on,extended_secondary_keys=on
 set optimizer_switch='default';
 set optimizer_switch='semijoin=off,loosescan=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=off,loosescan=off,firstmatch=on,subquery_materialization_cost_based=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=off,loosescan=off,firstmatch=on,subquery_materialization_cost_based=on,extended_secondary_keys=on
 set optimizer_switch='default';
 set optimizer_switch='materialization=off,loosescan=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=on,loosescan=off,firstmatch=on,subquery_materialization_cost_based=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=on,loosescan=off,firstmatch=on,subquery_materialization_cost_based=on,extended_secondary_keys=on
 set optimizer_switch='default';
 create table t1 (a1 char(8), a2 char(8));
 create table t2 (b1 char(8), b2 char(8));

=== modified file 'mysql-test/t/innodb_pk_extension.test'
--- a/mysql-test/t/innodb_pk_extension.test	2012-10-24 07:49:04 +0000
+++ b/mysql-test/t/innodb_pk_extension.test	2012-10-24 08:43:38 +0000
@@ -1,225 +1,24 @@
---source include/have_innodb.inc
-
 --echo #
 --echo #  WL#6266 Make use of hidden key parts
 --echo #
 
-CREATE TABLE t1
-(
- pk_1 INT,
- pk_2 INT,
- f1 DATETIME,
- f2 INT,
- PRIMARY KEY(pk_1, pk_2),
- KEY k1(f1),
- KEY k2(f2)
-) ENGINE = InnoDB;
-
-INSERT INTO t1 VALUES
-(1, 1, '2000-01-01', 1), (1, 2, '2000-01-02', 2), (1, 3, '2000-01-03', 3), (1, 4, '2000-01-04', 4), (1, 5, '2000-01-05', 5),
-(2, 1, '2000-01-01', 6), (2, 2, '2000-01-02', 7), (2, 3, '2000-01-03', 8), (2, 4, '2000-01-04', 9), (2, 5, '2000-01-05', 10),
-(3, 1, '2000-01-01', 11), (3, 2, '2000-01-02', 12), (3, 3, '2000-01-03', 13), (3, 4, '2000-01-04', 14), (3, 5, '2000-01-05', 15),
-(4, 1, '2000-01-01', 16), (4, 2, '2000-01-02', 17), (4, 3, '2000-01-03', 18), (4, 4, '2000-01-04', 19), (4, 5, '2000-01-05', 20),
-(5, 1, '2000-01-01', 21), (5, 2, '2000-01-02', 22), (5, 3, '2000-01-03', 23), (5, 4, '2000-01-04', 24), (5, 5, '2000-01-05', 25),
-(6, 1, '2000-01-06', 26), (6, 2, '2000-01-06', 27), (6, 3, '2000-01-03', 28), (6, 4, '2000-01-06', 29), (6, 5, '2000-01-06', 30),
-(7, 1, '2000-01-06', 31), (7, 2, '2000-01-06', 32), (7, 3, '2000-01-03', 33), (7, 4, '2000-01-06', 34), (7, 5, '2000-01-06', 35),
-(8, 1, '2000-01-06', 36), (8, 2, '2000-01-06', 37), (8, 3, '2000-01-03', 38), (8, 4, '2000-01-06', 39), (8, 5, '2000-01-06', 40),
-(9, 1, '2000-01-06', 41), (9, 2, '2000-01-06', 42), (9, 3, '2000-01-03', 43), (9, 4, '2000-01-06', 44), (9, 5, '2000-01-06', 45);
-
-INSERT INTO t1 VALUES
-(11, 1, '2000-01-01', 1), (11, 2, '2000-01-02', 2), (11, 3, '2000-01-03', 3), (11, 4, '2000-01-04', 4), (11, 5, '2000-01-05', 5),
-(12, 1, '2000-01-01', 6), (12, 2, '2000-01-02', 7), (12, 3, '2000-01-03', 8), (12, 4, '2000-01-04', 9), (12, 5, '2000-01-05', 10),
-(13, 1, '2000-01-01', 11), (13, 2, '2000-01-02', 12), (13, 3, '2000-01-03', 13), (13, 4, '2000-01-04', 14), (13, 5, '2000-01-05', 15),
-(14, 1, '2000-01-01', 16), (14, 2, '2000-01-02', 17), (14, 3, '2000-01-03', 18), (14, 4, '2000-01-04', 19), (14, 5, '2000-01-05', 20),
-(15, 1, '2000-01-01', 1), (15, 2, '2000-01-02', 2), (15, 3, '2000-01-03', 3), (15, 4, '2000-01-04', 4), (15, 5, '2000-01-05', 5),
-(16, 1, '2000-01-06', 6), (16, 2, '2000-01-06', 7), (16, 3, '2000-01-03', 8), (16, 4, '2000-01-06', 9), (16, 5, '2000-01-06', 10),
-(17, 1, '2000-01-06', 31), (17, 2, '2000-01-06', 32), (17, 3, '2000-01-03', 33), (17, 4, '2000-01-06', 34), (17, 5, '2000-01-06', 35),
-(18, 1, '2000-01-06', 36), (18, 2, '2000-01-06', 37), (18, 3, '2000-01-03', 38), (18, 4, '2000-01-06', 39), (18, 5, '2000-01-06', 40),
-(19, 1, '2000-01-06', 1), (19, 2, '2000-01-06', 2), (19, 3, '2000-01-03', 3), (19, 4, '2000-01-06', 4), (19, 5, '2000-01-06', 5);
-
-
-INSERT INTO t1 VALUES
-(21, 1, '2000-01-01', 1), (21, 2, '2000-01-02', 2), (31, 3, '2000-01-03', 3), (41, 4, '2000-01-04', 4), (51, 5, '2000-01-05', 5),
-(22, 1, '2000-01-01', 6), (22, 2, '2000-01-02', 7), (32, 3, '2000-01-03', 8), (42, 4, '2000-01-04', 9), (52, 5, '2000-01-05', 10),
-(23, 1, '2000-01-01', 11), (23, 2, '2000-01-02', 12), (33, 3, '2000-01-03', 13), (43, 4, '2000-01-04', 14), (53, 5, '2000-01-05', 15),
-(24, 1, '2000-01-01', 16), (24, 2, '2000-01-02', 17), (34, 3, '2000-01-03', 18), (44, 4, '2000-01-04', 19), (54, 5, '2000-01-05', 20),
-(25, 1, '2000-01-01', 1), (25, 2, '2000-01-02', 2), (35, 3, '2000-01-03', 3), (45, 4, '2000-01-04', 4), (55, 5, '2000-01-05', 5),
-(26, 1, '2000-01-06', 6), (26, 2, '2000-01-06', 7), (36, 3, '2000-01-03', 8), (46, 4, '2000-01-06', 9), (56, 5, '2000-01-06', 10),
-(27, 1, '2000-01-06', 31), (27, 2, '2000-01-06', 32), (37, 3, '2000-01-03', 33), (47, 4, '2000-01-06', 34), (57, 5, '2000-01-06', 35),
-(28, 1, '2000-01-06', 36), (28, 2, '2000-01-06', 37), (38, 3, '2000-01-03', 38), (48, 4, '2000-01-06', 39), (58, 5, '2000-01-06', 40),
-(29, 1, '2000-01-06', 1), (29, 2, '2000-01-06', 2), (39, 3, '2000-01-03', 3), (49, 4, '2000-01-06', 4), (59, 5, '2000-01-06', 5);
-
-INSERT INTO t1 SELECT pk_1 + 60, pk_2, f1, f2 FROM t1;
-INSERT INTO t1 SELECT pk_1 + 120, pk_2, f1, f2 FROM t1;
-INSERT INTO t1 SELECT pk_1 + 240, pk_2, f1, f2 FROM t1;
-INSERT INTO t1 SELECT pk_1, pk_2 + 10, f1, f2 FROM t1;
-
-ANALYZE TABLE t1;
-
---echo #
---echo # REF access optimization
---echo #
-
---replace_column 9 #
-EXPLAIN SELECT count(*) FROM t1 WHERE pk_1 = 3 and f1 = '2000-01-03';
-FLUSH STATUS;
-SELECT count(*) FROM t1 WHERE pk_1 = 3 and f1 = '2000-01-03';
-SHOW STATUS LIKE 'handler_read%';
-
---replace_column 9 #
-EXPLAIN SELECT pk_2 FROM t1 WHERE pk_1 = 3 and f1 = '2000-01-03';
-FLUSH STATUS;
-SELECT pk_2 FROM t1 WHERE pk_1 = 3 and f1 = '2000-01-03';
-SHOW STATUS LIKE 'handler_read%';
-
---echo #
---echo # RANGE access optimization
---echo #
-
---replace_column 9 #
-EXPLAIN SELECT count(*) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
-FLUSH STATUS;
-SELECT count(*) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
-SHOW STATUS LIKE 'handler_read%';
-
---replace_column 9 #
-EXPLAIN SELECT pk_1, pk_2 FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
-FLUSH STATUS;
-SELECT pk_1, pk_2 FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
-SHOW STATUS LIKE 'handler_read%';
-
---echo #
---echo # MAX/MIN optimization
---echo #
-
---replace_column 9 #
-EXPLAIN SELECT MIN(pk_1) FROM t1 WHERE f1 = '2000-01-03';
-FLUSH STATUS;
-SELECT MIN(pk_1) FROM t1 WHERE f1 = '2000-01-03';
-SHOW STATUS LIKE 'handler_read%';
-
---replace_column 9 #
-EXPLAIN SELECT MIN(pk_1) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
-FLUSH STATUS;
-SELECT MIN(pk_1) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
-SHOW STATUS LIKE 'handler_read%';
-
---replace_column 9 #
-EXPLAIN SELECT MAX(pk_1) FROM t1 WHERE f1 = '2000-01-03';
-FLUSH STATUS;
-SELECT MAX(pk_1) FROM t1 WHERE f1 = '2000-01-03';
-SHOW STATUS LIKE 'handler_read%';
-
---replace_column 9 #
-EXPLAIN SELECT MAX(pk_1) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
-FLUSH STATUS;
-SELECT MAX(pk_1) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
-SHOW STATUS LIKE 'handler_read%';
-
---echo #
---echo # Loose index scan
---echo #
-
---replace_column 9 #
-EXPLAIN SELECT MIN(pk_1) FROM t1 WHERE f2 BETWEEN 13 AND 14 GROUP BY f2;
-FLUSH STATUS;
-SELECT MIN(pk_1) FROM t1 WHERE f2 BETWEEN 13 AND 14 GROUP BY f2;
-SHOW STATUS LIKE 'handler_read%';
-
---replace_column 9 #
-EXPLAIN SELECT MIN(pk_1) FROM t1 WHERE f2 IN (1, 2) GROUP BY f2;
-FLUSH STATUS;
-SELECT MIN(pk_1) FROM t1 WHERE f2 IN (1, 2) GROUP BY f2;
-SHOW STATUS LIKE 'handler_read%';
-
---echo #
---echo # JOIN optimization
---echo #
-
---replace_column 9 #
-EXPLAIN SELECT count(*) FROM t1 AS t1 JOIN t1 AS t2
-ON t2.pk_1 = t1.pk_1 WHERE t1.f1 = '2000-01-03' AND t2.f1 = '2000-01-03';
-FLUSH STATUS;
-SELECT count(*) FROM t1 AS t1 JOIN t1 AS t2
-ON t2.pk_1 = t1.pk_1 WHERE t1.f1 = '2000-01-03' AND t2.f1 = '2000-01-03';
-SHOW STATUS LIKE 'handler_read%';
-
---echo #
---echo # Optimization of sorting
---echo #
-
---replace_column 9 #
-EXPLAIN SELECT f1, pk_1, pk_2 FROM t1 WHERE pk_1 = 3 AND f1 = '2000-01-03'
-ORDER BY pk_2 DESC LIMIT 5;
-FLUSH STATUS;
-SELECT f1, pk_1, pk_2 FROM t1 WHERE pk_1 = 3 AND f1 = '2000-01-03'
-ORDER BY pk_2 DESC LIMIT 5;
-SHOW STATUS LIKE 'handler_read%';
-
---replace_column 9 #
-EXPLAIN SELECT f1, pk_1, pk_2 FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03'
-ORDER BY pk_2 DESC LIMIT 5;
-FLUSH STATUS;
-SELECT f1, pk_1, pk_2 FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03'
-ORDER BY pk_2 DESC LIMIT 5;
-SHOW STATUS LIKE 'handler_read%';
-
-DROP TABLE t1;
-
---echo #
---echo # Max key part limitation
---echo #
-
-CREATE TABLE t1
-(
-  f1 INT, f2 INT, f3 INT, f4 INT, f5 INT, f6 INT, f7 INT, f8 INT, f9 INT, f10 INT,
-  f11 INT, f12 INT, f13 INT, f14 INT, f15 INT, f16 INT, f17 INT, f18 INT,
-  PRIMARY KEY (f1, f2, f3, f4, f5, f6, f7, f8, f9, f10),
-  KEY k1 (f11, f12, f13, f14, f15, f16, f17)
-) ENGINE = InnoDB;
-
---replace_column 9 #
-EXPLAIN SELECT f17 FROM t1 FORCE INDEX (k1) WHERE
-f1 = 0 AND f2 = 0 AND f3 = 0 AND f4 = 0 AND f5 = 0 AND
-f6 = 0 AND f7 = 0 AND f8 = 0 AND f9 = 0 AND f10 = 0 AND
-f11 = 0 AND f12 = 0 AND f13 = 0 AND f14 = 0 AND
-f15 = 0 AND f16 = 0 AND f17 = 0;
-
---replace_column 9 #
-EXPLAIN SELECT f17 FROM t1 FORCE INDEX (k1) WHERE
-f1 = 0 AND f2 = 0 AND f3 = 0 AND f4 = 0 AND f5 = 0 AND
-f6 = 0 AND f7 = 0 AND f8 = 0 AND f9 = 0 AND
-f11 = 0 AND f12 = 0 AND f13 = 0 AND f14 = 0 AND
-f15 = 0 AND f16 = 0 AND f17 = 0;
-
-DROP TABLE t1;
-
---echo #
---echo # Max key length limitation
---echo #
-
-CREATE TABLE t1
-(
-  f1 VARCHAR(500), f2 VARCHAR(500), f3 VARCHAR(500),
-  f4 VARCHAR(500), f5 VARCHAR(500), f6 VARCHAR(500),
-  f7 VARCHAR(500),
-  PRIMARY KEY (f1, f2, f3, f4),
-  KEY k1 (f5, f6, f7)
-) ENGINE = InnoDB;
-
---replace_column 9 #
-EXPLAIN SELECT f5 FROM t1 FORCE INDEX (k1) WHERE
-f1 = 'a' AND f2 = 'a' AND f3 = 'a' AND f4 = 'a' AND
-f5 = 'a' AND f6 = 'a' AND f7 = 'a';
-
---replace_column 9 #
-EXPLAIN SELECT f5 FROM t1 FORCE INDEX (k1) WHERE
-f1 = 'a' AND f2 = 'a' AND f3 = 'a' AND
-f5 = 'a' AND f6 = 'a' AND f7 = 'a';
-
---replace_column 9 #
-EXPLAIN SELECT f5 FROM t1 FORCE INDEX (k1) WHERE
-f1 = 'a' AND f2 = 'a' AND f4 = 'a' AND
-f5 = 'a' AND f6 = 'a' AND f7 = 'a';
 
-DROP TABLE t1;
+--echo #
+--echo # Optimizer switch extended_secondary_keys=on
+--echo #
+
+set optimizer_switch= "extended_secondary_keys=on";
+
+--source include/innodb_pk_extension.inc
+
+
+--echo #
+--echo # Optimizer switch extended_secondary_keys=off
+--echo #
+
+set optimizer_switch= "extended_secondary_keys=off";
+
+--source include/innodb_pk_extension.inc
+
+set optimizer_switch=default;
+

=== modified file 'sql/handler.cc'
--- a/sql/handler.cc	2012-10-24 07:49:04 +0000
+++ b/sql/handler.cc	2012-10-24 08:43:38 +0000
@@ -4533,7 +4533,7 @@ int handler::index_next_same(uchar *buf,
       table->record[0]= buf;
       key_info= table->key_info + active_index;
       key_part= key_info->key_part;
-      key_part_end= key_part + key_info->actual_key_parts;
+      key_part_end= key_part + key_info->user_defined_key_parts;
       for (; key_part < key_part_end; key_part++)
       {
         DBUG_ASSERT(key_part->field);

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2012-10-24 07:49:04 +0000
+++ b/sql/opt_range.cc	2012-10-24 08:43:38 +0000
@@ -2656,7 +2656,7 @@ int SQL_SELECT::test_quick_select(THD *t
         param.key[param.keys]=key_parts;
         key_part_info= key_info->key_part;
         Opt_trace_array trace_keypart(trace, "key_parts");
-        for (uint part=0 ; part < key_info->actual_key_parts ;
+        for (uint part=0 ; part < real_key_parts(key_info) ;
              part++, key_parts++, key_part_info++)
         {
           key_parts->key=          param.keys;
@@ -9420,8 +9420,9 @@ get_quick_select(PARAM *param,uint idx,S
       quick->key_parts=(KEY_PART*)
         memdup_root(parent_alloc? parent_alloc : &quick->alloc,
                     (char*) param->key[idx],
-                    sizeof(KEY_PART) * param->table->
-                    key_info[param->real_keynr[idx]].actual_key_parts);
+                    sizeof(KEY_PART) *
+                    real_key_parts(&param->
+                                   table->key_info[param->real_keynr[idx]]));
     }
   }
   DBUG_RETURN(quick);
@@ -11325,7 +11326,7 @@ get_best_group_min_max(PARAM *param, SEL
     if (join->group_list)
     {
       cur_part= cur_index_info->key_part;
-      end_part= cur_part + cur_index_info->actual_key_parts;
+      end_part= cur_part + real_key_parts(cur_index_info);
       /* Iterate in parallel over the GROUP list and the index parts. */
       for (tmp_group= join->group_list; tmp_group && (cur_part != end_part);
            tmp_group= tmp_group->next, cur_part++)
@@ -11453,9 +11454,9 @@ get_best_group_min_max(PARAM *param, SEL
       must form a sequence without any gaps that starts immediately after the
       last group keypart.
     */
-    last_part= cur_index_info->key_part + cur_index_info->actual_key_parts;
+    last_part= cur_index_info->key_part + real_key_parts(cur_index_info);
     first_non_group_part= 
-      (cur_group_key_parts < cur_index_info->actual_key_parts) ?
+      (cur_group_key_parts < real_key_parts(cur_index_info)) ?
       cur_index_info->key_part + cur_group_key_parts :
       NULL;
     first_non_infix_part= min_max_arg_part ?
@@ -11935,7 +11936,7 @@ get_field_keypart(KEY *index, Field *fie
 {
   KEY_PART_INFO *part, *end;
 
-  for (part= index->key_part, end= part + index->actual_key_parts;
+  for (part= index->key_part, end= part + real_key_parts(index) ;
        part < end; part++)
   {
     if (field->eq(part->field))

=== modified file 'sql/opt_sum.cc'
--- a/sql/opt_sum.cc	2012-10-24 07:49:04 +0000
+++ b/sql/opt_sum.cc	2012-10-24 08:43:38 +0000
@@ -909,7 +909,7 @@ static bool find_key_for_maxmin(bool max
       continue;
     uint jdx= 0;
     *prefix_len= 0;
-    for (part= keyinfo->key_part, part_end= part + keyinfo->actual_key_parts ;
+    for (part= keyinfo->key_part, part_end= part + real_key_parts(keyinfo) ;
          part != part_end ;
          part++, jdx++, key_part_to_use= (key_part_to_use << 1) | 1)
     {

=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc	2012-10-24 07:49:04 +0000
+++ b/sql/sql_optimizer.cc	2012-10-24 08:43:38 +0000
@@ -5320,7 +5320,7 @@ add_key_part(Key_use_array *keyuse_array
       if (form->key_info[key].flags & (HA_FULLTEXT | HA_SPATIAL))
 	continue;    // ToDo: ft-keys in non-ft queries.   SerG
 
-      uint key_parts= (uint) form->key_info[key].actual_key_parts;
+      uint key_parts= real_key_parts(&form->key_info[key]);
       for (uint part=0 ; part <  key_parts ; part++)
       {
 	if (field->eq(form->key_info[key].key_part[part].field))

=== modified file 'sql/sql_planner.cc'
--- a/sql/sql_planner.cc	2012-10-24 07:49:04 +0000
+++ b/sql/sql_planner.cc	2012-10-24 08:43:38 +0000
@@ -568,7 +568,7 @@ void Optimize_table_order::best_access_p
         loose_scan_opt.check_ref_access_part1(s, key, start_key, found_part);
 
         /* Check if we found full key */
-        if (found_part == LOWER_BITS(key_part_map, keyinfo->actual_key_parts) &&
+        if (found_part == LOWER_BITS(key_part_map, real_key_parts(keyinfo)) &&
             !ref_or_null_part)
         {                                         /* use eq key */
           max_key_part= (uint) ~0;
@@ -608,7 +608,7 @@ void Optimize_table_order::best_access_p
             }
             else
             {
-              if (!(records= keyinfo->rec_per_key[keyinfo->actual_key_parts-1]))
+              if (!(records= keyinfo->rec_per_key[real_key_parts(keyinfo)-1]))
               {                                   /* Prefer longer keys */
                 records=
                   ((double) s->records / (double) rec *
@@ -659,7 +659,7 @@ void Optimize_table_order::best_access_p
           if ((found_part & 1) &&
               (!(table->file->index_flags(key, 0, 0) & HA_ONLY_WHOLE_INDEX) ||
                found_part == LOWER_BITS(key_part_map,
-                                        keyinfo->actual_key_parts)))
+                                        real_key_parts(keyinfo))))
           {
             max_key_part= max_part_bit(found_part);
             /*

=== modified file 'sql/sql_priv.h'
--- a/sql/sql_priv.h	2012-10-21 19:37:01 +0000
+++ b/sql/sql_priv.h	2012-10-24 08:43:38 +0000
@@ -208,7 +208,8 @@ template <class T> bool valid_buffer_ran
 #define OPTIMIZER_SWITCH_LOOSE_SCAN                (1ULL << 12)
 #define OPTIMIZER_SWITCH_FIRSTMATCH                (1ULL << 13)
 #define OPTIMIZER_SWITCH_SUBQ_MAT_COST_BASED       (1ULL << 14)
-#define OPTIMIZER_SWITCH_LAST                      (1ULL << 15)
+#define OPTIMIZER_SWITCH_EXTENDED_SECONDARY_KEYS   (1ULL << 15)
+#define OPTIMIZER_SWITCH_LAST                      (1ULL << 16)
 
 /**
    If OPTIMIZER_SWITCH_ALL is defined, optimizer_switch flags for newer 
@@ -234,7 +235,8 @@ template <class T> bool valid_buffer_ran
                                   OPTIMIZER_SWITCH_SEMIJOIN | \
                                   OPTIMIZER_SWITCH_LOOSE_SCAN | \
                                   OPTIMIZER_SWITCH_FIRSTMATCH | \
-                                  OPTIMIZER_SWITCH_SUBQ_MAT_COST_BASED)
+                                  OPTIMIZER_SWITCH_SUBQ_MAT_COST_BASED | \
+                                  OPTIMIZER_SWITCH_EXTENDED_SECONDARY_KEYS)
 #else
 #define OPTIMIZER_SWITCH_DEFAULT (OPTIMIZER_SWITCH_INDEX_MERGE | \
                                   OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \
@@ -244,7 +246,8 @@ template <class T> bool valid_buffer_ran
                                   OPTIMIZER_SWITCH_INDEX_CONDITION_PUSHDOWN | \
                                   OPTIMIZER_SWITCH_MRR | \
                                   OPTIMIZER_SWITCH_MRR_COST_BASED | \
-                                  OPTIMIZER_SWITCH_BNL)
+                                  OPTIMIZER_SWITCH_BNL | \
+                                  OPTIMIZER_SWITCH_EXTENDED_SECONDARY_KEYS)
 #endif
 /*
   Replication uses 8 bytes to store SQL_MODE in the binary log. The day you

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-10-24 07:49:04 +0000
+++ b/sql/sql_select.cc	2012-10-24 08:43:38 +0000
@@ -1744,8 +1744,9 @@ bool create_ref_for_key(JOIN *join, JOIN
     DBUG_RETURN(false);
   if (j->type == JT_CONST)
     j->table->const_table= 1;
-  else if (((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY)) != HA_NOSAME) ||
-	   keyparts != keyinfo->user_defined_key_parts || null_ref_key)
+  else if (((real_key_flags(keyinfo) & 
+             (HA_NOSAME | HA_NULL_PART_KEY)) != HA_NOSAME) ||
+	   keyparts != real_key_parts(keyinfo) || null_ref_key)
   {
     /* Must read with repeat */
     j->type= null_ref_key ? JT_REF_OR_NULL : JT_REF;
@@ -5471,7 +5472,7 @@ test_if_cheaper_ordering(const JOIN_TAB 
             See Bug #28591 for details.
           */  
           rec_per_key= used_key_parts &&
-                       used_key_parts <= keyinfo->actual_key_parts ?
+                       used_key_parts <= real_key_parts(keyinfo) ?
                        keyinfo->rec_per_key[used_key_parts-1] : 1;
           set_if_bigger(rec_per_key, 1);
           /*
@@ -5687,5 +5688,38 @@ uint get_index_for_order(ORDER *order, T
 
 
 /**
+  Returns number of key parts depending on
+  OPTIMIZER_SWITCH_EXTENDED_SECONDARY_KEYS flag.
+
+  @param  key_info  pointer to KEY structure
+
+  @return number of key parts.
+*/
+
+uint real_key_parts(KEY *key_info)
+{
+  return key_info->table->in_use->
+    optimizer_switch_flag(OPTIMIZER_SWITCH_EXTENDED_SECONDARY_KEYS) ?
+    key_info->actual_key_parts : key_info->user_defined_key_parts;
+}
+
+
+/**
+  Returns key flags depending on
+  OPTIMIZER_SWITCH_EXTENDED_SECONDARY_KEYS flag.
+
+  @param  key_info  pointer to KEY structure
+
+  @return key flags.
+*/
+
+uint real_key_flags(KEY *key_info)
+{
+  return key_info->table->in_use->
+    optimizer_switch_flag(OPTIMIZER_SWITCH_EXTENDED_SECONDARY_KEYS) ?
+    key_info->actual_flags : key_info->flags;
+}
+
+/**
   @} (end of group Query_Optimizer)
 */

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2012-08-30 09:16:46 +0000
+++ b/sql/sql_select.h	2012-10-24 08:43:38 +0000
@@ -1331,4 +1331,7 @@ static inline Item * and_items(Item* con
   return (cond? (new Item_cond_and(cond, item)) : item);
 }
 
+uint real_key_parts(KEY *key_info);
+uint real_key_flags(KEY *key_info);
+
 #endif /* SQL_SELECT_INCLUDED */

=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc	2012-10-24 07:49:04 +0000
+++ b/sql/sql_table.cc	2012-10-24 08:43:38 +0000
@@ -4031,6 +4031,7 @@ mysql_prepare_create_table(THD *thd, HA_
 	key_info->name=(char*) key_name;
       }
     }
+    key_info->actual_flags= key_info->flags;
     if (!key_info->name || check_column_name(key_info->name))
     {
       my_error(ER_WRONG_NAME_FOR_INDEX, MYF(0), key_info->name);

=== modified file 'sql/sql_tmp_table.cc'
--- a/sql/sql_tmp_table.cc	2012-10-24 07:49:04 +0000
+++ b/sql/sql_tmp_table.cc	2012-10-24 08:43:38 +0000
@@ -984,6 +984,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
   param->end_write_records= rows_limit;
 
   keyinfo= param->keyinfo;
+  keyinfo->table= table;
 
   if (group)
   {
@@ -1036,6 +1037,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
       }
       keyinfo->key_length+=  key_part_info->store_length;
     }
+    keyinfo->actual_flags= keyinfo->flags;
   }
 
   if (distinct && field_count != param->hidden_field_count)
@@ -1072,7 +1074,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
            sizeof(KEY_PART_INFO));
     table->key_info= share->key_info= keyinfo;
     keyinfo->key_part= key_part_info;
-    keyinfo->flags= HA_NOSAME | HA_NULL_ARE_EQUAL;
+    keyinfo->actual_flags= keyinfo->flags= HA_NOSAME | HA_NULL_ARE_EQUAL;
     keyinfo->key_length= 0;  // Will compute the sum of the parts below.
     keyinfo->name= (char*) "<auto_key>";
     keyinfo->algorithm= HA_KEY_ALG_UNDEF;
@@ -1386,7 +1388,7 @@ TABLE *create_duplicate_weedout_tmp_tabl
     share->uniques= test(using_unique_constraint);
     table->key_info= table->s->key_info= keyinfo;
     keyinfo->key_part=key_part_info;
-    keyinfo->flags=HA_NOSAME;
+    keyinfo->actual_flags= keyinfo->flags= HA_NOSAME;
     keyinfo->usable_key_parts= keyinfo->user_defined_key_parts= 1;
     keyinfo->actual_key_parts= keyinfo->user_defined_key_parts;
     keyinfo->key_length=0;

=== modified file 'sql/structs.h'
--- a/sql/structs.h	2012-10-24 07:49:04 +0000
+++ b/sql/structs.h	2012-10-24 08:43:38 +0000
@@ -86,31 +86,40 @@ public:
 
 
 typedef struct st_key {
-  uint	key_length;			/* Tot length of key */
-  ulong flags;                          /* dupp key and pack flags */
-  uint  user_defined_key_parts;         /** How many key_parts */
-  uint  actual_key_parts;               /** How many key_parts including
-                                            hidden parts
-                                        */
-  uint  hidden_key_parts;               /** Key parts added from first key */
-  uint	usable_key_parts;		/* Should normally be = key_parts */
+  /** Tot length of key */
+  uint	key_length;
+  /** dupp key and pack flags */
+  ulong flags;
+  /** dupp key and pack flags for actual key parts */
+  ulong actual_flags;
+  /** How many key_parts */
+  uint  user_defined_key_parts;
+  /** How many key_parts including hidden parts */
+  uint  actual_key_parts;
+  /** Key parts added from first key */
+  uint  hidden_key_parts;
+  /** Should normally be = key_parts */
+  uint	usable_key_parts;
   uint  block_size;
   enum  ha_key_alg algorithm;
-  /*
-    Note that parser is used when the table is opened for use, and
-    parser_name is used when the table is being created.
+  /**
+     Note that parser is used when the table is opened for use, and
+     parser_name is used when the table is being created.
   */
   union
   {
-    plugin_ref parser;                  /* Fulltext [pre]parser */
-    LEX_STRING *parser_name;            /* Fulltext [pre]parser name */
+    /** Fulltext [pre]parser */
+    plugin_ref parser;
+    /** Fulltext [pre]parser name */
+    LEX_STRING *parser_name;
   };
   KEY_PART_INFO *key_part;
-  char	*name;				/* Name of key */
-  /*
-    Array of AVG(#records with the same field value) for 1st ... Nth key part.
-    0 means 'not known'.
-    For temporary heap tables this member is NULL.
+  /** Name of key */
+  char	*name;
+  /**
+     Array of AVG(#records with the same field value) for 1st ... Nth key part.
+     0 means 'not known'.
+     For temporary heap tables this member is NULL.
   */
   ulong *rec_per_key;
   union {

=== modified file 'sql/sys_vars.cc'
--- a/sql/sys_vars.cc	2012-10-09 12:52:55 +0000
+++ b/sql/sys_vars.cc	2012-10-24 08:43:38 +0000
@@ -2027,7 +2027,7 @@ static const char *optimizer_switch_name
   "materialization", "semijoin", "loosescan", "firstmatch",
   "subquery_materialization_cost_based",
 #endif
-  "default", NullS
+  "extended_secondary_keys", "default", NullS
 };
 static Sys_var_flagset Sys_optimizer_switch(
        "optimizer_switch",
@@ -2039,7 +2039,7 @@ static Sys_var_flagset Sys_optimizer_swi
        ", materialization, semijoin, loosescan, firstmatch,"
        " subquery_materialization_cost_based"
 #endif
-       ", block_nested_loop, batched_key_access"
+       ", block_nested_loop, batched_key_access, extended_secondary_keys"
        "} and val is one of {on, off, default}",
        SESSION_VAR(optimizer_switch), CMD_LINE(REQUIRED_ARG),
        optimizer_switch_names, DEFAULT(OPTIMIZER_SWITCH_DEFAULT),

=== modified file 'sql/table.cc'
--- a/sql/table.cc	2012-10-24 07:49:04 +0000
+++ b/sql/table.cc	2012-10-24 08:43:38 +0000
@@ -846,6 +846,8 @@ void KEY_PART_INFO::init_from_field(Fiel
   Add primary key parts to the secondary key
   unless they would be too long. Function
   updates sk->actual/hidden_key_parts.
+  Function also updates sk->actual_flags
+
 
   @param[in]     sk            Secondary key
   @param[in]     pk            Primary key
@@ -865,7 +867,10 @@ static void add_pk_parts_to_sk(KEY *sk, 
     KEY_PART_INFO *pk_key_part= &pk->key_part[pk_part];
     /* MySQL does not supports more key parts than MAX_REF_LENGTH */
     if (sk->actual_key_parts >= MAX_REF_PARTS)
+    {
+      sk->actual_flags= 0;
       return;
+    }
 
     for (uint j= 0; j < sk->user_defined_key_parts; j++)
     {
@@ -874,19 +879,23 @@ static void add_pk_parts_to_sk(KEY *sk, 
         break;
 
       if (j == sk->user_defined_key_parts - 1)
-       {   
-         /* MySQL does not supports keys longer than MAX_KEY_LENGTH */
-         if (max_key_length + pk_key_part->length > MAX_KEY_LENGTH)
-           return;
-
-         **key_part= *pk_key_part;
-         **rec_per_key= 0;
-         key_part= &++*key_part;
-         rec_per_key= &++*rec_per_key;
-         sk->actual_key_parts++;
-         sk->hidden_key_parts++;
-         max_key_length+= pk_key_part->length;
-       }
+      {   
+        /* MySQL does not supports keys longer than MAX_KEY_LENGTH */
+        if (max_key_length + pk_key_part->length > MAX_KEY_LENGTH)
+        {
+          sk->actual_flags= 0;
+          return;
+        }
+        /* it can be unique key */
+        sk->actual_flags= HA_NOSAME;
+        **key_part= *pk_key_part;
+        **rec_per_key= 0;
+        key_part= &++*key_part;
+        rec_per_key= &++*rec_per_key;
+        sk->actual_key_parts++;
+        sk->hidden_key_parts++;
+        max_key_length+= pk_key_part->length;
+      }
     }
   }
   return;
@@ -1750,7 +1759,10 @@ static int open_binary_frm(THD *thd, TAB
           value.
         */
         keyinfo->actual_key_parts= keyinfo->user_defined_key_parts;
+        keyinfo->actual_flags= keyinfo->flags;
       }
+      else
+        keyinfo->actual_flags|= keyinfo->flags;
 
       for (i=0 ; i < keyinfo->actual_key_parts ; key_part++,i++)
       {
@@ -3050,9 +3062,9 @@ uint calculate_key_len(TABLE *table, uin
   /* works only with key prefixes */
   DBUG_ASSERT(((keypart_map + 1) & keypart_map) == 0);
 
-  KEY *key_info= table->s->key_info+key;
+  KEY *key_info= table->key_info + key;
   KEY_PART_INFO *key_part= key_info->key_part;
-  KEY_PART_INFO *end_key_part= key_part + key_info->actual_key_parts;
+  KEY_PART_INFO *end_key_part= key_part + real_key_parts(key_info);
   uint length= 0;
 
   while (key_part < end_key_part && keypart_map)
@@ -5456,8 +5468,9 @@ bool TABLE::add_tmp_key(Field_map *key_p
   cur_key->key_length= key_len;
   cur_key->algorithm= HA_KEY_ALG_BTREE;
   cur_key->name= key_name;
-  cur_key->flags= HA_GENERATED_KEY;
+  cur_key->actual_flags= cur_key->flags= HA_GENERATED_KEY;
   cur_key->rec_per_key= (ulong*) (key_buf + sizeof(KEY_PART_INFO) * key_part_count);
+  cur_key->table= this;
 
   if (field_count == key_part_count)
     covering_keys.set_bit(s->keys);

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (sergey.glukhov:4808 to 4809) WL#6266Sergey Glukhov25 Oct