Fixed some issues with FORCE INDEX

Added code to support that force index can be used to force an index scan
instead of a full table scan. Currently this code is disable but I added
a test to verify that things works if the code is ever enabled.

Other things:

- FORCE INDEX will now work with "Range checked for each record" and
  join cache (see main/type_time_6065)
- Removed code ifdef with BAD_OPTIMIZATION (New cost calculations should
  fix this).
- Removed TABLE_LIST->force_index and comment that it should be removed
- Added TABLE->force_index_join and use in the corresponding places.
  This means that FORCE INDEX FOR ORDER BY will not affect keys used
  in joins anymore.
  Remove TODO that the above should be added.
  I still kept TABLE->force_index as it's used in
  test_if_cheaper_ordering() and opt_range.cc
- Removed setting table->force_index when calling test_quick_select() as
  it's not needed (force_index is an argument to test_quick_select())

9 files changed, 236 insertions, 105 deletions

diff --git a/mysql-test/main/key.result b/mysql-test/main/key.result
index 653e41f8e28..762218f8580 100644
--- a/mysql-test/main/key.result
+++ b/mysql-test/main/key.result
@@ -643,7 +643,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	a	5	NULL	6	
 SHOW STATUS LIKE 'Last_query_cost';
 Variable_name	Value
-Last_query_cost	11.003515
+Last_query_cost	8.506592
 DROP TABLE t1;
 #
 # MDEV-21480: Unique key using ref access though eq_ref access can be used
@@ -692,3 +692,42 @@ drop table t1,t2;
 #
 create table t1 (a int, b int, key(a), key(a desc));
 drop table t1;
