MDEV-6929: Port Facebook Prefix Index Queries Optimization

Merge Facebook commit 154c579b828a60722a7d9477fc61868c07453d08
and e8f0052f9b112dc786bf9b957ed5b16a5749f7fd authored
by Steaphan Greene from https://github.com/facebook/mysql-5.6

Optimize prefix index queries to skip cluster index lookup when possible.

Currently InnoDB will always fetch the clustered index (primary key
index) for all prefix columns in an index, even when the value of a
particular record is smaller than the prefix length. This change
optimizes that case to use the record from the secondary index and avoid
the extra lookup.

Also adds two status vars that track how effective this is:

innodb_secondary_index_triggered_cluster_reads:
Times secondary index lookup triggered cluster lookup.

innodb_secondary_index_triggered_cluster_reads_avoided:
Times prefix optimization avoided triggering cluster lookup.

37 files changed, 871 insertions, 52 deletions

diff --git a/mysql-test/r/fast_prefix_index_fetch_innodb.result b/mysql-test/r/fast_prefix_index_fetch_innodb.result
new file mode 100644
index 00000000000..92af85f7fdb
--- /dev/null
+++ b/mysql-test/r/fast_prefix_index_fetch_innodb.result
@@ -0,0 +1,102 @@
+drop table if exists prefixinno;
+set global innodb_prefix_index_cluster_optimization = ON;
+show variables like 'innodb_prefix_index_cluster_optimization';
+Variable_name	Value
+innodb_prefix_index_cluster_optimization	ON
+# Create a table with a large varchar field that we index the prefix
+# of and ensure we only trigger cluster lookups when we expect it.
+create table prefixinno (
+id int not null,
+fake_id int not null,
+bigfield varchar(4096),
+primary key(id),
+index bigfield_idx (bigfield(32)),
+index fake_id_bigfield_prefix (fake_id, bigfield(32))
+) engine=innodb;
+insert into prefixinno values (1, 1001, repeat('a', 1)),
+(8, 1008, repeat('b', 8)),
+(24, 1024, repeat('c', 24)),
+(31, 1031, repeat('d', 31)),
+(32, 1032, repeat('x', 32)),
+(33, 1033, repeat('y', 33)),
+(128, 1128, repeat('z', 128));
+select * from prefixinno;
+id	fake_id	bigfield
+1	1001	a
+8	1008	bbbbbbbb
+24	1024	cccccccccccccccccccccccc
+31	1031	ddddddddddddddddddddddddddddddd
+32	1032	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+33	1033	yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
+128	1128	zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
+# Baseline sanity check: 0, 0.
+no-op query
+no-op query
+cluster_lookups_matched
+1
+cluster_lookups_avoided_matched
+1
+# Eligible for optimization.
+id	bigfield
+31	ddddddddddddddddddddddddddddddd
+cluster_lookups_matched
+1
+cluster_lookups_avoided_matched
+1
+# Eligible for optimization, access via fake_id only.
+id	bigfield
+31	ddddddddddddddddddddddddddddddd
+cluster_lookups_matched
+1
+cluster_lookups_avoided_matched
+1
+# Not eligible for optimization, access via fake_id of big row.
+id	bigfield
+33	yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
+cluster_lookups_matched
+1
+cluster_lookups_avoided_matched
+1
+# Not eligible for optimization.
+id	bigfield
+32	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+cluster_lookups_matched
+1
+cluster_lookups_avoided_matched
+1
+# Not eligible for optimization.
+id	bigfield
+33	yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
+cluster_lookups_matched
+1
+cluster_lookups_avoided_matched
+1
+# Eligible, should not increment lookup counter.
+id	bigfield
+8	bbbbbbbb
+cluster_lookups_matched
+1
+cluster_lookups_avoided_matched
+1
+# Eligible, should not increment lookup counter.
+id	bigfield
+24	cccccccccccccccccccccccc
+cluster_lookups_matched
+1
+cluster_lookups_avoided_matched
+1
+# Should increment lookup counter.
+id	bigfield
+128	zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
+cluster_lookups_matched
+1
+cluster_lookups_avoided_matched
+1
+# Disable optimization, confirm we still increment counter.
+id	bigfield
+33	yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
+cluster_lookups_matched
+1
+cluster_lookups_avoided_matched
+1
+# make test suite happy by cleaning up our mess
diff --git a/mysql-test/suite/sys_vars/r/innodb_monitor_disable_basic.result b/mysql-test/suite/sys_vars/r/innodb_monitor_disable_basic.result
index a20647487f5..e4b5bb4d21c 100644
--- a/mysql-test/suite/sys_vars/r/innodb_monitor_disable_basic.result
+++ b/mysql-test/suite/sys_vars/r/innodb_monitor_disable_basic.result
@@ -40,6 +40,8 @@ buffer_pages_written	disabled
 buffer_index_pages_written	disabled
 buffer_non_index_pages_written	disabled
 buffer_pages_read	disabled
+buffer_index_sec_rec_cluster_reads	disabled
+buffer_index_sec_rec_cluster_reads_avoided	disabled
 buffer_data_reads	disabled
 buffer_data_written	disabled
 buffer_flush_batch_scanned	disabled
diff --git a/mysql-test/suite/sys_vars/r/innodb_monitor_enable_basic.result b/mysql-test/suite/sys_vars/r/innodb_monitor_enable_basic.result
index a20647487f5..e4b5bb4d21c 100644
--- a/mysql-test/suite/sys_vars/r/innodb_monitor_enable_basic.result
+++ b/mysql-test/suite/sys_vars/r/innodb_monitor_enable_basic.result
@@ -40,6 +40,8 @@ buffer_pages_written	disabled
 buffer_index_pages_written	disabled
 buffer_non_index_pages_written	disabled
 buffer_pages_read	disabled
+buffer_index_sec_rec_cluster_reads	disabled
+buffer_index_sec_rec_cluster_reads_avoided	disabled
 buffer_data_reads	disabled
 buffer_data_written	disabled
 buffer_flush_batch_scanned	disabled
diff --git a/mysql-test/suite/sys_vars/r/innodb_monitor_reset_all_basic.result b/mysql-test/suite/sys_vars/r/innodb_monitor_reset_all_basic.result
index a20647487f5..e4b5bb4d21c 100644
--- a/mysql-test/suite/sys_vars/r/innodb_monitor_reset_all_basic.result
+++ b/mysql-test/suite/sys_vars/r/innodb_monitor_reset_all_basic.result
@@ -40,6 +40,8 @@ buffer_pages_written	disabled
 buffer_index_pages_written	disabled
 buffer_non_index_pages_written	disabled
 buffer_pages_read	disabled
+buffer_index_sec_rec_cluster_reads	disabled
+buffer_index_sec_rec_cluster_reads_avoided	disabled
 buffer_data_reads	disabled
 buffer_data_written	disabled
 buffer_flush_batch_scanned	disabled
diff --git a/mysql-test/suite/sys_vars/r/innodb_monitor_reset_basic.result b/mysql-test/suite/sys_vars/r/innodb_monitor_reset_basic.result
index a20647487f5..e4b5bb4d21c 100644
--- a/mysql-test/suite/sys_vars/r/innodb_monitor_reset_basic.result
+++ b/mysql-test/suite/sys_vars/r/innodb_monitor_reset_basic.result
@@ -40,6 +40,8 @@ buffer_pages_written	disabled
 buffer_index_pages_written	disabled
 buffer_non_index_pages_written	disabled
 buffer_pages_read	disabled
+buffer_index_sec_rec_cluster_reads	disabled
+buffer_index_sec_rec_cluster_reads_avoided	disabled
 buffer_data_reads	disabled
 buffer_data_written	disabled
 buffer_flush_batch_scanned	disabled
