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(¶m->
+ 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#6266 | Sergey Glukhov | 25 Oct |