+# Check some issues with FORCE INDEX and full index scans
+# (Does FORCE INDEX force an index scan)
+#
+create table t1 (a int primary key, b int, c int, d int,
+key k1 (b) using BTREE, key k2 (c,d) using btree) engine=heap;
+insert into t1 select seq as a, seq as b, seq as c, seq as d
+from seq_1_to_100;
+explain select sum(a+b) from t1 force index (k1) where b>0 and a=99;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	k1	k1	5	NULL	100	Using where
+explain select sum(a+b) from t1 force index (k1) where a>0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+explain select sum(a+b) from t1 force index (k1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	
+explain select sum(a+b) from t1 force index for join (k1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	
+explain select sum(a+b) from t1 force index for order by (k1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	
+explain select sum(a+b) from t1 force index (k1,k2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	
+select sum(a+b) from t1 force index (k1);
+sum(a+b)
+10100
+explain select sum(a+b) from t1 force index (primary);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	
+select sum(a+b) from t1 force index (primary);
+sum(a+b)
+10100
+explain select straight_join sum(a+b) from seq_1_to_10 as s, t1 force index (k2) where t1.a=s.seq;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	s	index	PRIMARY	PRIMARY	8	NULL	10	Using index
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
+drop table t1;
diff --git a/mysql-test/main/key.test b/mysql-test/main/key.test
index c891194f1ad..7ec18ad5d0a 100644
--- a/mysql-test/main/key.test
+++ b/mysql-test/main/key.test
@@ -611,3 +611,23 @@ drop table t1,t2;
 --echo #
 create table t1 (a int, b int, key(a), key(a desc));
 drop table t1;
+
+--echo # Check some issues with FORCE INDEX and full index scans
+--echo # (Does FORCE INDEX force an index scan)
+--echo #
+
+create table t1 (a int primary key, b int, c int, d int,
+key k1 (b) using BTREE, key k2 (c,d) using btree) engine=heap;
+insert into t1 select seq as a, seq as b, seq as c, seq as d
+from seq_1_to_100;
+explain select sum(a+b) from t1 force index (k1) where b>0 and a=99;
+explain select sum(a+b) from t1 force index (k1) where a>0;
+explain select sum(a+b) from t1 force index (k1);
+explain select sum(a+b) from t1 force index for join (k1);
+explain select sum(a+b) from t1 force index for order by (k1);
+explain select sum(a+b) from t1 force index (k1,k2);
+select sum(a+b) from t1 force index (k1);
+explain select sum(a+b) from t1 force index (primary);
+select sum(a+b) from t1 force index (primary);
+explain select straight_join sum(a+b) from seq_1_to_10 as s, t1 force index (k2) where t1.a=s.seq;
+drop table t1;
diff --git a/mysql-test/main/type_time_6065.result b/mysql-test/main/type_time_6065.result
index c5fb790b656..75b272430c1 100644
--- a/mysql-test/main/type_time_6065.result
+++ b/mysql-test/main/type_time_6065.result
@@ -408,7 +408,7 @@ t2 force INDEX (col_datetime_key)
 WHERE col_time_key >= col_datetime_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
-1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`
 SELECT * FROM
@@ -439,7 +439,7 @@ t2 force INDEX (col_datetime_key)
 WHERE col_datetime_key >= col_time_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
-1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`
 SELECT * FROM
@@ -532,7 +532,7 @@ t2 force INDEX (col_datetime_key)
 WHERE col_time_key >= col_datetime_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
-1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`
 SELECT * FROM
@@ -563,7 +563,7 @@ t2 force INDEX (col_datetime_key)
 WHERE col_datetime_key >= col_time_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
-1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`
 SELECT * FROM
@@ -656,7 +656,7 @@ t1 force INDEX (col_time_key)
 WHERE col_time_key >= col_datetime_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
-1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`
 SELECT * FROM
@@ -687,7 +687,7 @@ t1 force INDEX (col_time_key)
 WHERE col_datetime_key >= col_time_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
-1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`
 SELECT * FROM
@@ -780,7 +780,7 @@ t1 force INDEX (col_time_key)
 WHERE col_time_key >= col_datetime_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
-1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`
 SELECT * FROM
@@ -811,7 +811,7 @@ t1 force INDEX (col_time_key)
 WHERE col_datetime_key >= col_time_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
-1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`
 SELECT * FROM
@@ -894,7 +894,7 @@ t2 force INDEX (col_datetime_key)
 WHERE col_time_key > col_datetime_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
-1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`
 SELECT * FROM
@@ -920,7 +920,7 @@ t2 force INDEX (col_datetime_key)
 WHERE col_datetime_key > col_time_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
-1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`
 SELECT * FROM
@@ -998,7 +998,7 @@ t2 force INDEX (col_datetime_key)
 WHERE col_time_key > col_datetime_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
-1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`
 SELECT * FROM
@@ -1024,7 +1024,7 @@ t2 force INDEX (col_datetime_key)
 WHERE col_datetime_key > col_time_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
-1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`
 SELECT * FROM
@@ -1102,7 +1102,7 @@ t1 force INDEX (col_time_key)
 WHERE col_time_key > col_datetime_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
-1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`
 SELECT * FROM
@@ -1128,7 +1128,7 @@ t1 force INDEX (col_time_key)
 WHERE col_datetime_key > col_time_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
-1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`
 SELECT * FROM
@@ -1206,7 +1206,7 @@ t1 force INDEX (col_time_key)
 WHERE col_time_key > col_datetime_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
-1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`
 SELECT * FROM
@@ -1232,7 +1232,7 @@ t1 force INDEX (col_time_key)
 WHERE col_datetime_key > col_time_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
-1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`
 SELECT * FROM
@@ -1320,7 +1320,7 @@ t2 force INDEX (col_datetime_key)
 WHERE col_time_key <= col_datetime_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
-1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`
 SELECT * FROM
@@ -1351,7 +1351,7 @@ t2 force INDEX (col_datetime_key)
 WHERE col_datetime_key <= col_time_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
-1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`
 SELECT * FROM
@@ -1444,7 +1444,7 @@ t2 force INDEX (col_datetime_key)
 WHERE col_time_key <= col_datetime_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
-1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`
 SELECT * FROM
@@ -1475,7 +1475,7 @@ t2 force INDEX (col_datetime_key)
 WHERE col_datetime_key <= col_time_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
-1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`
 SELECT * FROM
@@ -1568,7 +1568,7 @@ t1 force INDEX (col_time_key)
 WHERE col_time_key <= col_datetime_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
-1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`
 SELECT * FROM
@@ -1599,7 +1599,7 @@ t1 force INDEX (col_time_key)
 WHERE col_datetime_key <= col_time_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
-1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`
 SELECT * FROM
@@ -1692,7 +1692,7 @@ t1 force INDEX (col_time_key)
 WHERE col_time_key <= col_datetime_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
-1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`
 SELECT * FROM
@@ -1723,7 +1723,7 @@ t1 force INDEX (col_time_key)
 WHERE col_datetime_key <= col_time_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
-1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`
 SELECT * FROM
@@ -1806,7 +1806,7 @@ t2 force INDEX (col_datetime_key)
 WHERE col_time_key < col_datetime_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
-1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`
 SELECT * FROM
@@ -1832,7 +1832,7 @@ t2 force INDEX (col_datetime_key)
 WHERE col_datetime_key < col_time_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
-1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`
 SELECT * FROM
@@ -1910,7 +1910,7 @@ t2 force INDEX (col_datetime_key)
 WHERE col_time_key < col_datetime_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
-1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`
 SELECT * FROM
@@ -1936,7 +1936,7 @@ t2 force INDEX (col_datetime_key)
 WHERE col_datetime_key < col_time_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
-1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where `test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`
 SELECT * FROM
@@ -2014,7 +2014,7 @@ t1 force INDEX (col_time_key)
 WHERE col_time_key < col_datetime_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
-1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`
 SELECT * FROM
@@ -2040,7 +2040,7 @@ t1 force INDEX (col_time_key)
 WHERE col_datetime_key < col_time_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
-1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`
 SELECT * FROM
@@ -2118,7 +2118,7 @@ t1 force INDEX (col_time_key)
 WHERE col_time_key < col_datetime_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
-1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`
 SELECT * FROM
@@ -2144,7 +2144,7 @@ t1 force INDEX (col_time_key)
 WHERE col_datetime_key < col_time_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
-1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Range checked for each record (index map: 0x1); Using index
 Warnings:
 Note	1003	select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where `test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`
 SELECT * FROM
diff --git a/sql/sql_base.h b/sql/sql_base.h
index c86a652c33a..1b294a468a9 100644
--- a/sql/sql_base.h
+++ b/sql/sql_base.h
@@ -357,7 +357,7 @@ inline void setup_table_map(TABLE *table, TABLE_LIST *table_list, uint tablenr)
   }
   table->tablenr= tablenr;
   table->map= (table_map) 1 << tablenr;
-  table->force_index= table_list->force_index;
+  table->force_index= table->force_index_join= 0;
   table->force_index_order= table->force_index_group= 0;
   table->covering_keys= table->s->keys_for_keyread;
 }
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 48c3147a95c..579e4cd27f7 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -8214,8 +8214,6 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd,
   ptr->mdl_type= mdl_type;
   ptr->table_options= table_options;
   ptr->updating=    MY_TEST(table_options & TL_OPTION_UPDATING);
-  /* TODO: remove TL_OPTION_FORCE_INDEX as it looks like it's not used */
-  ptr->force_index= MY_TEST(table_options & TL_OPTION_FORCE_INDEX);
   ptr->ignore_leaves= MY_TEST(table_options & TL_OPTION_IGNORE_LEAVES);
   ptr->sequence=      MY_TEST(table_options & TL_OPTION_SEQUENCE);
   ptr->derived=	    table->sel;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 23463eff2ca..3bd4bd1fd1c 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -437,6 +437,7 @@ POSITION::POSITION()
   records_read= cond_selectivity= read_time= records_out= 0.0;
   prefix_record_count= 0.0;
   key= 0;
+  forced_index= 0;
   use_join_buffer= 0;
   sj_strategy= SJ_OPT_NONE;
   n_sj_tables= 0;
@@ -1960,12 +1961,9 @@ bool JOIN::make_range_rowid_filters()
     filter_map.clear_all();
     filter_map.set_bit(tab->range_rowid_filter_info->get_key_no());
     filter_map.merge(tab->table->with_impossible_ranges);
-    bool force_index_save= tab->table->force_index;
-    tab->table->force_index= true;
     int rc= sel->test_quick_select(thd, filter_map, (table_map) 0,
                                    (ha_rows) HA_POS_ERROR,
-                                   true, false, true, true);
-    tab->table->force_index= force_index_save;
+                                   true /* force index */, false, true, true);
     if (thd->is_error())
       goto no_filter;
     /*
@@ -3218,6 +3216,7 @@ int JOIN::optimize_stage2()
     */
     if ((order || group_list) &&
         tab->type != JT_ALL &&
+        tab->type != JT_NEXT &&
         tab->type != JT_FT &&
         tab->type != JT_REF_OR_NULL &&
         ((order && simple_order) || (group_list && simple_group)))
@@ -8043,6 +8042,7 @@ best_access_path(JOIN      *join,
   TABLE *table= s->table;
   KEYUSE *best_key=         0;
   uint best_max_key_part=   0;
+  uint best_forced_index= MAX_KEY, forced_index= MAX_KEY;
   my_bool found_constraint= 0;
   double best_cost=         DBL_MAX;
   double records=           DBL_MAX;
@@ -8853,7 +8853,7 @@ best_access_path(JOIN      *join,
         best_max_key_part >= table->opt_range[best_key->key].key_parts) &&// (2)
       !((table->file->ha_table_flags() & HA_TABLE_SCAN_ON_INDEX) &&   // (3)
         !table->covering_keys.is_clear_all() && best_key && !s->quick) &&// (3)
-      !(table->force_index && best_key && !s->quick) &&               // (4)
+      !(table->force_index_join && best_key && !s->quick) &&               // (4)
       !(best_key && table->pos_in_table_list->jtbm_subselect))        // (5)
   {                                             // Check full join
     double rnd_records, records_after_filter, org_records;
@@ -8956,24 +8956,61 @@ best_access_path(JOIN      *join,
       DBUG_ASSERT(rnd_records <= s->records);
 
       /* Estimate cost of reading table. */
-      if (table->force_index && !best_key)
+      if (s->cached_forced_index_type)
       {
-        INDEX_READ_COST cost= cost_for_index_read(thd, table, s->ref.key,
-                                                  s->records,
-                                                  (ha_rows)s->worst_seeks);
-        /*
-          The query is using 'force_index' and we did not find a usable key.
-          Caclulcate cost of a table scan with the forced index.
-        */
-        type= JT_NEXT;
-        tmp= cost.read_cost;
-        /* Calculate cost of checking the attached WHERE */
-        tmp= COST_ADD(cost.read_cost, s->records * WHERE_COST_THD(thd));
+        type=         s->cached_forced_index_type;
+        tmp=          s->cached_forced_index_cost;
+        forced_index= s->cached_forced_index;
       }
-      else // table scan
+      else
       {
-        tmp= s->cached_scan_and_compare_time;
-        type= JT_ALL;
+        if (table->force_index_join && !best_key)
+        {
+          /*
+            The query is using 'forced_index' and we did not find a usable key.
+            Calculate cost of a table scan with the forced index.
+          */
+          type= JT_NEXT;
+          if (s->cached_covering_key != MAX_KEY)
+          {
+            /* Use value from estimate_scan_time */
+            forced_index= s->cached_covering_key;
+            tmp= s->cached_scan_and_compare_time;
+          }
+          else
+          {
+#ifdef FORCE_INDEX_SHOULD_FORCE_INDEX_SCAN
+            /* No cached key, use shortest allowed key */
+            key_map keys= *table->file->keys_to_use_for_scanning();
+            keys.intersect(table->keys_in_use_for_query);
+            if ((forced_index= find_shortest_key(table, &keys)) < MAX_KEY)
+            {
+              INDEX_READ_COST cost= cost_for_index_read(thd, table,
+                                                        forced_index,
+                                                        s->records,
+                                                        s->worst_seeks);
+              tmp= cost.read_cost;
+              /* Calculate cost of checking the attached WHERE */
+              tmp= COST_ADD(cost.read_cost,
+                            s->records * WHERE_COST_THD(thd));
+            }
+            else
+#endif
+            {
+              /* No usable key, use table scan */
+              tmp= s->cached_scan_and_compare_time;
+              type= JT_ALL;
+            }
+          }
+        }
+        else // table scan
+        {
+          tmp= s->cached_scan_and_compare_time;
+          type= JT_ALL;
+        }
+        s->cached_forced_index_type= type;
+        s->cached_forced_index_cost= tmp;
+        s->cached_forced_index= forced_index;
       }
 
       if ((table->map & join->outer_join) || disable_jbuf)
@@ -9057,6 +9094,7 @@ best_access_path(JOIN      *join,
       best_cost= tmp;
       records= rnd_records;
       best_key= 0;
+      best_forced_index= forced_index;
       /*
         filter is only set if
         s->quick->get_type() == QUICK_SELECT_I::QS_TYPE_RANGE
@@ -9088,6 +9126,7 @@ best_access_path(JOIN      *join,
   pos->records_out=  records_out;
   pos->read_time=    best_cost;
   pos->key=          best_key;
+  pos->forced_index= best_forced_index;
   pos->type=         best_type;
   pos->table=        s;
   pos->ref_depend_map= best_ref_depends_map;
@@ -11788,7 +11827,7 @@ bool JOIN::get_best_combination()
       j= jt;
     }
     
-    *j= *best_positions[tablenr].table;
+    *j= *cur_pos->table;
 
     j->bush_root_tab= sjm_nest_root;
 
@@ -11805,29 +11844,34 @@ bool JOIN::get_best_combination()
 
     if (j->type == JT_SYSTEM)
       goto loop_end;
-    if ( !(keyuse= best_positions[tablenr].key))
+    if (!(keyuse= cur_pos->key))
     {
-      j->type=JT_ALL;
-      if (best_positions[tablenr].use_join_buffer &&
+      if (cur_pos->type == JT_NEXT)             // Forced index
+      {
+        j->type= JT_NEXT;
+        j->index= cur_pos->forced_index;
+      }
+      else
+        j->type= JT_ALL;
+      if (cur_pos->use_join_buffer &&
           tablenr != const_tables)
 	full_join= 1;
     }
-
     if ((j->type == JT_REF || j->type == JT_EQ_REF) &&
         is_hash_join_key_no(j->ref.key))
       hash_join= TRUE; 
 
     j->range_rowid_filter_info=
-      best_positions[tablenr].range_rowid_filter_info;
+      cur_pos->range_rowid_filter_info;
 
   loop_end:
     /* 
       Save records_read in JOIN_TAB so that select_describe()/etc don't have
       to access join->best_positions[]. 
     */
-    j->records_read= best_positions[tablenr].records_read;
-    j->records_out=  best_positions[tablenr].records_out;
-    j->cond_selectivity= best_positions[tablenr].cond_selectivity;
+    j->records_read= cur_pos->records_read;
+    j->records_out=  cur_pos->records_out;
+    j->cond_selectivity= cur_pos->cond_selectivity;
     DBUG_ASSERT(j->cond_selectivity <= 1.0);
     crash_if_first_double_is_bigger(j->records_out,
                                     j->records_read *
@@ -12098,7 +12142,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j,
     if (!keyparts && allow_full_scan)
     {
       /* It's a LooseIndexScan strategy scanning whole index */
-      j->type= JT_ALL;
+      j->type= JT_ALL;                // TODO: Check if this should be JT_NEXT
       j->index= key;
       DBUG_RETURN(FALSE);
     }
@@ -13057,13 +13101,14 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
               tab->table->reginfo.impossible_range)
 	    DBUG_RETURN(1);
 	}
-	else if (tab->type == JT_ALL && ! use_quick_range)
+	else if ((tab->type == JT_ALL || tab->type == JT_NEXT) &&
+                  ! use_quick_range)
 	{
 	  if (!tab->const_keys.is_clear_all() &&
 	      tab->table->reginfo.impossible_range)
 	    DBUG_RETURN(1);				// Impossible range
 	  /*
-	    We plan to scan all rows.
+	    We plan to scan all rows either with table or index scan
 	    Check again if we should use an index.
 
             There are two cases:
@@ -14166,6 +14211,7 @@ uint check_join_cache_usage(JOIN_TAB *tab,
   prev_cache= prev_tab->cache;
 
   switch (tab->type) {
+  case JT_NEXT:
   case JT_ALL:
     if (cache_level == 1)
       prev_cache= 0;
@@ -14327,6 +14373,7 @@ restart:
     case JT_EQ_REF:
     case JT_REF:
     case JT_REF_OR_NULL:
+    case JT_NEXT:
     case JT_ALL:
       tab->used_join_cache_level= check_join_cache_usage(tab, options,
                                                          no_jbuf_after,
@@ -14536,7 +14583,6 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after)
       tab->next_select=sub_select;		/* normal select */
     }
 
-
     if (tab->loosescan_match_tab)
     {
       if (!(tab->loosescan_buf= (uchar*)join->thd->alloc(tab->
@@ -14584,13 +14630,32 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after)
           (!jcl || jcl > 4) && !tab->ref.is_access_triggered())
         push_index_cond(tab, tab->ref.key);
       break;
+    case JT_NEXT:                               // Index scan
+      DBUG_ASSERT(!(tab->select && tab->select->quick));
+      if (tab->use_quick == 2)
+      {
+        join->thd->set_status_no_good_index_used();
+	tab->read_first_record= join_init_quick_read_record;
+	if (statistics)
+	  join->thd->inc_status_select_range_check();
+      }
+      else
+      {
+        tab->read_first_record= join_read_first;
+        if (statistics)
+        {
+          join->thd->inc_status_select_scan();
+          join->thd->query_plan_flags|= QPLAN_FULL_SCAN;
+        }
+      }
+      break;
     case JT_ALL:
     case JT_HASH:
       /*
 	If previous table use cache
         If the incoming data set is already sorted don't use cache.
         Also don't use cache if this is the first table in semi-join
-          materialization nest.
+        materialization nest.
       */
       /* These init changes read_record */
       if (tab->use_quick == 2)
@@ -14649,27 +14714,10 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after)
             if (tab->loosescan_match_tab)
               tab->index= tab->loosescan_key;
             else 
-            {
-#ifdef BAD_OPTIMIZATION
-              /*
-                It has turned out that the below change, while speeding things
-                up for disk-bound loads, slows them down for cases when the data
-                is in disk cache (see BUG#35850):
-                See bug #26447: "Using the clustered index for a table scan
-                is always faster than using a secondary index".
-              */
-              if (table->file->pk_is_clustering_key(table->s->primary_key))
-                tab->index= table->s->primary_key;
-              else
-#endif
-              {
-                tab->index=find_shortest_key(table, & table->covering_keys);
-                DBUG_ASSERT(tab->index == tab->cached_covering_key);
-              }
-            }
+              tab->index= tab->cached_covering_key;
 	    tab->read_first_record= join_read_first;
             /* Read with index_first / index_next */
-	    tab->type= tab->type == JT_ALL ? JT_NEXT : JT_HASH_NEXT;		
+	    tab->type= tab->type == JT_ALL ? JT_NEXT : JT_HASH_NEXT;
 	  }
 	}
         if (tab->select && tab->select->quick &&
@@ -14779,7 +14827,8 @@ bool error_if_full_join(JOIN *join)
   for (JOIN_TAB *tab=first_top_level_tab(join, WITH_CONST_TABLES); tab;
        tab= next_top_level_tab(join, tab))
   {
-    if (tab->type == JT_ALL && (!tab->select || !tab->select->quick))
+    if ((tab->type == JT_ALL || tab->type == JT_NEXT) &&
+        (!tab->select || !tab->select->quick))
     {
       my_message(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE,
                  ER_THD(join->thd,
@@ -23251,6 +23300,7 @@ bool test_if_use_dynamic_range_scan(JOIN_TAB *join_tab)
     return (join_tab->use_quick == 2 && test_if_quick_select(join_tab) > 0);
 }
 
+
 int join_init_read_record(JOIN_TAB *tab)
 {
   bool need_unpacking= FALSE;
@@ -23300,10 +23350,29 @@ int join_init_read_record(JOIN_TAB *tab)
   save_copy=     tab->read_record.copy_field;
   save_copy_end= tab->read_record.copy_field_end;
   
-  if (init_read_record(&tab->read_record, tab->join->thd, tab->table,
-                       tab->select, tab->filesort_result, 1, 1, FALSE))
-    return 1;
+  /*
+    JT_NEXT means that we should use an index scan on index 'tab->index'
+    However if filesort is set, the table was already sorted above
+    and now have to retrive the rows from the tmp file or by rnd_pos()
+    If !(tab->select && tab->select->quick)) it means that we are
+    in "Range checked for each record" and we better let the normal
+    init_read_record() handle this case
+  */
 
+  if (tab->type == JT_NEXT && ! tab->filesort &&
+      !(tab->select && tab->select->quick))
+  {
+    /* Used with covered_index scan or force index */
+    if (init_read_record_idx(&tab->read_record, tab->join->thd, tab->table,
+                             1, tab->index, 0))
+      return 1;
+  }
+  else
+  {
+    if (init_read_record(&tab->read_record, tab->join->thd, tab->table,
+                         tab->select, tab->filesort_result, 1, 1, FALSE))
+      return 1;
+  }
   tab->read_record.copy_field=     save_copy;
   tab->read_record.copy_field_end= save_copy_end;
 
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 49503d095bb..f6c1ca63a99 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -358,6 +358,7 @@ typedef struct st_join_table {
   /* set by estimate_scan_time() */
   double        cached_scan_time;
   double        cached_scan_and_compare_time;
+  double        cached_forced_index_cost;
 
   /*
     dependent is the table that must be read before the current one
@@ -406,7 +407,8 @@ typedef struct st_join_table {
   uint          used_blobs;
   uint          used_null_fields;
   uint          used_uneven_bit_fields;
-  enum join_type type;
+  uint          cached_forced_index;
+  enum join_type type, cached_forced_index_type;
   /* If first key part is used for any key in 'key_dependent' */
   bool          key_start_dependent;
   bool          cached_eq_ref_table,eq_ref_table;
@@ -1044,7 +1046,7 @@ public:
     are covered by the specified semi-join strategy
   */
   uint n_sj_tables;
-
+  uint forced_index;                    // If force_index() is used
   /*
     TRUE <=> join buffering will be used. At the moment this is based on
     *very* imprecise guesses made in best_access_path().
diff --git a/sql/table.cc b/sql/table.cc
index 16dcb48970d..bcd65835ffa 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -8634,18 +8634,19 @@ bool TABLE_LIST::process_index_hints(TABLE *tbl)
       index_group[INDEX_HINT_USE].merge(index_group[INDEX_HINT_FORCE]);
     }
 
-    /*
-      TODO: get rid of tbl->force_index (on if any FORCE INDEX is specified)
-      and create tbl->force_index_join instead.
-      Then use the correct force_index_XX instead of the global one.
-    */
-    if (!index_join[INDEX_HINT_FORCE].is_clear_all() ||
-        tbl->force_index_group || tbl->force_index_order)
+    if (!index_join[INDEX_HINT_FORCE].is_clear_all())
     {
-      tbl->force_index= TRUE;
+      tbl->force_index_join= TRUE;
       index_join[INDEX_HINT_USE].merge(index_join[INDEX_HINT_FORCE]);
     }
 
+    /*
+      TODO: get rid of tbl->force_index (on if any FORCE INDEX is specified)
+      Use the correct force_index_XX in all places instead of the global one.
+    */
+    tbl->force_index= (tbl->force_index_order | tbl->force_index_group |
+                       tbl->force_index_join);
+
     /* apply USE INDEX */
     if (!index_join[INDEX_HINT_USE].is_clear_all() || have_empty_use_join)
       tbl->keys_in_use_for_query.intersect(index_join[INDEX_HINT_USE]);
diff --git a/sql/table.h b/sql/table.h
index 4350c33d389..d94127ec486 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1507,6 +1507,9 @@ public:
   */
   bool force_index;
 
+  /* Flag set when the statement contains FORCE INDEX FOR JOIN */
+  bool force_index_join;
+
   /**
     Flag set when the statement contains FORCE INDEX FOR ORDER BY
     See TABLE_LIST::process_index_hints().
@@ -2624,9 +2627,8 @@ struct TABLE_LIST
   uint		outer_join;		/* Which join type */
   uint		shared;			/* Used in multi-upd */
   bool          updatable;		/* VIEW/TABLE can be updated now */
-  bool		straight;		/* optimize with prev table */
+  bool          straight;		/* optimize with prev table */
   bool          updating;               /* for replicate-do/ignore table */
-  bool		force_index;		/* prefer index over table scan */
   bool          ignore_leaves;          /* preload only non-leaf nodes */
   bool          crashed;                /* Table was found crashed */
   bool          skip_locked;            /* Skip locked in view defination */