drop database if exists test;
create database test;
use test;

CREATE TABLE `tbl1` (
  `id` bigint(16) NOT NULL,
  `rec_id` int(6) NOT NULL,
  `id_value2` bigint(16) DEFAULT NULL,
  PRIMARY KEY (`id`,`rec_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

- perl
for(my $i=1; $i <= 50000000; $i++) {
  print "$i,$i,$i\n";
}

load data local infile '/export/umesh/server/binaries/GABuilds/mysql-5.6.46/tbl1.dmp' into table tbl1 fields terminated by ',';

CREATE TABLE `tbl2` (
  `t_id` bigint(16) NOT NULL AUTO_INCREMENT,
  `s_date` datetime DEFAULT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB;

- perl

use POSIX;


for(my $i=1; $i <= 50000000; $i++) {
   my $dt = strftime "%Y-%m-%d %H:%M:%S", localtime time;
  print "$i,$dt\n";
}

load data local infile '/export/umesh/server/binaries/GABuilds/mysql-5.6.46/tbl2.dmp' into table tbl2 fields terminated by ',';


explain select t1.rec_id from tbl1 t1 INNER JOIN (select a.id,a.rec_id,s_date from tbl2 b , tbl1 a WHERE a.id_value2 = b.t_id and a.id = 49999999 order by s_date desc) t2 on (t1.id = t2.id and t1.rec_id = t2.rec_id); 

explain update tbl1 t1 INNER JOIN (select a.id,a.rec_id,b.s_date from tbl2 b , tbl1 a WHERE a.id_value2 = b.t_id and a.id = 49999999 order by s_date desc) t2 on (t1.id = t2.id and t1.rec_id = t2.rec_id)  SET t1.rec_id = @ROWNUM:= @ROWNUM+1;

explain update tbl1 t1 INNER JOIN (select a.id,a.rec_id,b.s_date from tbl1 a, tbl2 b  WHERE a.id_value2 = b.t_id and a.id = 49999999 order by s_date desc) t2 on (t1.id = t2.id and t1.rec_id = t2.rec_id)  SET t1.rec_id = @ROWNUM:= @ROWNUM+1;

- 5.6.46
rm -rf 97418/
scripts/mysql_install_db --basedir=$PWD --datadir=$PWD/97418
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/97418 --core-file --socket=/tmp/mysql_ushastry.sock  --port=3333 --log-error=$PWD/97418/log.err --secure-file-priv="" 2>&1 &


 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.46 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop database if exists test;
Query OK, 0 rows affected (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql>
mysql> CREATE TABLE `tbl1` (
    ->   `id` bigint(16) NOT NULL,
    ->   `rec_id` int(6) NOT NULL,
    ->   `id_value2` bigint(16) DEFAULT NULL,
    ->   PRIMARY KEY (`id`,`rec_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> load data local infile '/export/umesh/server/binaries/GABuilds/mysql-5.6.46/tbl1.dmp' into table tbl1 fields terminated by ',';
Query OK, 50000000 rows affected (3 min 2.34 sec)
Records: 50000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> load data local infile '/export/umesh/server/binaries/GABuilds/mysql-5.6.46/tbl2.dmp' into table tbl2 fields terminated by ',';
Query OK, 50000000 rows affected (3 min 43.14 sec)
Records: 50000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql>

mysql> explain select t1.rec_id from tbl1 t1 INNER JOIN (select a.id,a.rec_id,s_date from tbl2 b , tbl1 a WHERE a.id_value2 = b.t_id and a.id = 49999999 order by s_date desc) t2 on (t1.id = t2.id and t1.rec_id = t2.rec_id);
+----+-------------+------------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref              | rows | Extra                                        |
+----+-------------+------------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL             |    2 | NULL                                         |
|  1 | PRIMARY     | t1         | eq_ref | PRIMARY       | PRIMARY | 12      | t2.id,t2.rec_id  |    1 | Using index                                  |
|  2 | DERIVED     | a          | ref    | PRIMARY       | PRIMARY | 8       | const            |    1 | Using where; Using temporary; Using filesort |
|  2 | DERIVED     | b          | eq_ref | PRIMARY       | PRIMARY | 8       | test.a.id_value2 |    1 | NULL                                         |
+----+-------------+------------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+
4 rows in set (0.00 sec)

mysql> explain update tbl1 t1 INNER JOIN (select a.id,a.rec_id,b.s_date from tbl2 b , tbl1 a WHERE a.id_value2 = b.t_id and a.id = 49999999 order by s_date desc) t2 on (t1.id = t2.id and t1.rec_id = t2.rec_id)  SET t1.rec_id = @ROWNUM:= @ROWNUM+1;
+----+-------------+------------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref              | rows | Extra                                        |
+----+-------------+------------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL             |    2 | NULL                                         |
|  1 | PRIMARY     | t1         | eq_ref | PRIMARY       | PRIMARY | 12      | t2.id,t2.rec_id  |    1 | NULL                                         |
|  2 | DERIVED     | a          | ref    | PRIMARY       | PRIMARY | 8       | const            |    1 | Using where; Using temporary; Using filesort |
|  2 | DERIVED     | b          | eq_ref | PRIMARY       | PRIMARY | 8       | test.a.id_value2 |    1 | NULL                                         |
+----+-------------+------------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+
4 rows in set (0.00 sec)

mysql> explain update tbl1 t1 INNER JOIN (select a.id,a.rec_id,b.s_date from tbl1 a, tbl2 b  WHERE a.id_value2 = b.t_id and a.id = 49999999 order by s_date desc) t2 on (t1.id = t2.id and t1.rec_id = t2.rec_id)  SET t1.rec_id = @ROWNUM:= @ROWNUM+1;
+----+-------------+------------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref              | rows | Extra                                        |
+----+-------------+------------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL             |    2 | NULL                                         |
|  1 | PRIMARY     | t1         | eq_ref | PRIMARY       | PRIMARY | 12      | t2.id,t2.rec_id  |    1 | NULL                                         |
|  2 | DERIVED     | a          | ref    | PRIMARY       | PRIMARY | 8       | const            |    1 | Using where; Using temporary; Using filesort |
|  2 | DERIVED     | b          | eq_ref | PRIMARY       | PRIMARY | 8       | test.a.id_value2 |    1 | NULL                                         |
+----+-------------+------------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+
4 rows in set (0.00 sec)

mysql> select t1.rec_id from tbl1 t1 INNER JOIN (select a.id,a.rec_id,s_date from tbl2 b , tbl1 a WHERE a.id_value2 = b.t_id and a.id = 49999999 order by s_date desc) t2 on (t1.id = t2.id and t1.rec_id = t2.rec_id);
+----------+
| rec_id   |
+----------+
| 49999999 |
+----------+
1 row in set (0.00 sec)

mysql> update tbl1 t1 INNER JOIN (select a.id,a.rec_id,b.s_date from tbl2 b , tbl1 a WHERE a.id_value2 = b.t_id and a.id = 49999999 order by s_date desc) t2 on (t1.id = t2.id and t1.rec_id = t2.rec_id)  SET t1.rec_id = @ROWNUM:= @ROWNUM+1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

- 5.7.28 (with sql_mode='' and default optimizer_switch)

rm -rf 97418/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/97418 --log-error-verbosity=3
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/97418 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/97418/log.err --log-error-verbosity=3 --secure-file-priv="" 2>&1 &

bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop database if exists test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql>
mysql> CREATE TABLE `tbl1` (
    ->   `id` bigint(16) NOT NULL,
    ->   `rec_id` int(6) NOT NULL,
    ->   `id_value2` bigint(16) DEFAULT NULL,
    ->   PRIMARY KEY (`id`,`rec_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> load data local infile '/export/umesh/server/binaries/GABuilds/mysql-5.6.46/tbl1.dmp' into table tbl1 fields terminated by ',';
Query OK, 50000000 rows affected (4 min 50.86 sec)
Records: 50000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> CREATE TABLE `tbl2` (
    ->   `t_id` bigint(16) NOT NULL AUTO_INCREMENT,
    ->   `s_date` datetime DEFAULT NULL,
    ->   PRIMARY KEY (`t_id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> load data local infile '/export/umesh/server/binaries/GABuilds/mysql-5.6.46/tbl2.dmp' into table tbl2 fields terminated by ',';
Query OK, 50000000 rows affected (5 min 37.56 sec)
Records: 50000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> explain select t1.rec_id from tbl1 t1 INNER JOIN (select a.id,a.rec_id,s_date from tbl2 b , tbl1 a WHERE a.id_value2 = b.t_id and a.id = 49999999 order by s_date desc) t2 on (t1.id = t2.id and t1.rec_id = t2.rec_id);
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                  | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ref    | PRIMARY       | PRIMARY | 8       | const                |    1 |   100.00 | Using index |
|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 12      | const,test.t1.rec_id |    1 |   100.00 | Using where |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | test.a.id_value2     |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

mysql> explain update tbl1 t1 INNER JOIN (select a.id,a.rec_id,b.s_date from tbl2 b , tbl1 a WHERE a.id_value2 = b.t_id and a.id = 49999999 order by s_date desc) t2 on (t1.id = t2.id and t1.rec_id = t2.rec_id)  SET t1.rec_id = @ROWNUM:= @ROWNUM+1;
+----+-------------+------------+------------+--------+---------------+---------+---------+-----------------+----------+----------+----------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref             | rows     | filtered | Extra          |
+----+-------------+------------+------------+--------+---------------+---------+---------+-----------------+----------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL            |  4876920 |   100.00 | NULL           |
|  1 | UPDATE      | t1         | NULL       | eq_ref | PRIMARY       | PRIMARY | 12      | t2.id,t2.rec_id |        1 |   100.00 | NULL           |
|  2 | DERIVED     | b          | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL            | 48769205 |   100.00 | Using filesort |
|  2 | DERIVED     | a          | NULL       | ref    | PRIMARY       | PRIMARY | 8       | const           |        1 |    10.00 | Using where    |
+----+-------------+------------+------------+--------+---------------+---------+---------+-----------------+----------+----------+----------------+
4 rows in set (0.00 sec)

mysql> explain update tbl1 t1 INNER JOIN (select a.id,a.rec_id,b.s_date from tbl1 a, tbl2 b  WHERE a.id_value2 = b.t_id and a.id = 49999999 order by s_date desc) t2 on (t1.id = t2.id and t1.rec_id = t2.rec_id)  SET t1.rec_id = @ROWNUM:= @ROWNUM+1;
+----+-------------+------------+------------+--------+---------------+---------+---------+------------------+------+----------+----------------------------------------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra                                        |
+----+-------------+------------+------------+--------+---------------+---------+---------+------------------+------+----------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL             |    2 |   100.00 | NULL                                         |
|  1 | UPDATE      | t1         | NULL       | eq_ref | PRIMARY       | PRIMARY | 12      | t2.id,t2.rec_id  |    1 |   100.00 | NULL                                         |
|  2 | DERIVED     | a          | NULL       | ref    | PRIMARY       | PRIMARY | 8       | const            |    1 |   100.00 | Using where; Using temporary; Using filesort |
|  2 | DERIVED     | b          | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | test.a.id_value2 |    1 |   100.00 | NULL                                         |
+----+-------------+------------+------------+--------+---------------+---------+---------+------------------+------+----------+----------------------------------------------+
4 rows in set (0.00 sec)

mysql> select t1.rec_id from tbl1 t1 INNER JOIN (select a.id,a.rec_id,s_date from tbl2 b , tbl1 a WHERE a.id_value2 = b.t_id and a.id = 49999999 order by s_date desc) t2 on (t1.id = t2.id and t1.rec_id = t2.rec_id);
+----------+
| rec_id   |
+----------+
| 49999999 |
+----------+
1 row in set (0.00 sec)


mysql> update tbl1 t1 INNER JOIN (select a.id,a.rec_id,b.s_date from tbl2 b , tbl1 a WHERE a.id_value2 = b.t_id and a.id = 49999999 order by s_date desc) t2 on (t1.id = t2.id and t1.rec_id = t2.rec_id)  SET t1.rec_id = @ROWNUM:= @ROWNUM+1;
Query OK, 1 row affected, 1 warning (3 min 2.47 sec)
Rows matched: 1  Changed: 1  Warnings: 1