4504 Sergey Glukhov 2012-10-30
WL#6266 Make use of hidden key parts(backport from trunk)
@ mysql-test/include/innodb_pk_extension.inc
test case
@ mysql-test/r/index_merge_innodb.result
result fix
@ mysql-test/r/index_merge_myisam.result
result fix, new optimizer switch
@ mysql-test/r/innodb_pk_extension_off.result
test result
@ mysql-test/r/innodb_pk_extension_on.result
test result
@ mysql-test/r/mysqld--help-notwin.result
result fix, new optimizer switch
@ mysql-test/r/mysqld--help-win.result
result fix, new optimizer switch
@ mysql-test/r/optimizer_switch.result
result fix, new optimizer switch
@ 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.
@ mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
result fix, new optimizer switch
@ mysql-test/t/innodb_pk_extension_off.test
test case
@ mysql-test/t/innodb_pk_extension_on.test
test case
@ 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.
@ 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_explain.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 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 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 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 actual_key_parts().
@ sql/sql_priv.h
added new optimize switch OPTIMIZER_SWITCH_USE_INDEX_EXTENSIONS
@ sql/sql_select.cc
create_ref_for_key():
keyinfo->user_defined_key_parts is replaced with actual_key_parts()
keyinfo->flags is replaced with actual_key_flags().
added functions actual_key_parts(), actual_key_flags().
@ sql/sql_select.h
added functions actual_key_parts(), actual_key_flags().
@ 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.
added initialization of keyinfo->actual_flags.
@ 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.
added initialization of keyinfo->actual_flags, keyinfo->table.
@ 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.
added KEY::actual_flags.
@ sql/sys_vars.cc
added new optimize switch "use_index_extensions"
@ 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.
TABLE::add_tmp_key()
added initialization of key_info->actual_key_parts.
added initialization of KEY::actual_flags.
calculate_key_len()
replaced key_info->key_parts with actual_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
actual_key_parts(), actual_key_flags() functions.
@ 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_defined_key_parts.
@ storage/federated/ha_federated.cc
KEY::key_parts renamed to KEY::user_defined_key_parts.
@ storage/heap/ha_heap.cc
KEY::key_parts renamed to KEY::user_defined_key_parts.
@ storage/innobase/handler/ha_innodb.cc
KEY::key_parts renamed to KEY::user_defined_key_parts.
@ storage/innobase/handler/handler0alter.cc
KEY::key_parts renamed to KEY::user_defined_key_parts.
@ storage/myisam/ha_myisam.cc
KEY::key_parts renamed to KEY::user_defined_key_parts.
added:
mysql-test/include/innodb_pk_extension.inc
mysql-test/r/innodb_pk_extension_off.result
mysql-test/r/innodb_pk_extension_on.result
mysql-test/t/innodb_pk_extension_off.test
mysql-test/t/innodb_pk_extension_on.test
modified:
mysql-test/r/index_merge_innodb.result
mysql-test/r/index_merge_myisam.result
mysql-test/r/mysqld--help-notwin.result
mysql-test/r/mysqld--help-win.result
mysql-test/r/optimizer_switch.result
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
mysql-test/suite/sys_vars/r/optimizer_switch_basic.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_priv.h
sql/sql_select.cc
sql/sql_select.h
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/sys_vars.cc
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
4503 Vamsikrishna Bhagi 2012-10-30
(no message)
=== 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-30 07:59:01 +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_innodb.result'
--- a/mysql-test/r/index_merge_innodb.result 2012-08-03 09:16:30 +0000
+++ b/mysql-test/r/index_merge_innodb.result 2012-10-30 07:59:01 +0000
@@ -1425,7 +1425,7 @@ id select_type table type possible_keys
1 SIMPLE t1 ref key1 key1 4 const ROWS Using where
explain select * from t1 where pk1 < 7500 and key1 = 10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge PRIMARY,key1 key1,PRIMARY 4,4 NULL ROWS Using intersect(key1,PRIMARY); Using where
+1 SIMPLE t1 index_merge PRIMARY,key1 key1,PRIMARY 8,4 NULL ROWS Using intersect(key1,PRIMARY); Using where
explain select * from t1 where pktail1ok=1 and key1=10;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index_merge key1,pktail1ok key1,pktail1ok 4,4 NULL 1 Using intersect(key1,pktail1ok); Using where
=== modified file 'mysql-test/r/index_merge_myisam.result'
--- a/mysql-test/r/index_merge_myisam.result 2012-08-08 07:44:36 +0000
+++ b/mysql-test/r/index_merge_myisam.result 2012-10-30 07:59:01 +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,use_index_extensions=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,use_index_extensions=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,use_index_extensions=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,use_index_extensions=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,use_index_extensions=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,use_index_extensions=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,use_index_extensions=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,use_index_extensions=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,use_index_extensions=on
drop table t0, t1;
=== added file 'mysql-test/r/innodb_pk_extension_off.result'
--- a/mysql-test/r/innodb_pk_extension_off.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/innodb_pk_extension_off.result 2012-10-30 07:59:01 +0000
@@ -0,0 +1,395 @@
+#
+# WL#6266 Make use of hidden key parts
+#
+#
+# Optimizer switch use_index_extensions=off
+#
+set optimizer_switch= "use_index_extensions=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;
=== added file 'mysql-test/r/innodb_pk_extension_on.result'
--- a/mysql-test/r/innodb_pk_extension_on.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/innodb_pk_extension_on.result 2012-10-30 07:59:01 +0000
@@ -0,0 +1,395 @@
+#
+# WL#6266 Make use of hidden key parts
+#
+#
+# Optimizer switch use_index_extensions=on
+#
+set optimizer_switch= "use_index_extensions=on";
+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 k1 10 const,const # Using index
+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 2
+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 k1 10 const,const # Using index
+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 2
+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 k1 10 NULL # Using where; Using index
+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 6
+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 k1 10 NULL # Using where; Using index
+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 6
+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 NULL NULL NULL NULL NULL NULL # Select tables optimized away
+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 0
+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 NULL NULL NULL NULL NULL NULL # Select tables optimized away
+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 0
+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 NULL NULL NULL NULL NULL NULL # Select tables optimized away
+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 0
+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 NULL NULL NULL NULL NULL NULL # Select tables optimized away
+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 0
+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 for group-by
+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 4
+Handler_read_last 1
+Handler_read_next 0
+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 for group-by
+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 5
+Handler_read_last 1
+Handler_read_next 0
+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 10 const,test.t1.pk_1 # 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 1296
+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 k1 10 const,const # Using where; Using index
+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 2
+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 k1 10 NULL # Using where; Using index; 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 6
+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 71 const,const,const,const,const,const,const,const,const,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 71 const,const,const,const,const,const,const,const,const,const,const,const,const,const,const,const # 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 3015 const,const,const,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 3015 const,const,const,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 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;
+set optimizer_switch=default;
=== modified file 'mysql-test/r/mysqld--help-notwin.result'
--- a/mysql-test/r/mysqld--help-notwin.result 2012-10-24 08:43:19 +0000
+++ b/mysql-test/r/mysqld--help-notwin.result 2012-10-30 07:59:01 +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,
+ use_index_extensions} 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,use_index_extensions=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/mysqld--help-win.result'
--- a/mysql-test/r/mysqld--help-win.result 2012-10-24 12:29:31 +0000
+++ b/mysql-test/r/mysqld--help-win.result 2012-10-30 07:59:01 +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,
+ use_index_extensions} and val is one of {on, off,
+ default}
--optimizer-trace=name
Controls tracing of the Optimizer:
optimizer_trace=option=val[,option=val...], where option
@@ -1123,7 +1124,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,use_index_extensions=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-30 07:59:01 +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,use_index_extensions=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,use_index_extensions=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,use_index_extensions=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,use_index_extensions=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,use_index_extensions=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,use_index_extensions=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,use_index_extensions=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,use_index_extensions=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,use_index_extensions=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/suite/innodb/r/innodb_mysql.result'
--- a/mysql-test/suite/innodb/r/innodb_mysql.result 2012-08-03 09:16:30 +0000
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result 2012-10-30 07:59:01 +0000
@@ -2568,7 +2568,7 @@ EXPLAIN SELECT * FROM t1 WHERE f1 IN
3784744,4180925,4559596,3963734,3856391,4494153)
AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge PRIMARY,idx1,idx2 idx1,PRIMARY 60,4 NULL 1 Using intersect(idx1,PRIMARY); Using where
+1 SIMPLE t1 ref PRIMARY,idx1,idx2 idx1 60 const,const,const 18 Using index condition
DROP TABLE t1;
#
# Bug#51431 Wrong sort order after import of dump file
=== modified file 'mysql-test/suite/opt_trace/r/range_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/range_no_prot.result 2012-08-23 07:45:33 +0000
+++ b/mysql-test/suite/opt_trace/r/range_no_prot.result 2012-10-30 07:59:01 +0000
@@ -5141,7 +5141,8 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
"index": "i1_idx",
"usable": true,
"key_parts": [
- "i1"
+ "i1",
+ "pk"
] /* key_parts */
},
{
@@ -5149,7 +5150,8 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
"usable": true,
"key_parts": [
"v",
- "i1"
+ "i1",
+ "pk"
] /* key_parts */
}
] /* potential_range_indices */,
@@ -5183,7 +5185,7 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
{
"index": "i1_idx",
"ranges": [
- "1 <= i1 <= 1"
+ "1 <= i1 <= 1 AND pk < 3"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
@@ -5197,7 +5199,7 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
{
"index": "v_idx",
"ranges": [
- "a <= v <= a AND 1 <= i1 <= 1"
+ "a <= v <= a AND 1 <= i1 <= 1 AND pk < 3"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
@@ -5416,7 +5418,8 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
"usable": true,
"key_parts": [
"v",
- "i1"
+ "i1",
+ "pk"
] /* key_parts */
},
{
@@ -5424,7 +5427,8 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
"usable": true,
"key_parts": [
"i2",
- "i1"
+ "i1",
+ "pk"
] /* key_parts */
}
] /* potential_range_indices */,
@@ -5452,7 +5456,7 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
{
"index": "v_idx",
"ranges": [
- "a <= v <= a AND 1 <= i1 <= 1"
+ "a <= v <= a AND 1 <= i1 <= 1 AND pk < 3"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
@@ -5465,7 +5469,7 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
{
"index": "i1_i2_idx",
"ranges": [
- "1 <= i2 <= 1 AND 1 <= i1 <= 1"
+ "1 <= i2 <= 1 AND 1 <= i1 <= 1 AND pk < 3"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
@@ -5529,7 +5533,7 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
"index": "v_idx",
"rows": 1,
"ranges": [
- "a <= v <= a AND 1 <= i1 <= 1"
+ "a <= v <= a AND 1 <= i1 <= 1 AND pk < 3"
] /* ranges */
}
] /* intersect_of */
@@ -5703,7 +5707,8 @@ EXPLAIN SELECT MAX(b), a FROM t1 WHERE b
"index": "b",
"usable": true,
"key_parts": [
- "b"
+ "b",
+ "a"
] /* key_parts */
}
] /* potential_range_indices */,
@@ -5728,8 +5733,9 @@ EXPLAIN SELECT MAX(b), a FROM t1 WHERE b
},
{
"index": "b",
+ "covering": true,
"usable": false,
- "cause": "not_covering"
+ "cause": "group_attribute_not_prefix_in_index"
}
] /* potential_group_range_indices */
} /* group_index_range */,
@@ -5768,7 +5774,7 @@ EXPLAIN SELECT MAX(b), a FROM t1 WHERE b
{
"index": "b",
"ranges": [
- "b < 2"
+ "b < 2 AND 1 <= a <= 1"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
=== modified file 'mysql-test/suite/opt_trace/r/range_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/range_ps_prot.result 2012-08-23 07:45:33 +0000
+++ b/mysql-test/suite/opt_trace/r/range_ps_prot.result 2012-10-30 07:59:01 +0000
@@ -5141,7 +5141,8 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
"index": "i1_idx",
"usable": true,
"key_parts": [
- "i1"
+ "i1",
+ "pk"
] /* key_parts */
},
{
@@ -5149,7 +5150,8 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
"usable": true,
"key_parts": [
"v",
- "i1"
+ "i1",
+ "pk"
] /* key_parts */
}
] /* potential_range_indices */,
@@ -5183,7 +5185,7 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
{
"index": "i1_idx",
"ranges": [
- "1 <= i1 <= 1"
+ "1 <= i1 <= 1 AND pk < 3"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
@@ -5197,7 +5199,7 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
{
"index": "v_idx",
"ranges": [
- "a <= v <= a AND 1 <= i1 <= 1"
+ "a <= v <= a AND 1 <= i1 <= 1 AND pk < 3"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
@@ -5416,7 +5418,8 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
"usable": true,
"key_parts": [
"v",
- "i1"
+ "i1",
+ "pk"
] /* key_parts */
},
{
@@ -5424,7 +5427,8 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
"usable": true,
"key_parts": [
"i2",
- "i1"
+ "i1",
+ "pk"
] /* key_parts */
}
] /* potential_range_indices */,
@@ -5452,7 +5456,7 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
{
"index": "v_idx",
"ranges": [
- "a <= v <= a AND 1 <= i1 <= 1"
+ "a <= v <= a AND 1 <= i1 <= 1 AND pk < 3"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
@@ -5465,7 +5469,7 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
{
"index": "i1_i2_idx",
"ranges": [
- "1 <= i2 <= 1 AND 1 <= i1 <= 1"
+ "1 <= i2 <= 1 AND 1 <= i1 <= 1 AND pk < 3"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
@@ -5529,7 +5533,7 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
"index": "v_idx",
"rows": 1,
"ranges": [
- "a <= v <= a AND 1 <= i1 <= 1"
+ "a <= v <= a AND 1 <= i1 <= 1 AND pk < 3"
] /* ranges */
}
] /* intersect_of */
@@ -5703,7 +5707,8 @@ EXPLAIN SELECT MAX(b), a FROM t1 WHERE b
"index": "b",
"usable": true,
"key_parts": [
- "b"
+ "b",
+ "a"
] /* key_parts */
}
] /* potential_range_indices */,
@@ -5728,8 +5733,9 @@ EXPLAIN SELECT MAX(b), a FROM t1 WHERE b
},
{
"index": "b",
+ "covering": true,
"usable": false,
- "cause": "not_covering"
+ "cause": "group_attribute_not_prefix_in_index"
}
] /* potential_group_range_indices */
} /* group_index_range */,
@@ -5768,7 +5774,7 @@ EXPLAIN SELECT MAX(b), a FROM t1 WHERE b
{
"index": "b",
"ranges": [
- "b < 2"
+ "b < 2 AND 1 <= a <= 1"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
=== modified file 'mysql-test/suite/sys_vars/r/optimizer_switch_basic.result'
--- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result 2012-07-10 11:59:40 +0000
+++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result 2012-10-30 07:59:01 +0000
@@ -1,57 +1,57 @@
SET @start_global_value = @@global.optimizer_switch;
SELECT @start_global_value;
@start_global_value
-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,use_index_extensions=on
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,use_index_extensions=on
select @@session.optimizer_switch;
@@session.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,use_index_extensions=on
show global 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,use_index_extensions=on
show session 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,use_index_extensions=on
select * from information_schema.global_variables where variable_name='optimizer_switch';
VARIABLE_NAME VARIABLE_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,use_index_extensions=on
select * from information_schema.session_variables where variable_name='optimizer_switch';
VARIABLE_NAME VARIABLE_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,use_index_extensions=on
set global optimizer_switch=10;
set session optimizer_switch=5;
select @@global.optimizer_switch;
@@global.optimizer_switch
-index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off
+index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off
select @@session.optimizer_switch;
@@session.optimizer_switch
-index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off
+index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off
set global optimizer_switch="index_merge_sort_union=on";
set session optimizer_switch="index_merge=off";
select @@global.optimizer_switch;
@@global.optimizer_switch
-index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off
+index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off
select @@session.optimizer_switch;
@@session.optimizer_switch
-index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off
+index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off
show global variables like 'optimizer_switch';
Variable_name Value
-optimizer_switch index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off
+optimizer_switch index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off
show session variables like 'optimizer_switch';
Variable_name Value
-optimizer_switch index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off
+optimizer_switch index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off
select * from information_schema.global_variables where variable_name='optimizer_switch';
VARIABLE_NAME VARIABLE_VALUE
-OPTIMIZER_SWITCH index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off
+OPTIMIZER_SWITCH index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off
select * from information_schema.session_variables where variable_name='optimizer_switch';
VARIABLE_NAME VARIABLE_VALUE
-OPTIMIZER_SWITCH index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off
+OPTIMIZER_SWITCH index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off
set session optimizer_switch="default";
select @@session.optimizer_switch;
@@session.optimizer_switch
-index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off
+index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off
set global optimizer_switch=1.1;
ERROR 42000: Incorrect argument type to variable 'optimizer_switch'
set global optimizer_switch=1e1;
@@ -70,4 +70,4 @@ ERROR 42000: Variable 'optimizer_switch'
SET @@global.optimizer_switch = @start_global_value;
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,use_index_extensions=on
=== added file 'mysql-test/t/innodb_pk_extension_off.test'
--- a/mysql-test/t/innodb_pk_extension_off.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/innodb_pk_extension_off.test 2012-10-30 07:59:01 +0000
@@ -0,0 +1,16 @@
+--source include/have_innodb_16k.inc
+
+--echo #
+--echo # WL#6266 Make use of hidden key parts
+--echo #
+
+--echo #
+--echo # Optimizer switch use_index_extensions=off
+--echo #
+
+set optimizer_switch= "use_index_extensions=off";
+
+--source include/innodb_pk_extension.inc
+
+set optimizer_switch=default;
+
=== added file 'mysql-test/t/innodb_pk_extension_on.test'
--- a/mysql-test/t/innodb_pk_extension_on.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/innodb_pk_extension_on.test 2012-10-30 07:59:01 +0000
@@ -0,0 +1,16 @@
+--source include/have_innodb_16k.inc
+
+--echo #
+--echo # WL#6266 Make use of hidden key parts
+--echo #
+
+--echo #
+--echo # Optimizer switch use_index_extensions=on
+--echo #
+
+set optimizer_switch= "use_index_extensions=on";
+
+--source include/innodb_pk_extension.inc
+
+set optimizer_switch=default;
+
=== modified file 'sql/abstract_query_plan.cc'
--- a/sql/abstract_query_plan.cc 2012-10-03 10:32:19 +0000
+++ b/sql/abstract_query_plan.cc 2012-10-30 07:59:01 +0000
@@ -337,7 +337,8 @@ namespace AQP
All parts of a key are specified for an unique index -> access is a key lookup.
*/
const KEY *key_info= join_tab->table->s->key_info;
- if (key_info[m_index_no].key_parts == join_tab->ref.key_parts &&
+ if (key_info[m_index_no].user_defined_key_parts ==
+ join_tab->ref.key_parts &&
key_info[m_index_no].flags & HA_NOSAME)
{
m_access_type=
@@ -349,7 +350,8 @@ namespace AQP
else
{
DBUG_ASSERT(join_tab->ref.key_parts > 0);
- DBUG_ASSERT(join_tab->ref.key_parts <= key_info[m_index_no].key_parts);
+ DBUG_ASSERT(join_tab->ref.key_parts <=
+ key_info[m_index_no].user_defined_key_parts);
m_access_type= AT_ORDERED_INDEX_SCAN;
DBUG_PRINT("info", ("Operation %d is an ordered index scan.", m_tab_no));
}
=== modified file 'sql/event_db_repository.cc'
--- a/sql/event_db_repository.cc 2012-10-08 14:19:40 +0000
+++ b/sql/event_db_repository.cc 2012-10-30 07:59:01 +0000
@@ -425,7 +425,7 @@ Event_db_repository::index_read_for_db_f
key_info= event_table->key_info;
- if (key_info->key_parts == 0 ||
+ if (key_info->user_defined_key_parts == 0 ||
key_info->key_part[0].field != event_table->field[ET_FIELD_DB])
{
/* Corrupted table: no index or index on a wrong column */
=== modified file 'sql/ha_partition.cc'
--- a/sql/ha_partition.cc 2012-10-03 13:09:01 +0000
+++ b/sql/ha_partition.cc 2012-10-30 07:59:01 +0000
@@ -4899,7 +4899,7 @@ int ha_partition::index_init(uint inx, b
KEY **key_info= m_curr_key_info;
do
{
- for (i= 0; i < (*key_info)->key_parts; i++)
+ for (i= 0; i < (*key_info)->user_defined_key_parts; i++)
bitmap_set_bit(table->read_set,
(*key_info)->key_part[i].field->field_index);
} while (*(++key_info));
=== modified file 'sql/handler.cc'
--- a/sql/handler.cc 2012-10-17 11:15:30 +0000
+++ b/sql/handler.cc 2012-10-30 07:59:01 +0000
@@ -3714,7 +3714,7 @@ int handler::check_collation_compatibili
for (; key < key_end; key++)
{
KEY_PART_INFO *key_part= key->key_part;
- KEY_PART_INFO *key_part_end= key_part + key->key_parts;
+ KEY_PART_INFO *key_part_end= key_part + key->user_defined_key_parts;
for (; key_part < key_part_end; key_part++)
{
if (!key_part->fieldnr)
@@ -3755,7 +3755,7 @@ int handler::ha_check_for_upgrade(HA_CHE
for (; keyinfo < keyend; keyinfo++)
{
keypart= keyinfo->key_part;
- keypartend= keypart + keyinfo->key_parts;
+ keypartend= keypart + keyinfo->user_defined_key_parts;
for (; keypart < keypartend; keypart++)
{
if (!keypart->fieldnr)
@@ -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->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);
@@ -5423,7 +5423,7 @@ double handler::index_only_read_time(uin
bool key_uses_partial_cols(TABLE *table, uint keyno)
{
KEY_PART_INFO *kp= table->key_info[keyno].key_part;
- KEY_PART_INFO *kp_end= kp + table->key_info[keyno].key_parts;
+ KEY_PART_INFO *kp_end= kp + table->key_info[keyno].user_defined_key_parts;
for (; kp != kp_end; kp++)
{
if (!kp->field->part_of_key.is_set(keyno))
=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc 2012-10-22 08:08:04 +0000
+++ b/sql/item_func.cc 2012-10-30 07:59:01 +0000
@@ -6283,7 +6283,7 @@ bool Item_func_match::fix_index()
for (keynr=0 ; keynr < fts ; keynr++)
{
KEY *ft_key=&table->key_info[ft_to_key[keynr]];
- uint key_parts=ft_key->key_parts;
+ uint key_parts=ft_key->user_defined_key_parts;
for (uint part=0 ; part < key_parts ; part++)
{
@@ -6315,7 +6315,7 @@ bool Item_func_match::fix_index()
{
// partial keys doesn't work
if (max_cnt < arg_count-1 ||
- max_cnt < table->key_info[ft_to_key[keynr]].key_parts)
+ max_cnt < table->key_info[ft_to_key[keynr]].user_defined_key_parts)
continue;
key=ft_to_key[keynr];
=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc 2012-10-08 14:19:40 +0000
+++ b/sql/item_subselect.cc 2012-10-30 07:59:01 +0000
@@ -3421,7 +3421,7 @@ bool subselect_hash_sj_engine::setup(Lis
tmp_table= tmp_result_sink->table;
tmp_key= tmp_table->key_info;
- tmp_key_parts= tmp_key->key_parts;
+ tmp_key_parts= tmp_key->user_defined_key_parts;
/*
If the subquery has blobs, or the total key lenght is bigger than some
@@ -3436,7 +3436,8 @@ bool subselect_hash_sj_engine::setup(Lis
DBUG_ASSERT(
tmp_table->s->uniques ||
tmp_table->key_info->key_length >= tmp_table->file->max_key_length() ||
- tmp_table->key_info->key_parts > tmp_table->file->max_key_parts());
+ tmp_table->key_info->user_defined_key_parts >
+ tmp_table->file->max_key_parts());
free_tmp_table(thd, tmp_table);
delete result;
result= NULL;
=== modified file 'sql/key.cc'
--- a/sql/key.cc 2012-06-22 10:11:31 +0000
+++ b/sql/key.cc 2012-10-30 07:59:01 +0000
@@ -81,7 +81,7 @@ int find_ref_key(KEY *key, uint key_coun
KEY_PART_INFO *key_part;
*key_length=0;
for (j=0, key_part=key_info->key_part ;
- j < key_info->key_parts ;
+ j < key_info->user_defined_key_parts ;
j++, key_part++)
{
if (key_part->offset == fieldpos)
@@ -163,7 +163,7 @@ void key_copy(uchar *to_key, uchar *from
void key_zero_nulls(uchar *tuple, KEY *key_info)
{
KEY_PART_INFO *key_part= key_info->key_part;
- KEY_PART_INFO *key_part_end= key_part + key_info->key_parts;
+ KEY_PART_INFO *key_part_end= key_part + key_info->user_defined_key_parts;
for (; key_part != key_part_end; key_part++)
{
if (key_part->null_bit && *tuple)
@@ -360,7 +360,7 @@ void key_unpack(String *to, TABLE *table
DBUG_ENTER("key_unpack");
to->length(0);
- KEY_PART_INFO *key_part_end= key->key_part + key->key_parts;
+ KEY_PART_INFO *key_part_end= key->key_part + key->user_defined_key_parts;
for (KEY_PART_INFO *key_part= key->key_part;
key_part < key_part_end;
key_part++)
@@ -548,7 +548,7 @@ int key_rec_cmp(void *key_p, uchar *firs
/* loop over all given keys */
do
{
- key_parts= key_info->key_parts;
+ key_parts= key_info->user_defined_key_parts;
key_part= key_info->key_part;
key_part_num= 0;
=== modified file 'sql/log_event.cc'
--- a/sql/log_event.cc 2012-10-21 19:37:01 +0000
+++ b/sql/log_event.cc 2012-10-30 07:59:01 +0000
@@ -9466,7 +9466,7 @@ my_bool are_all_columns_signaled_for_key
{
DBUG_ENTER("are_all_columns_signaled_for_key");
- for (uint i=0 ; i < keyinfo->key_parts ;i++)
+ for (uint i=0 ; i < keyinfo->user_defined_key_parts ;i++)
{
uint fieldnr= keyinfo->key_part[i].fieldnr - 1;
if (fieldnr >= cols->n_bits ||
@@ -10341,7 +10341,7 @@ INDEX_SCAN:
BI image that is null and part of UNNI.
*/
bool null_found= FALSE;
- for (uint i=0; i < keyinfo->key_parts && !null_found; i++)
+ for (uint i=0; i < keyinfo->user_defined_key_parts && !null_found; i++)
{
uint fieldnr= keyinfo->key_part[i].fieldnr - 1;
Field **f= table->field+fieldnr;
=== modified file 'sql/log_event_old.cc'
--- a/sql/log_event_old.cc 2012-10-08 14:19:40 +0000
+++ b/sql/log_event_old.cc 2012-10-30 07:59:01 +0000
@@ -2366,7 +2366,7 @@ int Old_rows_log_event::find_row(const R
BI image that is null and part of UNNI.
*/
bool null_found= FALSE;
- for (uint i=0; i < keyinfo->key_parts && !null_found; i++)
+ for (uint i=0; i < keyinfo->user_defined_key_parts && !null_found; i++)
{
uint fieldnr= keyinfo->key_part[i].fieldnr - 1;
Field **f= table->field+fieldnr;
=== modified file 'sql/opt_explain.cc'
--- a/sql/opt_explain.cc 2012-10-16 14:24:56 +0000
+++ b/sql/opt_explain.cc 2012-10-30 07:59:01 +0000
@@ -912,7 +912,7 @@ bool Explain_table_base::explain_key_and
{
DBUG_ASSERT(key != MAX_KEY);
return explain_key_and_len_index(key, table->key_info[key].key_length,
- table->key_info[key].key_parts);
+ table->key_info[key].user_defined_key_parts);
}
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2012-10-19 11:58:07 +0000
+++ b/sql/opt_range.cc 2012-10-30 07:59:01 +0000
@@ -864,7 +864,7 @@ static SEL_ARG *get_mm_leaf(RANGE_OPT_PA
Item_func::Functype type,Item *value);
static SEL_TREE *get_mm_tree(RANGE_OPT_PARAM *param,Item *cond);
-static bool is_key_scan_ror(PARAM *param, uint keynr, uint8 nparts);
+static bool is_key_scan_ror(PARAM *param, uint keynr, uint nparts);
static ha_rows check_quick_select(PARAM *param, uint idx, bool index_only,
SEL_ARG *tree, bool update_tbl_stats,
uint *mrr_flags, uint *bufsize,
@@ -2462,8 +2462,8 @@ static int fill_used_fields_bitmap(PARAM
{
/* The table uses clustered PK and it is not internally generated */
KEY_PART_INFO *key_part= param->table->key_info[pk].key_part;
- KEY_PART_INFO *key_part_end= key_part +
- param->table->key_info[pk].key_parts;
+ KEY_PART_INFO *key_part_end=
+ key_part + param->table->key_info[pk].user_defined_key_parts;
for (;key_part != key_part_end; ++key_part)
bitmap_clear_bit(¶m->needed_fields, key_part->fieldnr-1);
}
@@ -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->key_parts ;
+ for (uint part=0 ; part < actual_key_parts(key_info) ;
part++, key_parts++, key_part_info++)
{
key_parts->key= param.keys;
@@ -4595,8 +4595,8 @@ ROR_SCAN_INFO *make_ror_scan(const PARAM
bitmap_clear_all(&ror_scan->covered_fields);
KEY_PART_INFO *key_part= param->table->key_info[keynr].key_part;
- KEY_PART_INFO *key_part_end= key_part +
- param->table->key_info[keynr].key_parts;
+ KEY_PART_INFO *key_part_end=
+ key_part + param->table->key_info[keynr].user_defined_key_parts;
for (;key_part != key_part_end; ++key_part)
{
if (bitmap_is_set(¶m->needed_fields, key_part->fieldnr-1))
@@ -9071,6 +9071,7 @@ uint sel_arg_range_seq_next(range_seq_t
}
else
{
+ const KEY *cur_key_info= ¶m->table->key_info[seq->real_keyno];
range->range_flag= cur->min_key_flag | cur->max_key_flag;
range->start_key.key= param->min_key;
@@ -9092,12 +9093,15 @@ uint sel_arg_range_seq_next(range_seq_t
2) The lower bound and the upper bound of the range has the
same value (min_key == max_key).
*/
- if (!(cur->min_key_flag & (NO_MIN_RANGE | NO_MAX_RANGE |
- NEAR_MIN | NEAR_MAX | GEOM_FLAG)) && // 1)
- !(cur->max_key_flag & (NO_MIN_RANGE | NO_MAX_RANGE |
- NEAR_MIN | NEAR_MAX | GEOM_FLAG)) && // 1)
- range->start_key.length == range->end_key.length && // 2)
- !memcmp(param->min_key, param->max_key, range->start_key.length)) // 2)
+ const uint is_open_range= (NO_MIN_RANGE | NO_MAX_RANGE |
+ NEAR_MIN | NEAR_MAX | GEOM_FLAG);
+ const bool is_eq_range_pred=
+ !(cur->min_key_flag & is_open_range) && // 1)
+ !(cur->max_key_flag & is_open_range) && // 1)
+ range->start_key.length == range->end_key.length && // 2)
+ !memcmp(param->min_key, param->max_key, range->start_key.length);
+
+ if (is_eq_range_pred)
{
range->range_flag= EQ_RANGE;
/*
@@ -9110,15 +9114,17 @@ uint sel_arg_range_seq_next(range_seq_t
/*
An equality range is a unique range (0 or 1 rows in the range)
if the index is unique (1) and all keyparts are used (2).
+ Note that keys which are extended with PK parts have no
+ HA_NOSAME flag. So we can use user_defined_key_parts.
*/
- if (param->table->key_info[seq->real_keyno].flags & HA_NOSAME && // 1)
- (uint)key_tree->part+1 == param->table->
- key_info[seq->real_keyno].key_parts) // 2)
+ if (cur_key_info->flags & HA_NOSAME && // 1)
+ (uint)key_tree->part+1 == cur_key_info->user_defined_key_parts) // 2)
range->range_flag|= UNIQUE_RANGE | (cur->min_key_flag & NULL_RANGE);
}
if (param->is_ror_scan)
{
+ const uint key_part_number= key_tree->part + 1;
/*
If we get here, the condition on the key was converted to form
"(keyXpart1 = c1) AND ... AND (keyXpart{key_tree->part - 1} = cN) AND
@@ -9127,15 +9133,14 @@ uint sel_arg_range_seq_next(range_seq_t
somecond is "keyXpart{key_tree->part} = const" and
uncovered "tail" of KeyX parts is either empty or is identical to
first members of clustered primary key.
+
+ If last key part is PK part added to the key as an extension
+ and is_key_scan_ror() result is TRUE then it's possible to
+ use ROR scan.
*/
- if (!(!(cur->min_key_flag & (NO_MIN_RANGE | NO_MAX_RANGE |
- NEAR_MIN | NEAR_MAX | GEOM_FLAG)) &&
- !(cur->max_key_flag & (NO_MIN_RANGE | NO_MAX_RANGE |
- NEAR_MIN | NEAR_MAX | GEOM_FLAG)) &&
- (range->start_key.length == range->end_key.length) &&
- !memcmp(range->start_key.key, range->end_key.key,
- range->start_key.length) &&
- is_key_scan_ror(param, seq->real_keyno, key_tree->part + 1)))
+ if ((!is_eq_range_pred &&
+ key_part_number <= cur_key_info->user_defined_key_parts) ||
+ !is_key_scan_ror(param, seq->real_keyno, key_part_number))
param->is_ror_scan= FALSE;
}
}
@@ -9311,12 +9316,21 @@ ha_rows check_quick_select(PARAM *param,
FALSE Otherwise
*/
-static bool is_key_scan_ror(PARAM *param, uint keynr, uint8 nparts)
+static bool is_key_scan_ror(PARAM *param, uint keynr, uint nparts)
{
KEY *table_key= param->table->key_info + keynr;
- KEY_PART_INFO *key_part= table_key->key_part + nparts;
+
+ /*
+ Range predicates on hidden key parts do not change the fact
+ that a scan is rowid ordered, so we only care about user
+ defined keyparts
+ */
+ const uint user_defined_nparts=
+ std::min<uint>(nparts, table_key->user_defined_key_parts);
+
+ KEY_PART_INFO *key_part= table_key->key_part + user_defined_nparts;
KEY_PART_INFO *key_part_end= (table_key->key_part +
- table_key->key_parts);
+ table_key->user_defined_key_parts);
uint pk_number;
for (KEY_PART_INFO *kp= table_key->key_part; kp < key_part; kp++)
@@ -9330,14 +9344,14 @@ static bool is_key_scan_ror(PARAM *param
if (key_part == key_part_end)
return TRUE;
- key_part= table_key->key_part + nparts;
+ key_part= table_key->key_part + user_defined_nparts;
pk_number= param->table->s->primary_key;
if (!param->table->file->primary_key_is_clustered() || pk_number == MAX_KEY)
return FALSE;
KEY_PART_INFO *pk_part= param->table->key_info[pk_number].key_part;
- KEY_PART_INFO *pk_part_end= pk_part +
- param->table->key_info[pk_number].key_parts;
+ KEY_PART_INFO *pk_part_end=
+ pk_part + param->table->key_info[pk_number].user_defined_key_parts;
for (;(key_part!=key_part_end) && (pk_part != pk_part_end);
++key_part, ++pk_part)
{
@@ -9406,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]].key_parts);
+ sizeof(KEY_PART) *
+ actual_key_parts(¶m->
+ table->key_info[param->real_keynr[idx]]));
}
}
DBUG_RETURN(quick);
@@ -9495,9 +9510,14 @@ get_quick_keys(PARAM *param,QUICK_RANGE_
if (length == (uint) (tmp_max_key - param->max_key) &&
!memcmp(param->min_key,param->max_key,length))
{
- KEY *table_key=quick->head->key_info+quick->index;
+ const KEY *table_key=quick->head->key_info+quick->index;
flag=EQ_RANGE;
- if ((table_key->flags & HA_NOSAME) && key->part == table_key->key_parts-1)
+ /*
+ Note that keys which are extended with PK parts have no
+ HA_NOSAME flag. So we can use user_defined_key_parts.
+ */
+ if ((table_key->flags & HA_NOSAME) &&
+ key->part == table_key->user_defined_key_parts - 1)
{
if (!(table_key->flags & HA_NULL_PART_KEY) ||
!null_part_in_key(key,
@@ -10585,7 +10605,8 @@ int QUICK_SELECT_DESC::get_next()
if (last_range)
{ // Already read through key
result = ((last_range->flag & EQ_RANGE &&
- used_key_parts <= head->key_info[index].key_parts) ?
+ used_key_parts <=
+ head->key_info[index].user_defined_key_parts) ?
file->ha_index_next_same(record, last_range->min_key,
last_range->min_length) :
file->ha_index_prev(record));
@@ -10603,7 +10624,7 @@ int QUICK_SELECT_DESC::get_next()
// Case where we can avoid descending scan, see comment above
const bool eqrange_all_keyparts= (last_range->flag & EQ_RANGE) &&
- (used_key_parts <= head->key_info[index].key_parts);
+ (used_key_parts <= head->key_info[index].user_defined_key_parts);
/*
If we have pushed an index condition (ICP) and this quick select
@@ -10654,7 +10675,8 @@ int QUICK_SELECT_DESC::get_next()
{
DBUG_ASSERT(last_range->flag & NEAR_MAX ||
(last_range->flag & EQ_RANGE &&
- used_key_parts > head->key_info[index].key_parts) ||
+ used_key_parts >
+ head->key_info[index].user_defined_key_parts) ||
range_reads_after_key(last_range));
result= file->ha_index_read_map(record, last_range->max_key,
last_range->max_keypart_map,
@@ -11304,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->key_parts;
+ end_part= cur_part + actual_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++)
@@ -11432,8 +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->key_parts;
- first_non_group_part= (cur_group_key_parts < cur_index_info->key_parts) ?
+ last_part= cur_index_info->key_part + actual_key_parts(cur_index_info);
+ first_non_group_part=
+ (cur_group_key_parts < actual_key_parts(cur_index_info)) ?
cur_index_info->key_part + cur_group_key_parts :
NULL;
first_non_infix_part= min_max_arg_part ?
@@ -11913,7 +11936,8 @@ get_field_keypart(KEY *index, Field *fie
{
KEY_PART_INFO *part, *end;
- for (part= index->key_part, end= part + index->key_parts; part < end; part++)
+ for (part= index->key_part, end= part + actual_key_parts(index) ;
+ part < end; part++)
{
if (field->eq(part->field))
return part - index->key_part + 1;
=== modified file 'sql/opt_sum.cc'
--- a/sql/opt_sum.cc 2012-10-08 14:19:40 +0000
+++ b/sql/opt_sum.cc 2012-10-30 07:59:01 +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->key_parts ;
+ for (part= keyinfo->key_part, part_end= part + actual_key_parts(keyinfo) ;
part != part_end ;
part++, jdx++, key_part_to_use= (key_part_to_use << 1) | 1)
{
=== modified file 'sql/rpl_info_table_access.cc'
--- a/sql/rpl_info_table_access.cc 2012-08-09 10:05:01 +0000
+++ b/sql/rpl_info_table_access.cc 2012-10-30 07:59:01 +0000
@@ -214,7 +214,7 @@ enum enum_return_id Rpl_info_table_acces
}
keyinfo= table->s->key_info + (uint) table->s->primary_key;
- for (uint idx= 0; idx < keyinfo->key_parts; idx++)
+ for (uint idx= 0; idx < keyinfo->user_defined_key_parts; idx++)
{
uint fieldnr= keyinfo->key_part[idx].fieldnr - 1;
=== modified file 'sql/sql_handler.cc'
--- a/sql/sql_handler.cc 2012-10-08 14:19:40 +0000
+++ b/sql/sql_handler.cc 2012-10-30 07:59:01 +0000
@@ -739,9 +739,9 @@ retry:
DBUG_ASSERT(m_key_name != 0);
KEY *keyinfo=table->key_info+keyno;
KEY_PART_INFO *key_part=keyinfo->key_part;
- if (m_key_expr->elements > keyinfo->key_parts)
+ if (m_key_expr->elements > keyinfo->user_defined_key_parts)
{
- my_error(ER_TOO_MANY_KEY_PARTS, MYF(0), keyinfo->key_parts);
+ my_error(ER_TOO_MANY_KEY_PARTS, MYF(0), keyinfo->user_defined_key_parts);
goto err;
}
List_iterator<Item> it_ke(*m_key_expr);
=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc 2012-10-05 06:53:08 +0000
+++ b/sql/sql_optimizer.cc 2012-10-30 07:59:01 +0000
@@ -3517,7 +3517,7 @@ const_table_extraction_done:
4. have an expensive outer join condition.
5. are blocked by handler for const table optimize.
*/
- if (eq_part.is_prefix(table->key_info[key].key_parts) &&
+ if (eq_part.is_prefix(table->key_info[key].user_defined_key_parts) &&
!table->fulltext_searched && // 1
!tl->outer_join_nest() && // 2
!(tl->embedding && tl->embedding->sj_on_expr) && // 3
@@ -4358,7 +4358,7 @@ static bool find_eq_ref_candidate(TABLE
keyuse++;
} while (keyuse->key == key && keyuse->table == table);
- if (bound_parts == LOWER_BITS(uint, keyinfo->key_parts))
+ if (bound_parts == LOWER_BITS(uint, keyinfo->user_defined_key_parts))
return TRUE;
if (keyuse->table != table)
return FALSE;
@@ -5302,7 +5302,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].key_parts;
+ uint key_parts= actual_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))
@@ -8533,7 +8533,7 @@ list_contains_unique_index(JOIN_TAB *tab
KEY_PART_INFO *key_part, *key_part_end;
for (key_part=keyinfo->key_part,
- key_part_end=key_part+ keyinfo->key_parts;
+ key_part_end=key_part+ keyinfo->user_defined_key_parts;
key_part < key_part_end;
key_part++)
{
=== modified file 'sql/sql_partition.cc'
--- a/sql/sql_partition.cc 2012-08-29 14:11:54 +0000
+++ b/sql/sql_partition.cc 2012-10-30 07:59:01 +0000
@@ -692,7 +692,7 @@ end:
static void clear_indicator_in_key_fields(KEY *key_info)
{
KEY_PART_INFO *key_part;
- uint key_parts= key_info->key_parts, i;
+ uint key_parts= key_info->user_defined_key_parts, i;
for (i= 0, key_part=key_info->key_part; i < key_parts; i++, key_part++)
key_part->field->flags&= (~GET_FIXED_FIELDS_FLAG);
}
@@ -712,7 +712,7 @@ static void clear_indicator_in_key_field
static void set_indicator_in_key_fields(KEY *key_info)
{
KEY_PART_INFO *key_part;
- uint key_parts= key_info->key_parts, i;
+ uint key_parts= key_info->user_defined_key_parts, i;
for (i= 0, key_part=key_info->key_part; i < key_parts; i++, key_part++)
key_part->field->flags|= GET_FIXED_FIELDS_FLAG;
}
@@ -832,7 +832,7 @@ static bool handle_list_of_fields(List_i
uint primary_key= table->s->primary_key;
if (primary_key != MAX_KEY)
{
- uint num_key_parts= table->key_info[primary_key].key_parts, i;
+ uint num_key_parts= table->key_info[primary_key].user_defined_key_parts, i;
/*
In the case of an empty list we use primary key as partition key.
*/
@@ -7239,7 +7239,7 @@ void set_key_field_ptr(KEY *key_info, co
const uchar *old_buf)
{
KEY_PART_INFO *key_part= key_info->key_part;
- uint key_parts= key_info->key_parts;
+ uint key_parts= key_info->user_defined_key_parts;
uint i= 0;
my_ptrdiff_t diff= (new_buf - old_buf);
DBUG_ENTER("set_key_field_ptr");
=== modified file 'sql/sql_planner.cc'
--- a/sql/sql_planner.cc 2012-10-26 13:42:30 +0000
+++ b/sql/sql_planner.cc 2012-10-30 07:59:01 +0000
@@ -572,7 +572,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->key_parts) &&
+ if (found_part == LOWER_BITS(key_part_map, actual_key_parts(keyinfo)) &&
!ref_or_null_part)
{ /* use eq key */
max_key_part= (uint) ~0;
@@ -612,7 +612,7 @@ void Optimize_table_order::best_access_p
}
else
{
- if (!(records=keyinfo->rec_per_key[keyinfo->key_parts-1]))
+ if (!(records= keyinfo->rec_per_key[actual_key_parts(keyinfo)-1]))
{ /* Prefer longer keys */
records=
((double) s->records / (double) rec *
@@ -662,7 +662,8 @@ 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->key_parts)))
+ found_part == LOWER_BITS(key_part_map,
+ actual_key_parts(keyinfo))))
{
max_key_part= max_part_bit(found_part);
/*
@@ -764,7 +765,7 @@ void Optimize_table_order::best_access_p
*/
double rec_per_key;
if (!(rec_per_key=(double)
- keyinfo->rec_per_key[keyinfo->key_parts-1]))
+ keyinfo->rec_per_key[keyinfo->user_defined_key_parts-1]))
rec_per_key=(double) s->records/rec+1;
if (!s->records)
@@ -774,10 +775,10 @@ void Optimize_table_order::best_access_p
else
{
double a=s->records*0.01;
- if (keyinfo->key_parts > 1)
+ if (keyinfo->user_defined_key_parts > 1)
tmp= (max_key_part * (rec_per_key - a) +
- a*keyinfo->key_parts - rec_per_key)/
- (keyinfo->key_parts-1);
+ a * keyinfo->user_defined_key_parts - rec_per_key) /
+ (keyinfo->user_defined_key_parts - 1);
else
tmp= a;
set_if_bigger(tmp,1.0);
=== 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-30 07:59:01 +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_USE_INDEX_EXTENSIONS (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_USE_INDEX_EXTENSIONS)
#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_USE_INDEX_EXTENSIONS)
#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-18 18:26:51 +0000
+++ b/sql/sql_select.cc 2012-10-30 07:59:01 +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->key_parts || null_ref_key)
+ else if (((actual_key_flags(keyinfo) &
+ (HA_NOSAME | HA_NULL_PART_KEY)) != HA_NOSAME) ||
+ keyparts != actual_key_parts(keyinfo) || null_ref_key)
{
/* Must read with repeat */
j->type= null_ref_key ? JT_REF_OR_NULL : JT_REF;
@@ -3444,7 +3445,7 @@ static int test_if_order_by_key(ORDER *o
{
KEY_PART_INFO *key_part,*key_part_end;
key_part=table->key_info[idx].key_part;
- key_part_end=key_part+table->key_info[idx].key_parts;
+ key_part_end=key_part+table->key_info[idx].user_defined_key_parts;
key_part_map const_key_parts=table->const_key_parts[idx];
int reverse=0;
uint key_parts;
@@ -3477,7 +3478,8 @@ static int test_if_order_by_key(ORDER *o
{
on_pk_suffix= TRUE;
key_part= table->key_info[table->s->primary_key].key_part;
- key_part_end=key_part+table->key_info[table->s->primary_key].key_parts;
+ key_part_end=key_part +
+ table->key_info[table->s->primary_key].user_defined_key_parts;
const_key_parts=table->const_key_parts[table->s->primary_key];
for (; const_key_parts & 1 ; const_key_parts>>= 1)
@@ -3512,7 +3514,7 @@ static int test_if_order_by_key(ORDER *o
}
if (on_pk_suffix)
{
- uint used_key_parts_secondary= table->key_info[idx].key_parts;
+ uint used_key_parts_secondary= table->key_info[idx].user_defined_key_parts;
uint used_key_parts_pk=
(uint) (key_part - table->key_info[table->s->primary_key].key_part);
key_parts= used_key_parts_pk + used_key_parts_secondary;
@@ -3600,7 +3602,7 @@ uint find_shortest_key(TABLE *table, con
parts aren't allowed.
*/
if (best == MAX_KEY ||
- table->key_info[best].key_parts >= table->s->fields)
+ table->key_info[best].user_defined_key_parts >= table->s->fields)
best= usable_clustered_pk;
}
return best;
@@ -3691,7 +3693,7 @@ test_if_subkey(ORDER *order, JOIN_TAB *t
{
if (usable_keys->is_set(nr) &&
table->key_info[nr].key_length < min_length &&
- table->key_info[nr].key_parts >= ref_key_parts &&
+ table->key_info[nr].user_defined_key_parts >= ref_key_parts &&
is_subkey(table->key_info[nr].key_part, ref_key_part,
ref_key_part_end) &&
!is_ref_or_null_optimized(tab, nr) &&
@@ -5414,7 +5416,7 @@ test_if_cheaper_ordering(const JOIN_TAB
See Bug #28591 for details.
*/
rec_per_key= used_key_parts &&
- used_key_parts <= keyinfo->key_parts ?
+ used_key_parts <= actual_key_parts(keyinfo) ?
keyinfo->rec_per_key[used_key_parts-1] : 1;
set_if_bigger(rec_per_key, 1);
/*
@@ -5455,7 +5457,7 @@ test_if_cheaper_ordering(const JOIN_TAB
select_limit= (ha_rows) (select_limit *
(double) table_records /
refkey_rows_estimate);
- rec_per_key= keyinfo->rec_per_key[keyinfo->key_parts-1];
+ rec_per_key= keyinfo->rec_per_key[keyinfo->user_defined_key_parts - 1];
set_if_bigger(rec_per_key, 1);
/*
Here we take into account the fact that rows are
@@ -5486,11 +5488,11 @@ test_if_cheaper_ordering(const JOIN_TAB
quick_records= table->quick_rows[nr];
if (best_key < 0 ||
(select_limit <= min(quick_records,best_records) ?
- keyinfo->key_parts < best_key_parts :
+ keyinfo->user_defined_key_parts < best_key_parts :
quick_records < best_records))
{
best_key= nr;
- best_key_parts= keyinfo->key_parts;
+ best_key_parts= keyinfo->user_defined_key_parts;
if (saved_best_key_parts)
*saved_best_key_parts= used_key_parts;
best_records= quick_records;
@@ -5630,5 +5632,38 @@ uint get_index_for_order(ORDER *order, T
/**
+ Returns number of key parts depending on
+ OPTIMIZER_SWITCH_USE_INDEX_EXTENSIONS flag.
+
+ @param key_info pointer to KEY structure
+
+ @return number of key parts.
+*/
+
+uint actual_key_parts(KEY *key_info)
+{
+ return key_info->table->in_use->
+ optimizer_switch_flag(OPTIMIZER_SWITCH_USE_INDEX_EXTENSIONS) ?
+ key_info->actual_key_parts : key_info->user_defined_key_parts;
+}
+
+
+/**
+ Returns key flags depending on
+ OPTIMIZER_SWITCH_USE_INDEX_EXTENSIONS flag.
+
+ @param key_info pointer to KEY structure
+
+ @return key flags.
+*/
+
+uint actual_key_flags(KEY *key_info)
+{
+ return key_info->table->in_use->
+ optimizer_switch_flag(OPTIMIZER_SWITCH_USE_INDEX_EXTENSIONS) ?
+ 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-24 11:37:44 +0000
+++ b/sql/sql_select.h 2012-10-30 07:59:01 +0000
@@ -1330,4 +1330,7 @@ static inline Item * and_items(Item* con
return (cond? (new Item_cond_and(cond, item)) : item);
}
+uint actual_key_parts(KEY *key_info);
+uint actual_key_flags(KEY *key_info);
+
#endif /* SQL_SELECT_INCLUDED */
=== modified file 'sql/sql_show.cc'
--- a/sql/sql_show.cc 2012-10-22 08:08:04 +0000
+++ b/sql/sql_show.cc 2012-10-30 07:59:01 +0000
@@ -1562,7 +1562,7 @@ int store_create_info(THD *thd, TABLE_LI
packet->append(STRING_WITH_LEN(" ("));
- for (uint j=0 ; j < key_info->key_parts ; j++,key_part++)
+ for (uint j=0 ; j < key_info->user_defined_key_parts ; j++,key_part++)
{
if (j)
packet->append(',');
@@ -5297,7 +5297,7 @@ static int get_schema_stat_record(THD *t
{
KEY_PART_INFO *key_part= key_info->key_part;
const char *str;
- for (uint j=0 ; j < key_info->key_parts ; j++,key_part++)
+ for (uint j=0 ; j < key_info->user_defined_key_parts ; j++,key_part++)
{
restore_record(table, s->default_values);
table->field[0]->store(STRING_WITH_LEN("def"), cs);
@@ -5732,7 +5732,7 @@ static int get_schema_key_column_usage_r
continue;
uint f_idx= 0;
KEY_PART_INFO *key_part= key_info->key_part;
- for (uint j=0 ; j < key_info->key_parts ; j++,key_part++)
+ for (uint j=0 ; j < key_info->user_defined_key_parts ; j++,key_part++)
{
if (key_part->field)
{
=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc 2012-10-24 07:50:30 +0000
+++ b/sql/sql_table.cc 2012-10-30 07:59:01 +0000
@@ -3681,7 +3681,8 @@ mysql_prepare_create_table(THD *thd, HA_
if (key->generated)
key_info->flags|= HA_GENERATED_KEY;
- key_info->key_parts=(uint8) key->columns.elements;
+ key_info->user_defined_key_parts=(uint8) key->columns.elements;
+ key_info->actual_key_parts= key_info->user_defined_key_parts;
key_info->key_part=key_part_info;
key_info->usable_key_parts= key_number;
key_info->algorithm= key->key_create_info.algorithm;
@@ -3721,7 +3722,7 @@ mysql_prepare_create_table(THD *thd, HA_
MYF(0));
DBUG_RETURN(TRUE);
}
- if (key_info->key_parts != 1)
+ if (key_info->user_defined_key_parts != 1)
{
my_error(ER_WRONG_ARGUMENTS, MYF(0), "SPATIAL INDEX");
DBUG_RETURN(TRUE);
@@ -3730,7 +3731,7 @@ mysql_prepare_create_table(THD *thd, HA_
else if (key_info->algorithm == HA_KEY_ALG_RTREE)
{
#ifdef HAVE_RTREE_KEYS
- if ((key_info->key_parts & 1) == 1)
+ if ((key_info->user_defined_key_parts & 1) == 1)
{
my_error(ER_WRONG_ARGUMENTS, MYF(0), "RTREE INDEX");
DBUG_RETURN(TRUE);
@@ -4014,6 +4015,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);
@@ -5334,7 +5336,7 @@ err:
static bool is_candidate_key(KEY *key)
{
KEY_PART_INFO *key_part;
- KEY_PART_INFO *key_part_end= key->key_part + key->key_parts;
+ KEY_PART_INFO *key_part_end= key->key_part + key->user_defined_key_parts;
if (!(key->flags & HA_NOSAME) || (key->flags & HA_NULL_PART_KEY))
return false;
@@ -5661,14 +5663,14 @@ static bool fill_alter_inplace_info(THD
if ((table_key->algorithm != new_key->algorithm) ||
((table_key->flags & HA_KEYFLAG_MASK) !=
(new_key->flags & HA_KEYFLAG_MASK)) ||
- (table_key->key_parts != new_key->key_parts))
+ (table_key->user_defined_key_parts != new_key->user_defined_key_parts))
goto index_changed;
/*
Check that the key parts remain compatible between the old and
new tables.
*/
- end= table_key->key_part + table_key->key_parts;
+ end= table_key->key_part + table_key->user_defined_key_parts;
for (key_part= table_key->key_part, new_part= new_key->key_part;
key_part < end;
key_part++, new_part++)
@@ -5856,7 +5858,7 @@ static void update_altered_table(const A
key= ha_alter_info.key_info_buffer +
ha_alter_info.index_add_buffer[add_key_idx];
- end= key->key_part + key->key_parts;
+ end= key->key_part + key->user_defined_key_parts;
for (key_part= key->key_part; key_part < end; key_part++)
altered_table->field[key_part->fieldnr]->flags|= FIELD_IN_ADD_INDEX;
}
@@ -5988,12 +5990,13 @@ bool mysql_compare_tables(TABLE *table,
if ((table_key->algorithm != new_key->algorithm) ||
((table_key->flags & HA_KEYFLAG_MASK) !=
(new_key->flags & HA_KEYFLAG_MASK)) ||
- (table_key->key_parts != new_key->key_parts))
+ (table_key->user_defined_key_parts != new_key->user_defined_key_parts))
DBUG_RETURN(false);
/* Check that the key parts remain compatible. */
KEY_PART_INFO *table_part;
- KEY_PART_INFO *table_part_end= table_key->key_part + table_key->key_parts;
+ KEY_PART_INFO *table_part_end= table_key->key_part +
+ table_key->user_defined_key_parts;
KEY_PART_INFO *new_part;
for (table_part= table_key->key_part, new_part= new_key->key_part;
table_part < table_part_end;
@@ -6782,7 +6785,7 @@ mysql_prepare_alter_table(THD *thd, TABL
KEY_PART_INFO *key_part= key_info->key_part;
key_parts.empty();
- for (uint j=0 ; j < key_info->key_parts ; j++,key_part++)
+ for (uint j=0 ; j < key_info->user_defined_key_parts ; j++,key_part++)
{
if (!key_part->field)
continue; // Wrong field (from UNIREG)
=== modified file 'sql/sql_tmp_table.cc'
--- a/sql/sql_tmp_table.cc 2012-08-29 12:49:37 +0000
+++ b/sql/sql_tmp_table.cc 2012-10-30 07:59:01 +0000
@@ -983,6 +983,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
param->end_write_records= rows_limit;
keyinfo= param->keyinfo;
+ keyinfo->table= table;
if (group)
{
@@ -994,7 +995,9 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
table->key_info= share->key_info= keyinfo;
keyinfo->key_part= key_part_info;
keyinfo->flags=HA_NOSAME;
- keyinfo->usable_key_parts=keyinfo->key_parts= param->group_parts;
+ keyinfo->usable_key_parts=keyinfo->user_defined_key_parts=
+ param->group_parts;
+ keyinfo->actual_key_parts= keyinfo->user_defined_key_parts;
keyinfo->key_length=0;
keyinfo->rec_per_key=0;
keyinfo->algorithm= HA_KEY_ALG_UNDEF;
@@ -1033,6 +1036,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)
@@ -1055,18 +1059,21 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
share->uniques= 1;
}
null_pack_length-=hidden_null_pack_length;
- keyinfo->key_parts= ((field_count-param->hidden_field_count)+
- (share->uniques ? test(null_pack_length) : 0));
+ keyinfo->user_defined_key_parts=
+ ((field_count-param->hidden_field_count) +
+ (share->uniques ? test(null_pack_length) : 0));
+ keyinfo->actual_key_parts= keyinfo->user_defined_key_parts;
table->distinct= 1;
share->keys= 1;
if (!(key_part_info= (KEY_PART_INFO*)
alloc_root(&table->mem_root,
- keyinfo->key_parts * sizeof(KEY_PART_INFO))))
+ keyinfo->user_defined_key_parts * sizeof(KEY_PART_INFO))))
goto err;
- memset(key_part_info, 0, keyinfo->key_parts * sizeof(KEY_PART_INFO));
+ memset(key_part_info, 0, keyinfo->user_defined_key_parts *
+ 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;
@@ -1380,8 +1387,9 @@ 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->usable_key_parts= keyinfo->key_parts= 1;
+ 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;
keyinfo->rec_per_key=0;
keyinfo->algorithm= HA_KEY_ALG_UNDEF;
@@ -1635,13 +1643,14 @@ bool create_myisam_tmp_table(TABLE *tabl
share->keys= 1;
}
HA_KEYSEG *seg= (HA_KEYSEG*) alloc_root(&table->mem_root,
- sizeof(*seg) * keyinfo->key_parts);
+ sizeof(*seg) *
+ keyinfo->user_defined_key_parts);
if (!seg)
goto err;
- memset(seg, 0, sizeof(*seg) * keyinfo->key_parts);
+ memset(seg, 0, sizeof(*seg) * keyinfo->user_defined_key_parts);
if (keyinfo->key_length >= table->file->max_key_length() ||
- keyinfo->key_parts > table->file->max_key_parts() ||
+ keyinfo->user_defined_key_parts > table->file->max_key_parts() ||
share->uniques)
{
/* Can't create a key; Make a unique constraint instead of a key */
@@ -1649,7 +1658,7 @@ bool create_myisam_tmp_table(TABLE *tabl
share->uniques= 1;
using_unique_constraint=1;
memset(&uniquedef, 0, sizeof(uniquedef));
- uniquedef.keysegs=keyinfo->key_parts;
+ uniquedef.keysegs=keyinfo->user_defined_key_parts;
uniquedef.seg=seg;
uniquedef.null_are_equal=1;
@@ -1665,10 +1674,10 @@ bool create_myisam_tmp_table(TABLE *tabl
/* Create an unique key */
memset(&keydef, 0, sizeof(keydef));
keydef.flag= keyinfo->flags;
- keydef.keysegs= keyinfo->key_parts;
+ keydef.keysegs= keyinfo->user_defined_key_parts;
keydef.seg= seg;
}
- for (uint i=0; i < keyinfo->key_parts ; i++,seg++)
+ for (uint i=0; i < keyinfo->user_defined_key_parts ; i++,seg++)
{
Field *field=keyinfo->key_part[i].field;
seg->flag= 0;
=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc 2012-10-09 06:56:49 +0000
+++ b/sql/sql_update.cc 2012-10-30 07:59:01 +0000
@@ -1172,7 +1172,8 @@ bool unsafe_key_update(TABLE_LIST *leave
// The primary key can cover multiple columns
KEY key_info= table1->key_info[table1->s->primary_key];
KEY_PART_INFO *key_part= key_info.key_part;
- KEY_PART_INFO *key_part_end= key_part + key_info.key_parts;
+ KEY_PART_INFO *key_part_end= key_part +
+ key_info.user_defined_key_parts;
for (;key_part != key_part_end; ++key_part)
{
=== modified file 'sql/sql_view.cc'
--- a/sql/sql_view.cc 2012-07-24 07:02:51 +0000
+++ b/sql/sql_view.cc 2012-10-30 07:59:01 +0000
@@ -1920,7 +1920,7 @@ bool check_key_in_view(THD *thd, TABLE_L
if ((key_info->flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME)
{
KEY_PART_INFO *key_part= key_info->key_part;
- KEY_PART_INFO *key_part_end= key_part + key_info->key_parts;
+ KEY_PART_INFO *key_part_end= key_part + key_info->user_defined_key_parts;
/* check that all key parts are used */
for (;;)
=== modified file 'sql/structs.h'
--- a/sql/structs.h 2012-03-22 18:28:07 +0000
+++ b/sql/structs.h 2012-10-30 07:59:01 +0000
@@ -86,24 +86,37 @@ public:
typedef struct st_key {
- uint key_length; /* Tot length of key */
- ulong flags; /* dupp key and pack flags */
- uint key_parts; /* How many key_parts */
- 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.
*/
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 */
- /*
+ /** 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.
=== modified file 'sql/sys_vars.cc'
--- a/sql/sys_vars.cc 2012-10-24 08:43:19 +0000
+++ b/sql/sys_vars.cc 2012-10-30 07:59:01 +0000
@@ -2023,7 +2023,7 @@ static const char *optimizer_switch_name
"materialization", "semijoin", "loosescan", "firstmatch",
"subquery_materialization_cost_based",
#endif
- "default", NullS
+ "use_index_extensions", "default", NullS
};
/** propagates changes to @@engine_condition_pushdown */
static bool fix_optimizer_switch(sys_var *self, THD *thd,
@@ -2045,7 +2045,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, use_index_extensions"
"} 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-16 12:40:33 +0000
+++ b/sql/table.cc 2012-10-30 07:59:01 +0000
@@ -834,6 +834,68 @@ void KEY_PART_INFO::init_from_field(Fiel
0 : FIELDFLAG_BINARY;
}
+
+/**
+ 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
+ @param[in,out] key_part Pointer to the current KEY_PART_INFO*
+ allocated after all the user defined key parts.
+ @param[in,out] rec_per_key Pointer to the current rec_per_key
+ allocated after all the user defined key parts.
+*/
+
+static void add_pk_parts_to_sk(KEY *sk, KEY *pk,
+ KEY_PART_INFO **key_part,
+ ulong **rec_per_key)
+{
+ uint max_key_length= sk->key_length;
+ bool is_unique_key= false;
+ for (uint pk_part= 0; pk_part < pk->user_defined_key_parts; pk_part++)
+ {
+ 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)
+ return;
+
+ for (uint j= 0; j < sk->user_defined_key_parts; j++)
+ {
+ if (sk->key_part[j].fieldnr == pk_key_part->fieldnr &&
+ sk->key_part[j].length >= pk_key_part->length)
+ 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;
+
+ /*
+ Secondary key will be unique if the key does not exceed
+ key length limitation and key parts limitation.
+ */
+ is_unique_key= true;
+ **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;
+ }
+ }
+ }
+ if (is_unique_key)
+ sk->actual_flags|= HA_NOSAME;
+ return;
+}
+
+
/*
Read data from a binary .frm file from MySQL 3.23 - 5.0 into TABLE_SHARE
@@ -852,6 +914,7 @@ static int open_binary_frm(THD *thd, TAB
uint key_info_length, com_length, null_bit_pos;
uint extra_rec_buf_length;
uint i,j;
+ bool use_extended_sk; // Supported extending of secondary keys with PK parts
bool use_hash;
char *keynames, *names, *comment_pos;
uchar forminfo[288];
@@ -963,17 +1026,30 @@ static int open_binary_frm(THD *thd, TAB
share->keys_for_keyread.init(0);
share->keys_in_use.init(keys);
- n_length=keys*sizeof(KEY)+key_parts*sizeof(KEY_PART_INFO);
+ strpos=disk_buff+6;
+
+ use_extended_sk= (legacy_db_type == DB_TYPE_INNODB);
+
+ uint total_key_parts;
+ if (use_extended_sk)
+ {
+ uint primary_key_parts= keys ?
+ (new_frm_ver >= 3) ? (uint) strpos[4] : (uint) strpos[3] : 0;
+ total_key_parts= key_parts + primary_key_parts * (keys - 1);
+ }
+ else
+ total_key_parts= key_parts;
+ n_length= keys * sizeof(KEY) + total_key_parts * sizeof(KEY_PART_INFO);
+
if (!(keyinfo = (KEY*) alloc_root(&share->mem_root,
n_length + uint2korr(disk_buff+4))))
goto err; /* purecov: inspected */
memset(keyinfo, 0, n_length);
share->key_info= keyinfo;
key_part= reinterpret_cast<KEY_PART_INFO*>(keyinfo+keys);
- strpos=disk_buff+6;
if (!(rec_per_key= (ulong*) alloc_root(&share->mem_root,
- sizeof(ulong)*key_parts)))
+ sizeof(ulong) * total_key_parts)))
goto err;
for (i=0 ; i < keys ; i++, keyinfo++)
@@ -983,7 +1059,7 @@ static int open_binary_frm(THD *thd, TAB
{
keyinfo->flags= (uint) uint2korr(strpos) ^ HA_NOSAME;
keyinfo->key_length= (uint) uint2korr(strpos+2);
- keyinfo->key_parts= (uint) strpos[4];
+ keyinfo->user_defined_key_parts= (uint) strpos[4];
keyinfo->algorithm= (enum ha_key_alg) strpos[5];
keyinfo->block_size= uint2korr(strpos+6);
strpos+=8;
@@ -992,14 +1068,14 @@ static int open_binary_frm(THD *thd, TAB
{
keyinfo->flags= ((uint) strpos[0]) ^ HA_NOSAME;
keyinfo->key_length= (uint) uint2korr(strpos+1);
- keyinfo->key_parts= (uint) strpos[3];
+ keyinfo->user_defined_key_parts= (uint) strpos[3];
keyinfo->algorithm= HA_KEY_ALG_UNDEF;
strpos+=4;
}
keyinfo->key_part= key_part;
keyinfo->rec_per_key= rec_per_key;
- for (j=keyinfo->key_parts ; j-- ; key_part++)
+ for (j=keyinfo->user_defined_key_parts ; j-- ; key_part++)
{
*rec_per_key++=0;
key_part->fieldnr= (uint16) (uint2korr(strpos) & FIELD_NR_MASK);
@@ -1025,6 +1101,22 @@ static int open_binary_frm(THD *thd, TAB
}
key_part->store_length=key_part->length;
}
+ /*
+ Add PK parts if engine supports PK extension for secondary keys.
+ Atm it works for Innodb only. Here we add unique first key parts
+ to the end of secondary key parts array and increase actual number
+ of key parts. Note that primary key is always first if exists.
+ Later if there is no PK in the table then number of actual keys parts
+ is set to user defined key parts.
+ */
+ keyinfo->actual_key_parts= keyinfo->user_defined_key_parts;
+ keyinfo->actual_flags= keyinfo->flags;
+ if (use_extended_sk && i && !(keyinfo->flags & HA_NOSAME))
+ {
+ add_pk_parts_to_sk(keyinfo, share->key_info,
+ &key_part, &rec_per_key);
+ }
+ share->key_parts+= keyinfo->hidden_key_parts;
}
keynames=(char*) key_part;
strpos+= (strmov(keynames, (char *) strpos) - keynames)+1;
@@ -1639,7 +1731,7 @@ static int open_binary_frm(THD *thd, TAB
declare this as a primary key.
*/
primary_key=key;
- for (i=0 ; i < keyinfo->key_parts ;i++)
+ for (i=0 ; i < keyinfo->user_defined_key_parts ;i++)
{
uint fieldnr= key_part[i].fieldnr;
if (!fieldnr ||
@@ -1653,7 +1745,18 @@ static int open_binary_frm(THD *thd, TAB
}
}
- for (i=0 ; i < keyinfo->key_parts ; key_part++,i++)
+ if (primary_key >= MAX_KEY && key)
+ {
+ /*
+ If there is no PK then PK parts added earlier are
+ excluded from processing and reset original flag
+ value.
+ */
+ keyinfo->actual_key_parts= keyinfo->user_defined_key_parts;
+ keyinfo->actual_flags= keyinfo->flags;
+ }
+
+ for (i=0 ; i < keyinfo->actual_key_parts ; key_part++,i++)
{
Field *field;
if (new_field_pack_flag <= 1)
@@ -1681,12 +1784,13 @@ static int open_binary_frm(THD *thd, TAB
field->type() == MYSQL_TYPE_GEOMETRY)
{
key_part->store_length+=HA_KEY_BLOB_LENGTH;
- keyinfo->key_length+= HA_KEY_BLOB_LENGTH;
+ if (i + 1 <= keyinfo->user_defined_key_parts)
+ keyinfo->key_length+= HA_KEY_BLOB_LENGTH;
}
key_part->init_flags();
if (i == 0 && key != primary_key)
field->flags |= (((keyinfo->flags & HA_NOSAME) &&
- (keyinfo->key_parts == 1)) ?
+ (keyinfo->user_defined_key_parts == 1)) ?
UNIQUE_KEY_FLAG : MULTIPLE_KEY_FLAG);
if (i == 0)
field->key_start.set_bit(key);
@@ -1756,10 +1860,15 @@ static int open_binary_frm(THD *thd, TAB
key_part->key_part_flag|= HA_PART_KEY_SEG;
}
}
+
+ /* Skip unused key parts if they exist */
+ if (primary_key >= MAX_KEY)
+ key_part+= keyinfo->hidden_key_parts;
+
keyinfo->usable_key_parts= usable_parts; // Filesort
set_if_bigger(share->max_key_length,keyinfo->key_length+
- keyinfo->key_parts);
+ keyinfo->user_defined_key_parts);
share->total_key_length+= keyinfo->key_length;
/*
MERGE tables do not have unique indexes. But every key could be
@@ -1777,7 +1886,7 @@ static int open_binary_frm(THD *thd, TAB
If we are using an integer as the primary key then allow the user to
refer to it as '_rowid'
*/
- if (share->key_info[primary_key].key_parts == 1)
+ if (share->key_info[primary_key].user_defined_key_parts == 1)
{
Field *field= share->key_info[primary_key].key_part[0].field;
if (field && field->result_type() == INT_RESULT)
@@ -2024,7 +2133,9 @@ int open_table_from_share(THD *thd, TABL
KEY *key_info, *key_info_end;
KEY_PART_INFO *key_part;
uint n_length;
- n_length= share->keys*sizeof(KEY) + share->key_parts*sizeof(KEY_PART_INFO);
+ n_length= share->keys * sizeof(KEY) +
+ share->key_parts * sizeof(KEY_PART_INFO);
+
if (!(key_info= (KEY*) alloc_root(&outparam->mem_root, n_length)))
goto err;
outparam->key_info= key_info;
@@ -2043,7 +2154,7 @@ int open_table_from_share(THD *thd, TABL
key_info->table= outparam;
key_info->key_part= key_part;
- for (key_part_end= key_part+ key_info->key_parts ;
+ for (key_part_end= key_part + key_info->actual_key_parts ;
key_part < key_part_end ;
key_part++)
{
@@ -2061,6 +2172,9 @@ int open_table_from_share(THD *thd, TABL
field->field_length= key_part->length;
}
}
+ /* Skip unused key parts if they exist */
+ if (share->primary_key >= MAX_KEY)
+ key_part+= key_info->hidden_key_parts;
}
}
@@ -2940,9 +3054,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->key_parts;
+ KEY_PART_INFO *end_key_part= key_part + actual_key_parts(key_info);
uint length= 0;
while (key_part < end_key_part && keypart_map)
@@ -4993,7 +5107,7 @@ void TABLE::mark_columns_used_by_index_n
{
KEY_PART_INFO *key_part= key_info[index].key_part;
KEY_PART_INFO *key_part_end= (key_part +
- key_info[index].key_parts);
+ key_info[index].user_defined_key_parts);
for (;key_part != key_part_end; key_part++)
bitmap_set_bit(bitmap, key_part->fieldnr-1);
}
@@ -5340,13 +5454,15 @@ bool TABLE::add_tmp_key(Field_map *key_p
return TRUE;
memset(key_buf, 0, key_buf_size);
cur_key->key_part= key_part_info= (KEY_PART_INFO*) key_buf;
- cur_key->usable_key_parts= cur_key->key_parts= key_part_count;
+ cur_key->usable_key_parts= cur_key->user_defined_key_parts= key_part_count;
+ cur_key->actual_key_parts= cur_key->user_defined_key_parts;
s->key_parts+= key_part_count;
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);
@@ -5397,7 +5513,7 @@ void TABLE::use_index(int key_to_save)
uint j;
KEY_PART_INFO *kp;
for (kp= key_info[i].key_part, j= 0;
- j < key_info[i].key_parts;
+ j < key_info[i].user_defined_key_parts;
j++, kp++)
{
if (i == key_to_save)
@@ -5432,7 +5548,7 @@ void TABLE::use_index(int key_to_save)
if (key_to_save > 0)
key_info[0]= key_info[key_to_save];
s->keys= 1;
- s->key_parts= key_info[0].key_parts;
+ s->key_parts= key_info[0].user_defined_key_parts;
if (covering_keys.is_set(key_to_save))
covering_keys.set_prefix(1);
else
@@ -6107,7 +6223,7 @@ bool TABLE::update_const_key_parts(Item
for (uint index= 0; index < s->keys; index++)
{
KEY_PART_INFO *keyinfo= key_info[index].key_part;
- KEY_PART_INFO *keyinfo_end= keyinfo + key_info[index].key_parts;
+ KEY_PART_INFO *keyinfo_end= keyinfo + key_info[index].user_defined_key_parts;
for (key_part_map part_map= (key_part_map)1;
keyinfo < keyinfo_end;
=== modified file 'sql/unireg.cc'
--- a/sql/unireg.cc 2012-06-22 10:11:31 +0000
+++ b/sql/unireg.cc 2012-10-30 07:59:01 +0000
@@ -630,15 +630,16 @@ static uint pack_keys(uchar *keybuff, ui
{
int2store(pos, (key->flags ^ HA_NOSAME));
int2store(pos+2,key->key_length);
- pos[4]= (uchar) key->key_parts;
+ pos[4]= (uchar) key->user_defined_key_parts;
pos[5]= (uchar) key->algorithm;
int2store(pos+6, key->block_size);
pos+=8;
- key_parts+=key->key_parts;
+ key_parts+=key->user_defined_key_parts;
DBUG_PRINT("loop", ("flags: %lu key_parts: %d at 0x%lx",
- key->flags, key->key_parts,
+ key->flags, key->user_defined_key_parts,
(long) key->key_part));
- for (key_part=key->key_part,key_part_end=key_part+key->key_parts ;
+ for (key_part=key->key_part,
+ key_part_end= key_part + key->user_defined_key_parts ;
key_part != key_part_end ;
key_part++)
=== modified file 'storage/archive/ha_archive.cc'
--- a/storage/archive/ha_archive.cc 2012-08-24 08:26:46 +0000
+++ b/storage/archive/ha_archive.cc 2012-10-30 07:59:01 +0000
@@ -752,7 +752,7 @@ int ha_archive::create(const char *name,
{
KEY *pos= table_arg->key_info+key;
KEY_PART_INFO *key_part= pos->key_part;
- KEY_PART_INFO *key_part_end= key_part + pos->key_parts;
+ KEY_PART_INFO *key_part_end= key_part + pos->user_defined_key_parts;
for (; key_part != key_part_end; key_part++)
{
=== modified file 'storage/federated/ha_federated.cc'
--- a/storage/federated/ha_federated.cc 2012-07-26 12:26:06 +0000
+++ b/storage/federated/ha_federated.cc 2012-10-30 07:59:01 +0000
@@ -1314,7 +1314,7 @@ bool ha_federated::create_where_from_key
}
for (key_part= key_info->key_part,
- remainder= key_info->key_parts,
+ remainder= key_info->user_defined_key_parts,
length= ranges[i]->length,
ptr= ranges[i]->key; ;
remainder--,
=== modified file 'storage/heap/ha_heap.cc'
--- a/storage/heap/ha_heap.cc 2012-06-22 10:11:31 +0000
+++ b/storage/heap/ha_heap.cc 2012-10-30 07:59:01 +0000
@@ -205,14 +205,14 @@ void ha_heap::update_key_stats()
if (key->algorithm != HA_KEY_ALG_BTREE)
{
if (key->flags & HA_NOSAME)
- key->rec_per_key[key->key_parts-1]= 1;
+ key->rec_per_key[key->user_defined_key_parts - 1]= 1;
else
{
ha_rows hash_buckets= file->s->keydef[i].hash_buckets;
uint no_records= hash_buckets ? (uint) (file->s->records/hash_buckets) : 2;
if (no_records < 2)
no_records= 2;
- key->rec_per_key[key->key_parts-1]= no_records;
+ key->rec_per_key[key->user_defined_key_parts - 1]= no_records;
}
}
}
@@ -630,7 +630,7 @@ ha_rows ha_heap::records_in_range(uint i
/* Assert that info() did run. We need current statistics here. */
DBUG_ASSERT(key_stat_version == file->s->key_stat_version);
- return key->rec_per_key[key->key_parts-1];
+ return key->rec_per_key[key->user_defined_key_parts - 1];
}
@@ -649,7 +649,7 @@ heap_prepare_hp_create_info(TABLE *table
memset(hp_create_info, 0, sizeof(*hp_create_info));
for (key= parts= 0; key < keys; key++)
- parts+= table_arg->key_info[key].key_parts;
+ parts+= table_arg->key_info[key].user_defined_key_parts;
if (!(keydef= (HP_KEYDEF*) my_malloc(keys * sizeof(HP_KEYDEF) +
parts * sizeof(HA_KEYSEG),
@@ -660,9 +660,9 @@ heap_prepare_hp_create_info(TABLE *table
{
KEY *pos= table_arg->key_info+key;
KEY_PART_INFO *key_part= pos->key_part;
- KEY_PART_INFO *key_part_end= key_part + pos->key_parts;
+ KEY_PART_INFO *key_part_end= key_part + pos->user_defined_key_parts;
- keydef[key].keysegs= (uint) pos->key_parts;
+ keydef[key].keysegs= (uint) pos->user_defined_key_parts;
keydef[key].flag= (pos->flags & (HA_NOSAME | HA_NULL_ARE_EQUAL));
keydef[key].seg= seg;
=== modified file 'storage/innobase/handler/ha_innodb.cc'
--- a/storage/innobase/handler/ha_innodb.cc 2012-10-17 11:15:30 +0000
+++ b/storage/innobase/handler/ha_innodb.cc 2012-10-30 07:59:01 +0000
@@ -4320,12 +4320,13 @@ innobase_match_index_columns(
DBUG_ENTER("innobase_match_index_columns");
/* Check whether user defined index column count matches */
- if (key_info->key_parts != index_info->n_user_defined_cols) {
+ if (key_info->user_defined_key_parts !=
+ index_info->n_user_defined_cols) {
DBUG_RETURN(FALSE);
}
key_part = key_info->key_part;
- key_end = key_part + key_info->key_parts;
+ key_end = key_part + key_info->user_defined_key_parts;
innodb_idx_fld = index_info->fields;
innodb_idx_fld_end = index_info->fields + index_info->n_fields;
@@ -5621,7 +5622,8 @@ ha_innobase::store_key_val_for_row(
{
KEY* key_info = table->key_info + keynr;
KEY_PART_INFO* key_part = key_info->key_part;
- KEY_PART_INFO* end = key_part + key_info->key_parts;
+ KEY_PART_INFO* end =
+ key_part + key_info->user_defined_key_parts;
char* buff_start = buff;
enum_field_types mysql_type;
Field* field;
@@ -8611,9 +8613,10 @@ create_index(
if (key->flags & HA_FULLTEXT) {
index = dict_mem_index_create(table_name, key->name, 0,
- DICT_FTS, key->key_parts);
+ DICT_FTS,
+ key->user_defined_key_parts);
- for (ulint i = 0; i < key->key_parts; i++) {
+ for (ulint i = 0; i < key->user_defined_key_parts; i++) {
KEY_PART_INFO* key_part = key->key_part + i;
dict_mem_index_add_field(
index, key_part->field->field_name, 0);
@@ -8637,15 +8640,16 @@ create_index(
}
field_lengths = (ulint*) my_malloc(
- key->key_parts * sizeof *field_lengths, MYF(MY_FAE));
+ key->user_defined_key_parts * sizeof *
+ field_lengths, MYF(MY_FAE));
/* We pass 0 as the space id, and determine at a lower level the space
id where to store the table */
index = dict_mem_index_create(table_name, key->name, 0,
- ind_type, key->key_parts);
+ ind_type, key->user_defined_key_parts);
- for (ulint i = 0; i < key->key_parts; i++) {
+ for (ulint i = 0; i < key->user_defined_key_parts; i++) {
KEY_PART_INFO* key_part = key->key_part + i;
ulint prefix_len;
ulint col_type;
@@ -10277,14 +10281,14 @@ ha_innobase::records_in_range(
goto func_exit;
}
- heap = mem_heap_create(2 * (key->key_parts * sizeof(dfield_t)
+ heap = mem_heap_create(2 * (key->actual_key_parts * sizeof(dfield_t)
+ sizeof(dtuple_t)));
- range_start = dtuple_create(heap, key->key_parts);
- dict_index_copy_types(range_start, index, key->key_parts);
+ range_start = dtuple_create(heap, key->actual_key_parts);
+ dict_index_copy_types(range_start, index, key->actual_key_parts);
- range_end = dtuple_create(heap, key->key_parts);
- dict_index_copy_types(range_end, index, key->key_parts);
+ range_end = dtuple_create(heap, key->actual_key_parts);
+ dict_index_copy_types(range_end, index, key->actual_key_parts);
row_sel_convert_mysql_key_to_innobase(
range_start,
@@ -10941,7 +10945,7 @@ ha_innobase::info_low(
break;
}
- for (j = 0; j < table->key_info[i].key_parts; j++) {
+ for (j = 0; j < table->key_info[i].actual_key_parts; j++) {
if (table->key_info[i].flags & HA_FULLTEXT) {
/* The whole concept has no validity
@@ -13193,7 +13197,7 @@ ha_innobase::cmp_ref(
key_part = table->key_info[table->s->primary_key].key_part;
key_part_end = key_part
- + table->key_info[table->s->primary_key].key_parts;
+ + table->key_info[table->s->primary_key].user_defined_key_parts;
for (; key_part != key_part_end; ++key_part) {
field = key_part->field;
=== modified file 'storage/innobase/handler/handler0alter.cc'
--- a/storage/innobase/handler/handler0alter.cc 2012-10-29 03:08:00 +0000
+++ b/storage/innobase/handler/handler0alter.cc 2012-10-30 07:59:01 +0000
@@ -344,7 +344,7 @@ ha_innobase::check_if_supported_inplace_
+ ha_alter_info->key_count;
new_key++) {
for (KEY_PART_INFO* key_part = new_key->key_part;
- key_part < new_key->key_part + new_key->key_parts;
+ key_part < new_key->key_part + new_key->user_defined_key_parts;
key_part++) {
const Create_field* new_field;
@@ -683,7 +683,7 @@ innobase_find_equiv_index(
for (uint i = 0; i < n_add; i++) {
const KEY* key = &keys[add[i]];
- if (key->key_parts < n_cols) {
+ if (key->user_defined_key_parts < n_cols) {
no_match:
continue;
}
@@ -1312,7 +1312,7 @@ innobase_check_index_keys(
}
name_ok:
- for (ulint i = 0; i < key.key_parts; i++) {
+ for (ulint i = 0; i < key.user_defined_key_parts; i++) {
const KEY_PART_INFO& key_part1
= key.key_part[i];
const Field* field
@@ -1440,7 +1440,7 @@ innobase_create_index_def(
const KEY* key = &keys[key_number];
ulint i;
ulint len;
- ulint n_fields = key->key_parts;
+ ulint n_fields = key->user_defined_key_parts;
char* index_name;
DBUG_ENTER("innobase_create_index_def");
@@ -1598,7 +1598,7 @@ innobase_fts_check_doc_id_index(
}
if ((key.flags & HA_NOSAME)
- && key.key_parts == 1
+ && key.user_defined_key_parts == 1
&& !strcmp(key.name, FTS_DOC_ID_INDEX_NAME)
&& !strcmp(key.key_part[0].field->field_name,
FTS_DOC_ID_COL_NAME)) {
@@ -1677,7 +1677,7 @@ innobase_fts_check_doc_id_index_in_def(
/* Do a check on FTS DOC ID_INDEX, it must be unique,
named as "FTS_DOC_ID_INDEX" and on column "FTS_DOC_ID" */
if (!(key->flags & HA_NOSAME)
- || key->key_parts != 1
+ || key->user_defined_key_parts != 1
|| strcmp(key->name, FTS_DOC_ID_INDEX_NAME)
|| strcmp(key->key_part[0].field->field_name,
FTS_DOC_ID_COL_NAME)) {
@@ -1758,7 +1758,7 @@ innobase_create_key_defs(
if (n_add > 0 && !new_primary && got_default_clust
&& (key_info[*add].flags & HA_NOSAME)
&& !(key_info[*add].flags & HA_KEY_HAS_PART_KEY_SEG)) {
- uint key_part = key_info[*add].key_parts;
+ uint key_part = key_info[*add].user_defined_key_parts;
new_primary = true;
@@ -1930,7 +1930,7 @@ innobase_check_column_length(
ulint max_col_len, /*!< in: maximum column length */
const KEY* key_info) /*!< in: Indexes to be created */
{
- for (ulint key_part = 0; key_part < key_info->key_parts; key_part++) {
+ for (ulint key_part = 0; key_part < key_info->user_defined_key_parts; key_part++) {
if (key_info->key_part[key_part].length > max_col_len) {
return(true);
}
=== modified file 'storage/myisam/ha_myisam.cc'
--- a/storage/myisam/ha_myisam.cc 2012-08-30 08:05:31 +0000
+++ b/storage/myisam/ha_myisam.cc 2012-10-30 07:59:01 +0000
@@ -249,8 +249,8 @@ int table2myisam(TABLE *table_arg, MI_KE
pos->algorithm;
keydef[i].block_length= pos->block_size;
keydef[i].seg= keyseg;
- keydef[i].keysegs= pos->key_parts;
- for (j= 0; j < pos->key_parts; j++)
+ keydef[i].keysegs= pos->user_defined_key_parts;
+ for (j= 0; j < pos->user_defined_key_parts; j++)
{
Field *field= pos->key_part[j].field;
type= field->key_type();
@@ -311,7 +311,7 @@ int table2myisam(TABLE *table_arg, MI_KE
(uchar*) table_arg->record[0]);
}
}
- keyseg+= pos->key_parts;
+ keyseg+= pos->user_defined_key_parts;
}
if (table_arg->found_next_number_field)
keydef[share->next_number_index].flag|= HA_AUTO_KEY;
@@ -2239,7 +2239,7 @@ Item *ha_myisam::idx_cond_push(uint keyn
*/
const KEY *key= &table_share->key_info[keyno_arg];
- for (uint k= 0; k < key->key_parts; ++k)
+ for (uint k= 0; k < key->user_defined_key_parts; ++k)
{
const KEY_PART_INFO *key_part= &key->key_part[k];
if (key_part->key_part_flag & HA_BLOB_PART)
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-5.6 branch (sergey.glukhov:4503 to 4504) WL#6266 | Sergey Glukhov | 16 Nov |