diff --git a/mysql-test/suite/sys_vars/r/innodb_prefix_index_cluster_optimization_basic.result b/mysql-test/suite/sys_vars/r/innodb_prefix_index_cluster_optimization_basic.result
new file mode 100644
index 00000000000..57b653bcf5e
--- /dev/null
+++ b/mysql-test/suite/sys_vars/r/innodb_prefix_index_cluster_optimization_basic.result
@@ -0,0 +1,122 @@
+SET @start_global_value = @@global.innodb_prefix_index_cluster_optimization;
+SELECT @start_global_value;
+@start_global_value
+0
+#
+# exists as global only
+#
+Valid values are 'ON' and 'OFF'
+select @@global.innodb_prefix_index_cluster_optimization in (0, 1);
+@@global.innodb_prefix_index_cluster_optimization in (0, 1)
+1
+select @@global.innodb_prefix_index_cluster_optimization;
+@@global.innodb_prefix_index_cluster_optimization
+0
+select @@session.innodb_prefix_index_cluster_optimization;
+ERROR HY000: Variable 'innodb_prefix_index_cluster_optimization' is a GLOBAL variable
+show global variables like 'innodb_prefix_index_cluster_optimization';
+Variable_name	Value
+innodb_prefix_index_cluster_optimization	OFF
+show session variables like 'innodb_prefix_index_cluster_optimization';
+Variable_name	Value
+innodb_prefix_index_cluster_optimization	OFF
+select * from information_schema.global_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_PREFIX_INDEX_CLUSTER_OPTIMIZATION	OFF
+select * from information_schema.session_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_PREFIX_INDEX_CLUSTER_OPTIMIZATION	OFF
+#
+# show that it's writable
+#
+set global innodb_prefix_index_cluster_optimization = 'OFF';
+select @@global.innodb_prefix_index_cluster_optimization;
+@@global.innodb_prefix_index_cluster_optimization
+0
+select * from information_schema.global_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_PREFIX_INDEX_CLUSTER_OPTIMIZATION	OFF
+select * from information_schema.session_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_PREFIX_INDEX_CLUSTER_OPTIMIZATION	OFF
+set @@global.innodb_prefix_index_cluster_optimization = 'ON';
+select @@global.innodb_prefix_index_cluster_optimization;
+@@global.innodb_prefix_index_cluster_optimization
+1
+select * from information_schema.global_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_PREFIX_INDEX_CLUSTER_OPTIMIZATION	ON
+select * from information_schema.session_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_PREFIX_INDEX_CLUSTER_OPTIMIZATION	ON
+set global innodb_prefix_index_cluster_optimization = 0;
+select @@global.innodb_prefix_index_cluster_optimization;
+@@global.innodb_prefix_index_cluster_optimization
+0
+select * from information_schema.global_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_PREFIX_INDEX_CLUSTER_OPTIMIZATION	OFF
+select * from information_schema.session_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_PREFIX_INDEX_CLUSTER_OPTIMIZATION	OFF
+set @@global.innodb_prefix_index_cluster_optimization = 1;
+select @@global.innodb_prefix_index_cluster_optimization;
+@@global.innodb_prefix_index_cluster_optimization
+1
+select * from information_schema.global_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_PREFIX_INDEX_CLUSTER_OPTIMIZATION	ON
+select * from information_schema.session_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_PREFIX_INDEX_CLUSTER_OPTIMIZATION	ON
+set session innodb_prefix_index_cluster_optimization = 'OFF';
+ERROR HY000: Variable 'innodb_prefix_index_cluster_optimization' is a GLOBAL variable and should be set with SET GLOBAL
+select @@global.innodb_prefix_index_cluster_optimization;
+@@global.innodb_prefix_index_cluster_optimization
+1
+select * from information_schema.global_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_PREFIX_INDEX_CLUSTER_OPTIMIZATION	ON
+select * from information_schema.session_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_PREFIX_INDEX_CLUSTER_OPTIMIZATION	ON
+set @@session.innodb_prefix_index_cluster_optimization = 'ON';
+ERROR HY000: Variable 'innodb_prefix_index_cluster_optimization' is a GLOBAL variable and should be set with SET GLOBAL
+select @@global.innodb_prefix_index_cluster_optimization;
+@@global.innodb_prefix_index_cluster_optimization
+1
+select * from information_schema.global_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_PREFIX_INDEX_CLUSTER_OPTIMIZATION	ON
+select * from information_schema.session_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_PREFIX_INDEX_CLUSTER_OPTIMIZATION	ON
+#
+# incorrect types
+#
+set global innodb_prefix_index_cluster_optimization = 1.1;
+ERROR 42000: Incorrect argument type to variable 'innodb_prefix_index_cluster_optimization'
+set global innodb_prefix_index_cluster_optimization = 1e1;
+ERROR 42000: Incorrect argument type to variable 'innodb_prefix_index_cluster_optimization'
+set global innodb_prefix_index_cluster_optimization = 2;
+ERROR 42000: Variable 'innodb_prefix_index_cluster_optimization' can't be set to the value of '2'
+set global innodb_prefix_index_cluster_optimization = -3;
+ERROR 42000: Variable 'innodb_prefix_index_cluster_optimization' can't be set to the value of '-3'
+select @@global.innodb_prefix_index_cluster_optimization;
+@@global.innodb_prefix_index_cluster_optimization
+1
+select * from information_schema.global_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_PREFIX_INDEX_CLUSTER_OPTIMIZATION	ON
+select * from information_schema.session_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_PREFIX_INDEX_CLUSTER_OPTIMIZATION	ON
+set global innodb_prefix_index_cluster_optimization = 'AUTO';
+ERROR 42000: Variable 'innodb_prefix_index_cluster_optimization' can't be set to the value of 'AUTO'
+#
+# Cleanup
+#
+SET @@global.innodb_prefix_index_cluster_optimization = @start_global_value;
+SELECT @@global.innodb_prefix_index_cluster_optimization;
+@@global.innodb_prefix_index_cluster_optimization
+0
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
index c1823ff7739..6de55595e2d 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
@@ -1461,6 +1461,20 @@ NUMERIC_BLOCK_SIZE	0
 ENUM_VALUE_LIST	NULL
 READ_ONLY	YES
 COMMAND_LINE_ARGUMENT	OPTIONAL
+VARIABLE_NAME	INNODB_PREFIX_INDEX_CLUSTER_OPTIMIZATION
+SESSION_VALUE	NULL
+GLOBAL_VALUE	OFF
+GLOBAL_VALUE_ORIGIN	COMPILE-TIME
+DEFAULT_VALUE	OFF
+VARIABLE_SCOPE	GLOBAL
+VARIABLE_TYPE	BOOLEAN
+VARIABLE_COMMENT	Enable prefix optimization to sometimes avoid cluster index lookups.
+NUMERIC_MIN_VALUE	NULL
+NUMERIC_MAX_VALUE	NULL
+NUMERIC_BLOCK_SIZE	NULL
+ENUM_VALUE_LIST	NULL
+READ_ONLY	NO
+COMMAND_LINE_ARGUMENT	OPTIONAL
 VARIABLE_NAME	INNODB_PRINT_ALL_DEADLOCKS
 SESSION_VALUE	NULL
 GLOBAL_VALUE	OFF
diff --git a/mysql-test/suite/sys_vars/t/innodb_prefix_index_cluster_optimization_basic.test b/mysql-test/suite/sys_vars/t/innodb_prefix_index_cluster_optimization_basic.test
new file mode 100644
index 00000000000..4e272fbd9c5
--- /dev/null
+++ b/mysql-test/suite/sys_vars/t/innodb_prefix_index_cluster_optimization_basic.test
@@ -0,0 +1,76 @@
+--source include/have_innodb.inc
+
+SET @start_global_value = @@global.innodb_prefix_index_cluster_optimization;
+SELECT @start_global_value;
+
+--echo #
+--echo # exists as global only
+--echo #
+
+--echo Valid values are 'ON' and 'OFF'
+select @@global.innodb_prefix_index_cluster_optimization in (0, 1);
+select @@global.innodb_prefix_index_cluster_optimization;
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
+select @@session.innodb_prefix_index_cluster_optimization;
+show global variables like 'innodb_prefix_index_cluster_optimization';
+show session variables like 'innodb_prefix_index_cluster_optimization';
+select * from information_schema.global_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+select * from information_schema.session_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+
+--echo #
+--echo # show that it's writable
+--echo #
+
+set global innodb_prefix_index_cluster_optimization = 'OFF';
+select @@global.innodb_prefix_index_cluster_optimization;
+select * from information_schema.global_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+select * from information_schema.session_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+set @@global.innodb_prefix_index_cluster_optimization = 'ON';
+select @@global.innodb_prefix_index_cluster_optimization;
+select * from information_schema.global_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+select * from information_schema.session_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+set global innodb_prefix_index_cluster_optimization = 0;
+select @@global.innodb_prefix_index_cluster_optimization;
+select * from information_schema.global_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+select * from information_schema.session_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+set @@global.innodb_prefix_index_cluster_optimization = 1;
+select @@global.innodb_prefix_index_cluster_optimization;
+select * from information_schema.global_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+select * from information_schema.session_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+
+--error ER_GLOBAL_VARIABLE
+set session innodb_prefix_index_cluster_optimization = 'OFF';
+select @@global.innodb_prefix_index_cluster_optimization;
+select * from information_schema.global_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+select * from information_schema.session_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+
+--error ER_GLOBAL_VARIABLE
+set @@session.innodb_prefix_index_cluster_optimization = 'ON';
+select @@global.innodb_prefix_index_cluster_optimization;
+select * from information_schema.global_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+select * from information_schema.session_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+
+--echo #
+--echo # incorrect types
+--echo #
+
+--error ER_WRONG_TYPE_FOR_VAR
+set global innodb_prefix_index_cluster_optimization = 1.1;
+--error ER_WRONG_TYPE_FOR_VAR
+set global innodb_prefix_index_cluster_optimization = 1e1;
+--error ER_WRONG_VALUE_FOR_VAR
+set global innodb_prefix_index_cluster_optimization = 2;
+--error ER_WRONG_VALUE_FOR_VAR
+set global innodb_prefix_index_cluster_optimization = -3;
+select @@global.innodb_prefix_index_cluster_optimization;
+select * from information_schema.global_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+select * from information_schema.session_variables where variable_name = 'innodb_prefix_index_cluster_optimization';
+--error ER_WRONG_VALUE_FOR_VAR
+set global innodb_prefix_index_cluster_optimization = 'AUTO';
+
+--echo #
+--echo # Cleanup
+--echo #
+
+SET @@global.innodb_prefix_index_cluster_optimization = @start_global_value;
+SELECT @@global.innodb_prefix_index_cluster_optimization;
diff --git a/mysql-test/t/fast_prefix_index_fetch_innodb.test b/mysql-test/t/fast_prefix_index_fetch_innodb.test
new file mode 100644
index 00000000000..e563e65ec2a
--- /dev/null
+++ b/mysql-test/t/fast_prefix_index_fetch_innodb.test
@@ -0,0 +1,150 @@
+-- source include/have_innodb.inc
+
+--disable_warnings
+drop table if exists prefixinno;
+--enable_warnings
+
+set global innodb_prefix_index_cluster_optimization = ON;
+show variables like 'innodb_prefix_index_cluster_optimization';
+
+--echo # Create a table with a large varchar field that we index the prefix
+--echo # of and ensure we only trigger cluster lookups when we expect it.
+create table prefixinno (
+       id int not null,
+       fake_id int not null,
+       bigfield varchar(4096),
+       primary key(id),
+       index bigfield_idx (bigfield(32)),
+       index fake_id_bigfield_prefix (fake_id, bigfield(32))
+) engine=innodb;
+
+insert into prefixinno values (1, 1001, repeat('a', 1)),
+                              (8, 1008, repeat('b', 8)),
+                              (24, 1024, repeat('c', 24)),
+                              (31, 1031, repeat('d', 31)),
+                              (32, 1032, repeat('x', 32)),
+                              (33, 1033, repeat('y', 33)),
+                              (128, 1128, repeat('z', 128));
+
+select * from prefixinno;
+
+let $show_count_statement = show status like 'innodb_secondary_index_triggered_cluster_reads';
+let $show_opt_statement = show status like 'innodb_secondary_index_triggered_cluster_reads_avoided';
+
+--disable_query_log
+
+--echo # Baseline sanity check: 0, 0.
+--let $base_count = query_get_value($show_count_statement, Value, 1)
+--let $base_opt = query_get_value($show_opt_statement, Value, 1)
+select "no-op query";
+--let $count = query_get_value($show_count_statement, Value, 1)
+eval select $count - $base_count into @cluster_lookups;
+select @cluster_lookups = 0 as cluster_lookups_matched;
+--let $opt = query_get_value($show_opt_statement, Value, 1)
+eval select $opt - $base_opt into @cluster_lookups;
+select @cluster_lookups = 0 as cluster_lookups_avoided_matched;
+
+--echo # Eligible for optimization.
+--let $base_count = query_get_value($show_count_statement, Value, 1)
+--let $base_opt = query_get_value($show_opt_statement, Value, 1)
+select id, bigfield from prefixinno where bigfield = repeat('d', 31);
+--let $count = query_get_value($show_count_statement, Value, 1)
+eval select $count - $base_count into @cluster_lookups;
+select @cluster_lookups = 0 as cluster_lookups_matched;
+--let $opt = query_get_value($show_opt_statement, Value, 1)
+eval select $opt - $base_opt into @cluster_lookups;
+select @cluster_lookups = 1 as cluster_lookups_avoided_matched;
+
+--echo # Eligible for optimization, access via fake_id only.
+--let $base_count = query_get_value($show_count_statement, Value, 1)
+--let $base_opt = query_get_value($show_opt_statement, Value, 1)
+select id, bigfield from prefixinno where fake_id = 1031;
+--let $count = query_get_value($show_count_statement, Value, 1)
+eval select $count - $base_count into @cluster_lookups;
+select @cluster_lookups = 0 as cluster_lookups_matched;
+--let $opt = query_get_value($show_opt_statement, Value, 1)
+eval select $opt - $base_opt into @cluster_lookups;
+select @cluster_lookups = 1 as cluster_lookups_avoided_matched;
+
+--echo # Not eligible for optimization, access via fake_id of big row.
+--let $base_count = query_get_value($show_count_statement, Value, 1)
+--let $base_opt = query_get_value($show_opt_statement, Value, 1)
+select id, bigfield from prefixinno where fake_id = 1033;
+--let $count = query_get_value($show_count_statement, Value, 1)
+eval select $count - $base_count into @cluster_lookups;
+select @cluster_lookups = 1 as cluster_lookups_matched;
+--let $opt = query_get_value($show_opt_statement, Value, 1)
+eval select $opt - $base_opt into @cluster_lookups;
+select @cluster_lookups = 0 as cluster_lookups_avoided_matched;
+
+--echo # Not eligible for optimization.
+--let $base_count = query_get_value($show_count_statement, Value, 1)
+--let $base_opt = query_get_value($show_opt_statement, Value, 1)
+select id, bigfield from prefixinno where bigfield = repeat('x', 32);
+--let $count = query_get_value($show_count_statement, Value, 1)
+eval select $count - $base_count into @cluster_lookups;
+select @cluster_lookups = 1 as cluster_lookups_matched;
+--let $opt = query_get_value($show_opt_statement, Value, 1)
+eval select $opt - $base_opt into @cluster_lookups;
+select @cluster_lookups = 0 as cluster_lookups_avoided_matched;
+
+--echo # Not eligible for optimization.
+--let $base_count = query_get_value($show_count_statement, Value, 1)
+--let $base_opt = query_get_value($show_opt_statement, Value, 1)
+select id, bigfield from prefixinno where bigfield = repeat('y', 33);
+--let $count = query_get_value($show_count_statement, Value, 1)
+eval select $count - $base_count into @cluster_lookups;
+select @cluster_lookups = 1 as cluster_lookups_matched;
+--let $opt = query_get_value($show_opt_statement, Value, 1)
+eval select $opt - $base_opt into @cluster_lookups;
+select @cluster_lookups = 0 as cluster_lookups_avoided_matched;
+
+--echo # Eligible, should not increment lookup counter.
+--let $base_count = query_get_value($show_count_statement, Value, 1)
+--let $base_opt = query_get_value($show_opt_statement, Value, 1)
+select id, bigfield from prefixinno where bigfield = repeat('b', 8);
+--let $count = query_get_value($show_count_statement, Value, 1)
+eval select $count - $base_count into @cluster_lookups;
+select @cluster_lookups = 0 as cluster_lookups_matched;
+--let $opt = query_get_value($show_opt_statement, Value, 1)
+eval select $opt - $base_opt into @cluster_lookups;
+select @cluster_lookups = 1 as cluster_lookups_avoided_matched;
+
+--echo # Eligible, should not increment lookup counter.
+--let $base_count = query_get_value($show_count_statement, Value, 1)
+--let $base_opt = query_get_value($show_opt_statement, Value, 1)
+select id, bigfield from prefixinno where bigfield = repeat('c', 24);
+--let $count = query_get_value($show_count_statement, Value, 1)
+eval select $count - $base_count into @cluster_lookups;
+select @cluster_lookups = 0 as cluster_lookups_matched;
+--let $opt = query_get_value($show_opt_statement, Value, 1)
+eval select $opt - $base_opt into @cluster_lookups;
+select @cluster_lookups = 1 as cluster_lookups_avoided_matched;
+
+--echo # Should increment lookup counter.
+--let $base_count = query_get_value($show_count_statement, Value, 1)
+--let $base_opt = query_get_value($show_opt_statement, Value, 1)
+select id, bigfield from prefixinno where bigfield = repeat('z', 128);
+--let $count = query_get_value($show_count_statement, Value, 1)
+eval select $count - $base_count into @cluster_lookups;
+select @cluster_lookups = 1 as cluster_lookups_matched;
+--let $opt = query_get_value($show_opt_statement, Value, 1)
+eval select $opt - $base_opt into @cluster_lookups;
+select @cluster_lookups = 0 as cluster_lookups_avoided_matched;
+
+--echo # Disable optimization, confirm we still increment counter.
+--let $base_count = query_get_value($show_count_statement, Value, 1)
+--let $base_opt = query_get_value($show_opt_statement, Value, 1)
+set global innodb_prefix_index_cluster_optimization = OFF;
+select id, bigfield from prefixinno where fake_id = 1033;
+--let $count = query_get_value($show_count_statement, Value, 1)
+eval select $count - $base_count into @cluster_lookups;
+select @cluster_lookups = 1 as cluster_lookups_matched;
+--let $opt = query_get_value($show_opt_statement, Value, 1)
+eval select $opt - $base_opt into @cluster_lookups;
+select @cluster_lookups = 0 as cluster_lookups_avoided_matched;
+
+
+--echo # make test suite happy by cleaning up our mess
+drop table prefixinno;
+set global innodb_prefix_index_cluster_optimization = OFF;
diff --git a/storage/innobase/dict/dict0dict.cc b/storage/innobase/dict/dict0dict.cc
index 0b2073f6dc7..8fdc64a6152 100644
--- a/storage/innobase/dict/dict0dict.cc
+++ b/storage/innobase/dict/dict0dict.cc
@@ -748,17 +748,24 @@ dict_index_get_nth_col_or_prefix_pos(
 /*=================================*/
 	const dict_index_t*	index,		/*!< in: index */
 	ulint			n,		/*!< in: column number */
-	ibool			inc_prefix)	/*!< in: TRUE=consider
+	ibool			inc_prefix,	/*!< in: TRUE=consider
 						column prefixes too */
+	ulint*			prefix_col_pos)	/*!< out: col num if prefix */
 {
 	const dict_field_t*	field;
 	const dict_col_t*	col;
 	ulint			pos;
 	ulint			n_fields;
+	ulint			prefixed_pos_dummy;
 
 	ut_ad(index);
 	ut_ad(index->magic_n == DICT_INDEX_MAGIC_N);
 
+	if (!prefix_col_pos) {
+		prefix_col_pos = &prefixed_pos_dummy;
+	}
+	*prefix_col_pos = ULINT_UNDEFINED;
+
 	col = dict_table_get_nth_col(index->table, n);
 
 	if (dict_index_is_clust(index)) {
@@ -771,10 +778,11 @@ dict_index_get_nth_col_or_prefix_pos(
 	for (pos = 0; pos < n_fields; pos++) {
 		field = dict_index_get_nth_field(index, pos);
 
-		if (col == field->col
-		    && (inc_prefix || field->prefix_len == 0)) {
-
-			return(pos);
+		if (col == field->col) {
+			*prefix_col_pos = pos;
+			if (inc_prefix || field->prefix_len == 0) {
+				return(pos);
+			}
 		}
 	}
 
@@ -919,7 +927,7 @@ dict_table_get_nth_col_pos(
 	ulint			n)	/*!< in: column number */
 {
 	return(dict_index_get_nth_col_pos(dict_table_get_first_index(table),
-					  n));
+					  n, NULL));
 }
 
 /********************************************************************//**
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index ced6faba79b..8154c0fab6a 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -799,6 +799,14 @@ static SHOW_VAR innodb_status_variables[]= {
   {"onlineddl_pct_progress",
   (char*) &export_vars.innodb_onlineddl_pct_progress, SHOW_LONG},
 
+  /* Times secondary index lookup triggered cluster lookup and
+  times prefix optimization avoided triggering cluster lookup */
+  {"secondary_index_triggered_cluster_reads",
+  (char*) &export_vars.innodb_sec_rec_cluster_reads,	  SHOW_LONG},
+  {"secondary_index_triggered_cluster_reads_avoided",
+  (char*) &export_vars.innodb_sec_rec_cluster_reads_avoided, SHOW_LONG},
+
+
   {NullS, NullS, SHOW_LONG}
 };
 
@@ -7044,11 +7052,20 @@ build_template_field(
 	templ->col_no = i;
 	templ->clust_rec_field_no = dict_col_get_clust_pos(col, clust_index);
 	ut_a(templ->clust_rec_field_no != ULINT_UNDEFINED);
+	templ->rec_field_is_prefix = FALSE;
 
 	if (dict_index_is_clust(index)) {
 		templ->rec_field_no = templ->clust_rec_field_no;
+		templ->rec_prefix_field_no = ULINT_UNDEFINED;
 	} else {
-		templ->rec_field_no = dict_index_get_nth_col_pos(index, i);
+		/* If we're in a secondary index, keep track
+		* of the original index position even if this
+		* is just a prefix index; we will use this
+		* later to avoid a cluster index lookup in
+		* some cases.*/
+
+		templ->rec_field_no = dict_index_get_nth_col_pos(index, i,
+						&templ->rec_prefix_field_no);
 	}
 
 	if (field->real_maybe_null()) {
@@ -7079,6 +7096,13 @@ build_template_field(
 	if (!dict_index_is_clust(index)
 	    && templ->rec_field_no == ULINT_UNDEFINED) {
 		prebuilt->need_to_access_clustered = TRUE;
+
+		if (templ->rec_prefix_field_no != ULINT_UNDEFINED) {
+			dict_field_t* field = dict_index_get_nth_field(
+						index,
+						templ->rec_prefix_field_no);
+			templ->rec_field_is_prefix = (field->prefix_len != 0);
+		}
 	}
 
 	if (prebuilt->mysql_prefix_len < templ->mysql_col_offset
@@ -7240,7 +7264,8 @@ ha_innobase::build_template(
 				} else {
 					templ->icp_rec_field_no
 						= dict_index_get_nth_col_pos(
-							prebuilt->index, i);
+							prebuilt->index, i,
+							NULL);
 				}
 
 				if (dict_index_is_clust(prebuilt->index)) {
@@ -7270,7 +7295,7 @@ ha_innobase::build_template(
 
 				templ->icp_rec_field_no
 					= dict_index_get_nth_col_or_prefix_pos(
-						prebuilt->index, i, TRUE);
+						prebuilt->index, i, TRUE, NULL);
 				ut_ad(templ->icp_rec_field_no
 				      != ULINT_UNDEFINED);
 
@@ -18475,6 +18500,12 @@ static MYSQL_SYSVAR_ULONG(
   1000000, 0);			/* Maximum value */
 #endif /* HAVE_ATOMIC_BUILTINS */
 
+static MYSQL_SYSVAR_BOOL(prefix_index_cluster_optimization,
+  srv_prefix_index_cluster_optimization,
+  PLUGIN_VAR_OPCMDARG,
+  "Enable prefix optimization to sometimes avoid cluster index lookups.",
+  NULL, NULL, FALSE);
+
 static MYSQL_SYSVAR_ULONG(thread_sleep_delay, srv_thread_sleep_delay,
   PLUGIN_VAR_RQCMDARG,
   "Time of innodb thread sleeping before joining InnoDB queue (usec). "
@@ -18908,6 +18939,7 @@ static struct st_mysql_sys_var* innobase_system_variables[]= {
 #ifdef HAVE_ATOMIC_BUILTINS
   MYSQL_SYSVAR(adaptive_max_sleep_delay),
 #endif /* HAVE_ATOMIC_BUILTINS */
+  MYSQL_SYSVAR(prefix_index_cluster_optimization),
   MYSQL_SYSVAR(thread_sleep_delay),
   MYSQL_SYSVAR(autoinc_lock_mode),
   MYSQL_SYSVAR(version),
diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc
index 329eae81854..74eb8d4a5b6 100644
--- a/storage/innobase/handler/handler0alter.cc
+++ b/storage/innobase/handler/handler0alter.cc
@@ -1194,7 +1194,8 @@ innobase_rec_to_mysql(
 
 		field->reset();
 
-		ipos = dict_index_get_nth_col_or_prefix_pos(index, i, TRUE);
+		ipos = dict_index_get_nth_col_or_prefix_pos(index, i, TRUE,
+							    NULL);
 
 		if (ipos == ULINT_UNDEFINED
 		    || rec_offs_nth_extern(offsets, ipos)) {
@@ -1246,7 +1247,8 @@ innobase_fields_to_mysql(
 
 		field->reset();
 
-		ipos = dict_index_get_nth_col_or_prefix_pos(index, i, TRUE);
+		ipos = dict_index_get_nth_col_or_prefix_pos(index, i, TRUE,
+							    NULL);
 
 		if (ipos == ULINT_UNDEFINED
 		    || dfield_is_ext(&fields[ipos])
diff --git a/storage/innobase/include/dict0dict.h b/storage/innobase/include/dict0dict.h
index b1a82b4b60f..c7161987b78 100644
--- a/storage/innobase/include/dict0dict.h
+++ b/storage/innobase/include/dict0dict.h
@@ -1159,8 +1159,9 @@ ulint
 dict_index_get_nth_col_pos(
 /*=======================*/
 	const dict_index_t*	index,	/*!< in: index */
-	ulint			n)	/*!< in: column number */
-	__attribute__((nonnull, warn_unused_result));
+	ulint			n,	/*!< in: column number */
+	ulint*			prefix_col_pos) /*!< out: col num if prefix */
+	__attribute__((nonnull(1), warn_unused_result));
 /********************************************************************//**
 Looks for column n in an index.
 @return position in internal representation of the index;
@@ -1171,9 +1172,11 @@ dict_index_get_nth_col_or_prefix_pos(
 /*=================================*/
 	const dict_index_t*	index,		/*!< in: index */
 	ulint			n,		/*!< in: column number */
-	ibool			inc_prefix)	/*!< in: TRUE=consider
+	ibool			inc_prefix,	/*!< in: TRUE=consider
 						column prefixes too */
-	__attribute__((nonnull, warn_unused_result));
+	ulint*			prefix_col_pos)	/*!< out: col num if prefix */
+
+	__attribute__((nonnull(1), warn_unused_result));
 /********************************************************************//**
 Returns TRUE if the index contains a column or a prefix of that column.
 @return	TRUE if contains the column or its prefix */
diff --git a/storage/innobase/include/dict0dict.ic b/storage/innobase/include/dict0dict.ic
index 84d5c57f720..43bd42ae025 100644
--- a/storage/innobase/include/dict0dict.ic
+++ b/storage/innobase/include/dict0dict.ic
@@ -1221,7 +1221,8 @@ dict_index_get_sys_col_pos(
 	}
 
 	return(dict_index_get_nth_col_pos(
-		       index, dict_table_get_sys_col_no(index->table, type)));
+			index, dict_table_get_sys_col_no(index->table, type),
+			NULL));
 }
 
 /*********************************************************************//**
@@ -1273,9 +1274,11 @@ ulint
 dict_index_get_nth_col_pos(
 /*=======================*/
 	const dict_index_t*	index,	/*!< in: index */
-	ulint			n)	/*!< in: column number */
+	ulint			n,	/*!< in: column number */
+	ulint*			prefix_col_pos) /*!< out: col num if prefix */
 {
-	return(dict_index_get_nth_col_or_prefix_pos(index, n, FALSE));
+	return(dict_index_get_nth_col_or_prefix_pos(index, n, FALSE,
+						    prefix_col_pos));
 }
 
 #ifndef UNIV_HOTBACKUP
diff --git a/storage/innobase/include/row0mysql.h b/storage/innobase/include/row0mysql.h
index 1e0f3b30f8c..3cff5a41b8e 100644
--- a/storage/innobase/include/row0mysql.h
+++ b/storage/innobase/include/row0mysql.h
@@ -606,6 +606,12 @@ struct mysql_row_templ_t {
 					Innobase record in the current index;
 					not defined if template_type is
 					ROW_MYSQL_WHOLE_ROW */
+	ibool	rec_field_is_prefix;	/* is this field in a prefix index? */
+	ulint	rec_prefix_field_no;	/* record field, even if just a
+					prefix; same as rec_field_no when not a
+					prefix, otherwise rec_field_no is
+					ULINT_UNDEFINED but this is the true
+					field number*/
 	ulint	clust_rec_field_no;	/*!< field number of the column in an
 					Innobase record in the clustered index;
 					not defined if template_type is
@@ -707,7 +713,9 @@ struct row_prebuilt_t {
 					columns through a secondary index
 					and at least one column is not in
 					the secondary index, then this is
-					set to TRUE */
+					set to TRUE; note that sometimes this
+					is set but we later optimize out the
+					clustered index lookup */
 	unsigned	templ_contains_blob:1;/*!< TRUE if the template contains
 					a column with DATA_BLOB ==
 					get_innobase_type_from_mysql_type();
diff --git a/storage/innobase/include/srv0mon.h b/storage/innobase/include/srv0mon.h
index dccf2d99bb0..00ec2925441 100644
--- a/storage/innobase/include/srv0mon.h
+++ b/storage/innobase/include/srv0mon.h
@@ -167,6 +167,8 @@ enum monitor_id_t {
 	MONITOR_OVLD_INDEX_PAGES_WRITTEN,
 	MONITOR_OVLD_NON_INDEX_PAGES_WRITTEN,
 	MONITOR_OVLD_PAGES_READ,
+	MONITOR_OVLD_INDEX_SEC_REC_CLUSTER_READS,
+	MONITOR_OVLD_INDEX_SEC_REC_CLUSTER_READS_AVOIDED,
 	MONITOR_OVLD_BYTE_READ,
 	MONITOR_OVLD_BYTE_WRITTEN,
 	MONITOR_FLUSH_BATCH_SCANNED,
diff --git a/storage/innobase/include/srv0srv.h b/storage/innobase/include/srv0srv.h
index e3ac49c50ac..f628632c616 100644
--- a/storage/innobase/include/srv0srv.h
+++ b/storage/innobase/include/srv0srv.h
@@ -157,6 +157,12 @@ struct srv_stats_t {
 
 	/** Number of rows inserted */
 	ulint_ctr_64_t		n_rows_inserted;
+
+	/** Number of times secondary index lookup triggered cluster lookup */
+	ulint_ctr_64_t		n_sec_rec_cluster_reads;
+
+	/** Number of times prefix optimization avoided triggering cluster lookup */
+	ulint_ctr_64_t		n_sec_rec_cluster_reads_avoided;
 };
 
 extern const char*	srv_main_thread_op_info;
@@ -304,6 +310,10 @@ extern ulong	srv_auto_extend_increment;
 
 extern ibool	srv_created_new_raw;
 
+/* Optimize prefix index queries to skip cluster index lookup when possible */
+/* Enables or disables this prefix optimization.  Disabled by default. */
+extern my_bool	srv_prefix_index_cluster_optimization;
+
 /** Maximum number of srv_n_log_files, or innodb_log_files_in_group */
 #define SRV_N_LOG_FILES_MAX 100
 extern ulong	srv_n_log_files;
@@ -969,6 +979,9 @@ struct export_var_t{
 						compression */
 	ib_int64_t innodb_pages_page_compression_error;/*!< Number of page
 						compression errors */
+
+	ulint innodb_sec_rec_cluster_reads;	/*!< srv_sec_rec_cluster_reads */
+	ulint innodb_sec_rec_cluster_reads_avoided; /*!< srv_sec_rec_cluster_reads_avoided */
 };
 
 /** Thread slot in the thread table.  */
diff --git a/storage/innobase/pars/pars0opt.cc b/storage/innobase/pars/pars0opt.cc
index cbed2b39eeb..5a7e1861d74 100644
--- a/storage/innobase/pars/pars0opt.cc
+++ b/storage/innobase/pars/pars0opt.cc
@@ -948,12 +948,14 @@ opt_find_all_cols(
 	/* Fill in the field_no fields in sym_node */
 
 	sym_node->field_nos[SYM_CLUST_FIELD_NO] = dict_index_get_nth_col_pos(
-		dict_table_get_first_index(index->table), sym_node->col_no);
+		dict_table_get_first_index(index->table), sym_node->col_no,
+		NULL);
 	if (!dict_index_is_clust(index)) {
 
 		ut_a(plan);
 
-		col_pos = dict_index_get_nth_col_pos(index, sym_node->col_no);
+		col_pos = dict_index_get_nth_col_pos(index, sym_node->col_no,
+						     NULL);
 
 		if (col_pos == ULINT_UNDEFINED) {
 
diff --git a/storage/innobase/pars/pars0pars.cc b/storage/innobase/pars/pars0pars.cc
index 655e5ba1324..c87e1f8e247 100644
--- a/storage/innobase/pars/pars0pars.cc
+++ b/storage/innobase/pars/pars0pars.cc
@@ -1232,7 +1232,8 @@ pars_process_assign_list(
 		col_sym = assign_node->col;
 
 		upd_field_set_field_no(upd_field, dict_index_get_nth_col_pos(
-					       clust_index, col_sym->col_no),
+						clust_index, col_sym->col_no,
+						NULL),
 				       clust_index, NULL);
 		upd_field->exp = assign_node->val;
 
diff --git a/storage/innobase/row/row0sel.cc b/storage/innobase/row/row0sel.cc
index e5a7694cb93..d888c1ef645 100644
--- a/storage/innobase/row/row0sel.cc
+++ b/storage/innobase/row/row0sel.cc
@@ -56,6 +56,7 @@ Created 12/19/1997 Heikki Tuuri
 #include "row0mysql.h"
 #include "read0read.h"
 #include "buf0lru.h"
+#include "srv0srv.h"
 #include "ha_prototypes.h"
 #include "m_string.h" /* for my_sys.h */
 #include "my_sys.h" /* DEBUG_SYNC_C */
@@ -2936,9 +2937,14 @@ row_sel_store_mysql_rec(
 			: templ->rec_field_no;
 		/* We should never deliver column prefixes to MySQL,
 		except for evaluating innobase_index_cond(). */
+		/* ...actually, we do want to do this in order to
+		support the prefix query optimization.
+
 		ut_ad(dict_index_get_nth_field(index, field_no)->prefix_len
 		      == 0);
 
+		...so we disable this assert. */
+
 		if (!row_sel_store_mysql_field(mysql_rec, prebuilt,
 					       rec, index, offsets,
 					       field_no, templ)) {
@@ -3031,6 +3037,8 @@ row_sel_get_clust_rec_for_mysql(
 	dberr_t		err;
 	trx_t*		trx;
 
+	srv_stats.n_sec_rec_cluster_reads.inc();
+
 	*out_rec = NULL;
 	trx = thr_get_trx(thr);
 
@@ -3686,6 +3694,7 @@ row_search_for_mysql(
 	ulint*		offsets				= offsets_;
 	ibool		table_lock_waited		= FALSE;
 	byte*		next_buf			= 0;
+	ibool		use_clustered_index		= FALSE;
 
 	rec_offs_init(offsets_);
 
@@ -4709,10 +4718,68 @@ locks_ok:
 	}
 
 	/* Get the clustered index record if needed, if we did not do the
-	search using the clustered index. */
-
-	if (index != clust_index && prebuilt->need_to_access_clustered) {
+	search using the clustered index... */
+
+	use_clustered_index =
+		(index != clust_index && prebuilt->need_to_access_clustered);
+
+	if (use_clustered_index && srv_prefix_index_cluster_optimization
+	    && prebuilt->n_template <= index->n_fields) {
+		/* ...but, perhaps avoid the clustered index lookup if
+		all of the following are true:
+		1) all columns are in the secondary index
+		2) all values for columns that are prefix-only
+		   indexes are shorter than the prefix size
+		This optimization can avoid many IOs for certain schemas.
+		*/
+		ibool row_contains_all_values = TRUE;
+		int i;
+		for (i = 0; i < prebuilt->n_template; i++) {
+			/* Condition (1) from above: is the field in the
+			index (prefix or not)? */
+			mysql_row_templ_t* templ =
+				prebuilt->mysql_template + i;
+			ulint secondary_index_field_no =
+				templ->rec_prefix_field_no;
+			if (secondary_index_field_no == ULINT_UNDEFINED) {
+				row_contains_all_values = FALSE;
+				break;
+			}
+			/* Condition (2) from above: if this is a
+			prefix, is this row's value size shorter
+			than the prefix? */
+			if (templ->rec_field_is_prefix) {
+				ulint record_size = rec_offs_nth_size(
+					offsets,
+					secondary_index_field_no);
+				const dict_field_t *field =
+					dict_index_get_nth_field(
+						index,
+						secondary_index_field_no);
+				ut_a(field->prefix_len > 0);
+				if (record_size >= field->prefix_len) {
+					row_contains_all_values = FALSE;
+					break;
+				}
+			}
+		}
+		/* If (1) and (2) were true for all columns above, use
+		rec_prefix_field_no instead of rec_field_no, and skip
+		the clustered lookup below. */
+		if (row_contains_all_values) {
+			for (i = 0; i < prebuilt->n_template; i++) {
+				mysql_row_templ_t* templ =
+					prebuilt->mysql_template + i;
+				templ->rec_field_no =
+					templ->rec_prefix_field_no;
+				ut_a(templ->rec_field_no != ULINT_UNDEFINED);
+			}
+			use_clustered_index = FALSE;
+			srv_stats.n_sec_rec_cluster_reads_avoided.inc();
+		}
+	}
 
+	if (use_clustered_index) {
 requires_clust_rec:
 		ut_ad(index != clust_index);
 		/* We use a 'goto' to the preceding label if a consistent
diff --git a/storage/innobase/srv/srv0mon.cc b/storage/innobase/srv/srv0mon.cc
index 7be25966557..44e228dc523 100644
--- a/storage/innobase/srv/srv0mon.cc
+++ b/storage/innobase/srv/srv0mon.cc
@@ -309,6 +309,18 @@ static monitor_info_t	innodb_counter_info[] =
 	 MONITOR_EXISTING | MONITOR_DEFAULT_ON),
 	 MONITOR_DEFAULT_START, MONITOR_OVLD_PAGES_READ},
 
+	{"buffer_index_sec_rec_cluster_reads", "buffer",
+	 "Number of secondary record reads triggered cluster read",
+	 static_cast<monitor_type_t>(
+	 MONITOR_EXISTING | MONITOR_DEFAULT_ON),
+	 MONITOR_DEFAULT_START, MONITOR_OVLD_INDEX_SEC_REC_CLUSTER_READS},
+
+	{"buffer_index_sec_rec_cluster_reads_avoided", "buffer",
+	 "Number of secondary record reads avoided triggering cluster read",
+	 static_cast<monitor_type_t>(
+	 MONITOR_EXISTING | MONITOR_DEFAULT_ON),
+	 MONITOR_DEFAULT_START, MONITOR_OVLD_INDEX_SEC_REC_CLUSTER_READS_AVOIDED},
+
 	{"buffer_data_reads", "buffer",
 	 "Amount of data read in bytes (innodb_data_reads)",
 	 static_cast<monitor_type_t>(
@@ -1646,6 +1658,16 @@ srv_mon_process_existing_counter(
 		value = stat.n_pages_read;
 		break;
 
+	/* Number of times secondary index lookup triggered cluster lookup */
+	case MONITOR_OVLD_INDEX_SEC_REC_CLUSTER_READS:
+		value = srv_stats.n_sec_rec_cluster_reads;
+		break;
+	/* Number of times prefix optimization avoided triggering cluster
+	lookup */
+	case MONITOR_OVLD_INDEX_SEC_REC_CLUSTER_READS_AVOIDED:
+		value = srv_stats.n_sec_rec_cluster_reads_avoided;
+		break;
+
 	/* innodb_data_reads, the total number of data reads */
 	case MONITOR_OVLD_BYTE_READ:
 		value = srv_stats.data_read;
diff --git a/storage/innobase/srv/srv0srv.cc b/storage/innobase/srv/srv0srv.cc
index 5dfb38b53c7..796fe4c9c59 100644
--- a/storage/innobase/srv/srv0srv.cc
+++ b/storage/innobase/srv/srv0srv.cc
@@ -356,6 +356,10 @@ UNIV_INTERN ulint	srv_fast_shutdown	= 0;
 /* Generate a innodb_status.<pid> file */
 UNIV_INTERN ibool	srv_innodb_status	= FALSE;
 
+/* Optimize prefix index queries to skip cluster index lookup when possible */
+/* Enables or disables this prefix optimization.  Disabled by default. */
+UNIV_INTERN my_bool	srv_prefix_index_cluster_optimization = 0;
+
 /* When estimating number of different key values in an index, sample
 this many index pages, there are 2 ways to calculate statistics:
 * persistent stats that are calculated by ANALYZE TABLE and saved
@@ -1566,6 +1570,11 @@ srv_export_innodb_status(void)
 	}
 #endif /* UNIV_DEBUG */
 
+	export_vars.innodb_sec_rec_cluster_reads =
+		srv_stats.n_sec_rec_cluster_reads;
+	export_vars.innodb_sec_rec_cluster_reads_avoided =
+		srv_stats.n_sec_rec_cluster_reads_avoided;
+
 	mutex_exit(&srv_innodb_monitor_mutex);
 }
 
diff --git a/storage/innobase/trx/trx0rec.cc b/storage/innobase/trx/trx0rec.cc
index a698b37c2a6..4d3458ff8bb 100644
--- a/storage/innobase/trx/trx0rec.cc
+++ b/storage/innobase/trx/trx0rec.cc
@@ -781,7 +781,8 @@ trx_undo_page_report_modify(
 				}
 
 				pos = dict_index_get_nth_col_pos(index,
-								 col_no);
+								 col_no,
+								 NULL);
 				ptr += mach_write_compressed(ptr, pos);
 
 				/* Save the old value of field */
diff --git a/storage/xtradb/dict/dict0dict.cc b/storage/xtradb/dict/dict0dict.cc
index 05169cbbaa0..1292c888e58 100644
--- a/storage/xtradb/dict/dict0dict.cc
+++ b/storage/xtradb/dict/dict0dict.cc
@@ -748,17 +748,24 @@ dict_index_get_nth_col_or_prefix_pos(
 /*=================================*/
 	const dict_index_t*	index,		/*!< in: index */
 	ulint			n,		/*!< in: column number */
-	ibool			inc_prefix)	/*!< in: TRUE=consider
+	ibool			inc_prefix,	/*!< in: TRUE=consider
 						column prefixes too */
+	ulint*			prefix_col_pos)	/*!< out: col num if prefix */
 {
 	const dict_field_t*	field;
 	const dict_col_t*	col;
 	ulint			pos;
 	ulint			n_fields;
+	ulint			prefixed_pos_dummy;
 
 	ut_ad(index);
 	ut_ad(index->magic_n == DICT_INDEX_MAGIC_N);
 
+	if (!prefix_col_pos) {
+		prefix_col_pos = &prefixed_pos_dummy;
+	}
+	*prefix_col_pos = ULINT_UNDEFINED;
+
 	col = dict_table_get_nth_col(index->table, n);
 
 	if (dict_index_is_clust(index)) {
@@ -771,10 +778,11 @@ dict_index_get_nth_col_or_prefix_pos(
 	for (pos = 0; pos < n_fields; pos++) {
 		field = dict_index_get_nth_field(index, pos);
 
-		if (col == field->col
-		    && (inc_prefix || field->prefix_len == 0)) {
-
-			return(pos);
+		if (col == field->col) {
+			*prefix_col_pos = pos;
+			if (inc_prefix || field->prefix_len == 0) {
+				return(pos);
+			}
 		}
 	}
 
@@ -919,7 +927,7 @@ dict_table_get_nth_col_pos(
 	ulint			n)	/*!< in: column number */
 {
 	return(dict_index_get_nth_col_pos(dict_table_get_first_index(table),
-					  n));
+					  n, NULL));
 }
 
 /********************************************************************//**
diff --git a/storage/xtradb/handler/ha_innodb.cc b/storage/xtradb/handler/ha_innodb.cc
index 93563b16c13..f3b11672735 100644
--- a/storage/xtradb/handler/ha_innodb.cc
+++ b/storage/xtradb/handler/ha_innodb.cc
@@ -998,6 +998,14 @@ static SHOW_VAR innodb_status_variables[]= {
   {"onlineddl_pct_progress",
   (char*) &export_vars.innodb_onlineddl_pct_progress, SHOW_LONG},
 
+  /* Times secondary index lookup triggered cluster lookup and
+  times prefix optimization avoided triggering cluster lookup */
+  {"secondary_index_triggered_cluster_reads",
+  (char*) &export_vars.innodb_sec_rec_cluster_reads,	  SHOW_LONG},
+  {"secondary_index_triggered_cluster_reads_avoided",
+  (char*) &export_vars.innodb_sec_rec_cluster_reads_avoided, SHOW_LONG},
+
+
   {NullS, NullS, SHOW_LONG}
 };
 
@@ -7615,11 +7623,20 @@ build_template_field(
 	templ->col_no = i;
 	templ->clust_rec_field_no = dict_col_get_clust_pos(col, clust_index);
 	ut_a(templ->clust_rec_field_no != ULINT_UNDEFINED);
+	templ->rec_field_is_prefix = FALSE;
 
 	if (dict_index_is_clust(index)) {
 		templ->rec_field_no = templ->clust_rec_field_no;
+		templ->rec_prefix_field_no = ULINT_UNDEFINED;
 	} else {
-		templ->rec_field_no = dict_index_get_nth_col_pos(index, i);
+		/* If we're in a secondary index, keep track
+		* of the original index position even if this
+		* is just a prefix index; we will use this
+		* later to avoid a cluster index lookup in
+		* some cases.*/
+
+		templ->rec_field_no = dict_index_get_nth_col_pos(index, i,
+						&templ->rec_prefix_field_no);
 	}
 
 	if (field->real_maybe_null()) {
@@ -7650,6 +7667,13 @@ build_template_field(
 	if (!dict_index_is_clust(index)
 	    && templ->rec_field_no == ULINT_UNDEFINED) {
 		prebuilt->need_to_access_clustered = TRUE;
+
+		if (templ->rec_prefix_field_no != ULINT_UNDEFINED) {
+			dict_field_t* field = dict_index_get_nth_field(
+						index,
+						templ->rec_prefix_field_no);
+			templ->rec_field_is_prefix = (field->prefix_len != 0);
+		}
 	}
 
 	if (prebuilt->mysql_prefix_len < templ->mysql_col_offset
@@ -7811,7 +7835,8 @@ ha_innobase::build_template(
 				} else {
 					templ->icp_rec_field_no
 						= dict_index_get_nth_col_pos(
-							prebuilt->index, i);
+							prebuilt->index, i,
+							NULL);
 				}
 
 				if (dict_index_is_clust(prebuilt->index)) {
@@ -7841,7 +7866,7 @@ ha_innobase::build_template(
 
 				templ->icp_rec_field_no
 					= dict_index_get_nth_col_or_prefix_pos(
-						prebuilt->index, i, TRUE);
+						prebuilt->index, i, TRUE, NULL);
 				ut_ad(templ->icp_rec_field_no
 				      != ULINT_UNDEFINED);
 
@@ -19705,6 +19730,12 @@ static MYSQL_SYSVAR_ULONG(
   1000000, 0);			/* Maximum value */
 #endif /* HAVE_ATOMIC_BUILTINS */
 
+static MYSQL_SYSVAR_BOOL(prefix_index_cluster_optimization,
+  srv_prefix_index_cluster_optimization,
+  PLUGIN_VAR_OPCMDARG,
+  "Enable prefix optimization to sometimes avoid cluster index lookups.",
+  NULL, NULL, FALSE);
+
 static MYSQL_SYSVAR_ULONG(thread_sleep_delay, srv_thread_sleep_delay,
   PLUGIN_VAR_RQCMDARG,
   "Time of innodb thread sleeping before joining InnoDB queue (usec). "
@@ -20207,6 +20238,7 @@ static struct st_mysql_sys_var* innobase_system_variables[]= {
 #ifdef HAVE_ATOMIC_BUILTINS
   MYSQL_SYSVAR(adaptive_max_sleep_delay),
 #endif /* HAVE_ATOMIC_BUILTINS */
+  MYSQL_SYSVAR(prefix_index_cluster_optimization),
   MYSQL_SYSVAR(thread_sleep_delay),
   MYSQL_SYSVAR(autoinc_lock_mode),
   MYSQL_SYSVAR(show_verbose_locks),
diff --git a/storage/xtradb/handler/handler0alter.cc b/storage/xtradb/handler/handler0alter.cc
index 2d7fd259cb1..85c77f4d8a2 100644
--- a/storage/xtradb/handler/handler0alter.cc
+++ b/storage/xtradb/handler/handler0alter.cc
@@ -1199,7 +1199,8 @@ innobase_rec_to_mysql(
 
 		field->reset();
 
-		ipos = dict_index_get_nth_col_or_prefix_pos(index, i, TRUE);
+		ipos = dict_index_get_nth_col_or_prefix_pos(index, i, TRUE,
+							    NULL);
 
 		if (ipos == ULINT_UNDEFINED
 		    || rec_offs_nth_extern(offsets, ipos)) {
@@ -1251,7 +1252,8 @@ innobase_fields_to_mysql(
 
 		field->reset();
 
-		ipos = dict_index_get_nth_col_or_prefix_pos(index, i, TRUE);
+		ipos = dict_index_get_nth_col_or_prefix_pos(index, i, TRUE,
+							    NULL);
 
 		if (ipos == ULINT_UNDEFINED
 		    || dfield_is_ext(&fields[ipos])
diff --git a/storage/xtradb/include/dict0dict.h b/storage/xtradb/include/dict0dict.h
index 78503d954ba..2642f8b1767 100644
--- a/storage/xtradb/include/dict0dict.h
+++ b/storage/xtradb/include/dict0dict.h
@@ -1158,8 +1158,9 @@ ulint
 dict_index_get_nth_col_pos(
 /*=======================*/
 	const dict_index_t*	index,	/*!< in: index */
-	ulint			n)	/*!< in: column number */
-	__attribute__((nonnull, warn_unused_result));
+	ulint			n,	/*!< in: column number */
+	ulint*			prefix_col_pos) /*!< out: col num if prefix */
+	__attribute__((nonnull(1), warn_unused_result));
 /********************************************************************//**
 Looks for column n in an index.
 @return position in internal representation of the index;
@@ -1170,9 +1171,11 @@ dict_index_get_nth_col_or_prefix_pos(
 /*=================================*/
 	const dict_index_t*	index,		/*!< in: index */
 	ulint			n,		/*!< in: column number */
-	ibool			inc_prefix)	/*!< in: TRUE=consider
+	ibool			inc_prefix,	/*!< in: TRUE=consider
 						column prefixes too */
-	__attribute__((nonnull, warn_unused_result));
+	ulint*			prefix_col_pos)	/*!< out: col num if prefix */
+
+	__attribute__((nonnull(1), warn_unused_result));
 /********************************************************************//**
 Returns TRUE if the index contains a column or a prefix of that column.
 @return	TRUE if contains the column or its prefix */
diff --git a/storage/xtradb/include/dict0dict.ic b/storage/xtradb/include/dict0dict.ic
index 2b698dd7218..676d09b7268 100644
--- a/storage/xtradb/include/dict0dict.ic
+++ b/storage/xtradb/include/dict0dict.ic
@@ -1225,7 +1225,8 @@ dict_index_get_sys_col_pos(
 	}
 
 	return(dict_index_get_nth_col_pos(
-		       index, dict_table_get_sys_col_no(index->table, type)));
+			index, dict_table_get_sys_col_no(index->table, type),
+			NULL));
 }
 
 /*********************************************************************//**
@@ -1277,9 +1278,11 @@ ulint
 dict_index_get_nth_col_pos(
 /*=======================*/
 	const dict_index_t*	index,	/*!< in: index */
-	ulint			n)	/*!< in: column number */
+	ulint			n,	/*!< in: column number */
+	ulint*			prefix_col_pos) /*!< out: col num if prefix */
 {
-	return(dict_index_get_nth_col_or_prefix_pos(index, n, FALSE));
+	return(dict_index_get_nth_col_or_prefix_pos(index, n, FALSE,
+						    prefix_col_pos));
 }
 
 #ifndef UNIV_HOTBACKUP
diff --git a/storage/xtradb/include/row0mysql.h b/storage/xtradb/include/row0mysql.h
index cd37a2f69bb..597d0043134 100644
--- a/storage/xtradb/include/row0mysql.h
+++ b/storage/xtradb/include/row0mysql.h
@@ -606,6 +606,12 @@ struct mysql_row_templ_t {
 					Innobase record in the current index;
 					not defined if template_type is
 					ROW_MYSQL_WHOLE_ROW */
+	ibool	rec_field_is_prefix;	/* is this field in a prefix index? */
+	ulint	rec_prefix_field_no;	/* record field, even if just a
+					prefix; same as rec_field_no when not a
+					prefix, otherwise rec_field_no is
+					ULINT_UNDEFINED but this is the true
+					field number*/
 	ulint	clust_rec_field_no;	/*!< field number of the column in an
 					Innobase record in the clustered index;
 					not defined if template_type is
@@ -708,7 +714,9 @@ struct row_prebuilt_t {
 					columns through a secondary index
 					and at least one column is not in
 					the secondary index, then this is
-					set to TRUE */
+					set to TRUE; note that sometimes this
+					is set but we later optimize out the
+					clustered index lookup */
 	unsigned	templ_contains_blob:1;/*!< TRUE if the template contains
 					a column with DATA_BLOB ==
 					get_innobase_type_from_mysql_type();
diff --git a/storage/xtradb/include/srv0mon.h b/storage/xtradb/include/srv0mon.h
index c1585c3072c..2dcf3ed480e 100644
--- a/storage/xtradb/include/srv0mon.h
+++ b/storage/xtradb/include/srv0mon.h
@@ -167,6 +167,8 @@ enum monitor_id_t {
 	MONITOR_OVLD_INDEX_PAGES_WRITTEN,
 	MONITOR_OVLD_NON_INDEX_PAGES_WRITTEN,
 	MONITOR_OVLD_PAGES_READ,
+	MONITOR_OVLD_INDEX_SEC_REC_CLUSTER_READS,
+	MONITOR_OVLD_INDEX_SEC_REC_CLUSTER_READS_AVOIDED,
 	MONITOR_OVLD_BYTE_READ,
 	MONITOR_OVLD_BYTE_WRITTEN,
 	MONITOR_FLUSH_BATCH_SCANNED,
diff --git a/storage/xtradb/include/srv0srv.h b/storage/xtradb/include/srv0srv.h
index 2825e37ba86..e47b6f1751d 100644
--- a/storage/xtradb/include/srv0srv.h
+++ b/storage/xtradb/include/srv0srv.h
@@ -158,6 +158,12 @@ struct srv_stats_t {
 	/** Number of rows inserted */
 	ulint_ctr_64_t		n_rows_inserted;
 
+	/** Number of times secondary index lookup triggered cluster lookup */
+	ulint_ctr_64_t		n_sec_rec_cluster_reads;
+
+	/** Number of times prefix optimization avoided triggering cluster lookup */
+	ulint_ctr_64_t		n_sec_rec_cluster_reads_avoided;
+
 	ulint_ctr_1_t		lock_deadlock_count;
 
 	ulint_ctr_1_t		n_lock_max_wait_time;
@@ -326,6 +332,10 @@ extern ulong	srv_auto_extend_increment;
 
 extern ibool	srv_created_new_raw;
 
+/* Optimize prefix index queries to skip cluster index lookup when possible */
+/* Enables or disables this prefix optimization.  Disabled by default. */
+extern my_bool	srv_prefix_index_cluster_optimization;
+
 /** Maximum number of srv_n_log_files, or innodb_log_files_in_group */
 #define SRV_N_LOG_FILES_MAX 100
 extern ulong	srv_n_log_files;
@@ -1183,6 +1193,9 @@ struct export_var_t{
 						compression */
 	ib_int64_t innodb_pages_page_compression_error;/*!< Number of page
 						compression errors */
+
+	ulint innodb_sec_rec_cluster_reads;	/*!< srv_sec_rec_cluster_reads */
+	ulint innodb_sec_rec_cluster_reads_avoided; /*!< srv_sec_rec_cluster_reads_avoided */
 };
 
 /** Thread slot in the thread table.  */
diff --git a/storage/xtradb/pars/pars0opt.cc b/storage/xtradb/pars/pars0opt.cc
index cbed2b39eeb..5a7e1861d74 100644
--- a/storage/xtradb/pars/pars0opt.cc
+++ b/storage/xtradb/pars/pars0opt.cc
@@ -948,12 +948,14 @@ opt_find_all_cols(
 	/* Fill in the field_no fields in sym_node */
 
 	sym_node->field_nos[SYM_CLUST_FIELD_NO] = dict_index_get_nth_col_pos(
-		dict_table_get_first_index(index->table), sym_node->col_no);
+		dict_table_get_first_index(index->table), sym_node->col_no,
+		NULL);
 	if (!dict_index_is_clust(index)) {
 
 		ut_a(plan);
 
-		col_pos = dict_index_get_nth_col_pos(index, sym_node->col_no);
+		col_pos = dict_index_get_nth_col_pos(index, sym_node->col_no,
+						     NULL);
 
 		if (col_pos == ULINT_UNDEFINED) {
 
diff --git a/storage/xtradb/pars/pars0pars.cc b/storage/xtradb/pars/pars0pars.cc
index f051481184b..18feb9407c7 100644
--- a/storage/xtradb/pars/pars0pars.cc
+++ b/storage/xtradb/pars/pars0pars.cc
@@ -1232,7 +1232,8 @@ pars_process_assign_list(
 		col_sym = assign_node->col;
 
 		upd_field_set_field_no(upd_field, dict_index_get_nth_col_pos(
-					       clust_index, col_sym->col_no),
+						clust_index, col_sym->col_no,
+						NULL),
 				       clust_index, NULL);
 		upd_field->exp = assign_node->val;
 
diff --git a/storage/xtradb/row/row0sel.cc b/storage/xtradb/row/row0sel.cc
index fd50e2240b5..5b85086e4a9 100644
--- a/storage/xtradb/row/row0sel.cc
+++ b/storage/xtradb/row/row0sel.cc
@@ -56,6 +56,7 @@ Created 12/19/1997 Heikki Tuuri
 #include "row0mysql.h"
 #include "read0read.h"
 #include "buf0lru.h"
+#include "srv0srv.h"
 #include "ha_prototypes.h"
 #include "srv0start.h"
 #include "m_string.h" /* for my_sys.h */
@@ -2942,9 +2943,14 @@ row_sel_store_mysql_rec(
 			: templ->rec_field_no;
 		/* We should never deliver column prefixes to MySQL,
 		except for evaluating innobase_index_cond(). */
+		/* ...actually, we do want to do this in order to
+		support the prefix query optimization.
+
 		ut_ad(dict_index_get_nth_field(index, field_no)->prefix_len
 		      == 0);
 
+		...so we disable this assert. */
+
 		if (!row_sel_store_mysql_field(mysql_rec, prebuilt,
 					       rec, index, offsets,
 					       field_no, templ)) {
@@ -3037,6 +3043,8 @@ row_sel_get_clust_rec_for_mysql(
 	dberr_t		err;
 	trx_t*		trx;
 
+	srv_stats.n_sec_rec_cluster_reads.inc();
+
 	*out_rec = NULL;
 	trx = thr_get_trx(thr);
 
@@ -3693,6 +3701,7 @@ row_search_for_mysql(
 	ulint*		offsets				= offsets_;
 	ibool		table_lock_waited		= FALSE;
 	byte*		next_buf			= 0;
+	ibool		use_clustered_index		= FALSE;
 
 	rec_offs_init(offsets_);
 
@@ -4727,10 +4736,69 @@ locks_ok:
 	}
 
 	/* Get the clustered index record if needed, if we did not do the
-	search using the clustered index. */
+	search using the clustered index... */
+
+	use_clustered_index =
+		(index != clust_index && prebuilt->need_to_access_clustered);
+
+	if (use_clustered_index && srv_prefix_index_cluster_optimization
+	    && prebuilt->n_template <= index->n_fields) {
+		/* ...but, perhaps avoid the clustered index lookup if
+		all of the following are true:
+		1) all columns are in the secondary index
+		2) all values for columns that are prefix-only
+		   indexes are shorter than the prefix size
+		This optimization can avoid many IOs for certain schemas.
+		*/
+		ibool row_contains_all_values = TRUE;
+		int i;
+		for (i = 0; i < prebuilt->n_template; i++) {
+			/* Condition (1) from above: is the field in the
+			index (prefix or not)? */
+			mysql_row_templ_t* templ =
+				prebuilt->mysql_template + i;
+			ulint secondary_index_field_no =
+				templ->rec_prefix_field_no;
+			if (secondary_index_field_no == ULINT_UNDEFINED) {
+				row_contains_all_values = FALSE;
+				break;
+			}
+			/* Condition (2) from above: if this is a
+			prefix, is this row's value size shorter
+			than the prefix? */
+			if (templ->rec_field_is_prefix) {
+				ulint record_size = rec_offs_nth_size(
+					offsets,
+					secondary_index_field_no);
+				const dict_field_t *field =
+					dict_index_get_nth_field(
+						index,
+						secondary_index_field_no);
+				ut_a(field->prefix_len > 0);
+				if (record_size >= field->prefix_len) {
+					row_contains_all_values = FALSE;
+					break;
+				}
+			}
+		}
+		/* If (1) and (2) were true for all columns above, use
+		rec_prefix_field_no instead of rec_field_no, and skip
+		the clustered lookup below. */
+		if (row_contains_all_values) {
+			for (i = 0; i < prebuilt->n_template; i++) {
+				mysql_row_templ_t* templ =
+					prebuilt->mysql_template + i;
+				templ->rec_field_no =
+					templ->rec_prefix_field_no;
+				ut_a(templ->rec_field_no != ULINT_UNDEFINED);
+			}
+			use_clustered_index = FALSE;
+			srv_stats.n_sec_rec_cluster_reads_avoided.inc();
+		}
+	}
+
+	if (use_clustered_index) {
 
-	if (index != clust_index && prebuilt->need_to_access_clustered) {
- 
 requires_clust_rec:
 		ut_ad(index != clust_index);
 		/* We use a 'goto' to the preceding label if a consistent
diff --git a/storage/xtradb/srv/srv0mon.cc b/storage/xtradb/srv/srv0mon.cc
index 7be25966557..44e228dc523 100644
--- a/storage/xtradb/srv/srv0mon.cc
+++ b/storage/xtradb/srv/srv0mon.cc
@@ -309,6 +309,18 @@ static monitor_info_t	innodb_counter_info[] =
 	 MONITOR_EXISTING | MONITOR_DEFAULT_ON),
 	 MONITOR_DEFAULT_START, MONITOR_OVLD_PAGES_READ},
 
+	{"buffer_index_sec_rec_cluster_reads", "buffer",
+	 "Number of secondary record reads triggered cluster read",
+	 static_cast<monitor_type_t>(
+	 MONITOR_EXISTING | MONITOR_DEFAULT_ON),
+	 MONITOR_DEFAULT_START, MONITOR_OVLD_INDEX_SEC_REC_CLUSTER_READS},
+
+	{"buffer_index_sec_rec_cluster_reads_avoided", "buffer",
+	 "Number of secondary record reads avoided triggering cluster read",
+	 static_cast<monitor_type_t>(
+	 MONITOR_EXISTING | MONITOR_DEFAULT_ON),
+	 MONITOR_DEFAULT_START, MONITOR_OVLD_INDEX_SEC_REC_CLUSTER_READS_AVOIDED},
+
 	{"buffer_data_reads", "buffer",
 	 "Amount of data read in bytes (innodb_data_reads)",
 	 static_cast<monitor_type_t>(
@@ -1646,6 +1658,16 @@ srv_mon_process_existing_counter(
 		value = stat.n_pages_read;
 		break;
 
+	/* Number of times secondary index lookup triggered cluster lookup */
+	case MONITOR_OVLD_INDEX_SEC_REC_CLUSTER_READS:
+		value = srv_stats.n_sec_rec_cluster_reads;
+		break;
+	/* Number of times prefix optimization avoided triggering cluster
+	lookup */
+	case MONITOR_OVLD_INDEX_SEC_REC_CLUSTER_READS_AVOIDED:
+		value = srv_stats.n_sec_rec_cluster_reads_avoided;
+		break;
+
 	/* innodb_data_reads, the total number of data reads */
 	case MONITOR_OVLD_BYTE_READ:
 		value = srv_stats.data_read;
diff --git a/storage/xtradb/srv/srv0srv.cc b/storage/xtradb/srv/srv0srv.cc
index a6035bdaa6c..60271f22dcd 100644
--- a/storage/xtradb/srv/srv0srv.cc
+++ b/storage/xtradb/srv/srv0srv.cc
@@ -490,6 +490,10 @@ UNIV_INTERN ulint	srv_fast_shutdown	= 0;
 /* Generate a innodb_status.<pid> file */
 UNIV_INTERN ibool	srv_innodb_status	= FALSE;
 
+/* Optimize prefix index queries to skip cluster index lookup when possible */
+/* Enables or disables this prefix optimization.  Disabled by default. */
+UNIV_INTERN my_bool	srv_prefix_index_cluster_optimization = 0;
+
 /* When estimating number of different key values in an index, sample
 this many index pages, there are 2 ways to calculate statistics:
 * persistent stats that are calculated by ANALYZE TABLE and saved
@@ -1960,6 +1964,11 @@ srv_export_innodb_status(void)
 	}
 #endif /* UNIV_DEBUG */
 
+	export_vars.innodb_sec_rec_cluster_reads =
+		srv_stats.n_sec_rec_cluster_reads;
+	export_vars.innodb_sec_rec_cluster_reads_avoided =
+		srv_stats.n_sec_rec_cluster_reads_avoided;
+
 	mutex_exit(&srv_innodb_monitor_mutex);
 }
 
diff --git a/storage/xtradb/trx/trx0rec.cc b/storage/xtradb/trx/trx0rec.cc
index a698b37c2a6..4d3458ff8bb 100644
--- a/storage/xtradb/trx/trx0rec.cc
+++ b/storage/xtradb/trx/trx0rec.cc
@@ -781,7 +781,8 @@ trx_undo_page_report_modify(
 				}
 
 				pos = dict_index_get_nth_col_pos(index,
-								 col_no);
+								 col_no,
+								 NULL);
 				ptr += mach_write_compressed(ptr, pos);
 
 				/* Save the old value of field */