With PS 5.5.40, getting same explain plan for both the queries.
mysql> EXPLAIN SELECT STRAIGHT_JOIN * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 );
+----+--------------------+-------+-----------------+-------------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-----------------+-------------------+---------+---------+------+------+-------------+
| 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
| 2 | DEPENDENT SUBQUERY | dk | unique_subquery | PRIMARY,Transform | PRIMARY | 4 | func | 1 | Using where |
+----+--------------------+-------+-----------------+-------------------+---------+---------+------+------+-------------+
2 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 );
+----+--------------------+-------+-----------------+-------------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-----------------+-------------------+---------+---------+------+------+-------------+
| 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
| 2 | DEPENDENT SUBQUERY | dk | unique_subquery | PRIMARY,Transform | PRIMARY | 4 | func | 1 | Using where |
+----+--------------------+-------+-----------------+-------------------+---------+---------+------+------+-------------+
2 rows in set (0.00 sec)
With PS 5.5.40, getting same explain plan for both the queries.
mysql> EXPLAIN SELECT STRAIGHT_JOIN * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 ); ------- ------- -----+- ------+ ------- ------- ---+--- ------- ------- --+---- -----+- ------- -+----- -+----- -+----- ------- -+ ------- ------- -----+- ------+ ------- ------- ---+--- ------- ------- --+---- -----+- ------- -+----- -+----- -+----- ------- -+ ------- ------- -----+- ------+ ------- ------- ---+--- ------- ------- --+---- -----+- ------- -+----- -+----- -+----- ------- -+
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
| 2 | DEPENDENT SUBQUERY | dk | unique_subquery | PRIMARY,Transform | PRIMARY | 4 | func | 1 | Using where |
+----+-
2 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 ); ------- ------- -----+- ------+ ------- ------- ---+--- ------- ------- --+---- -----+- ------- -+----- -+----- -+----- ------- -+ ------- ------- -----+- ------+ ------- ------- ---+--- ------- ------- --+---- -----+- ------- -+----- -+----- -+----- ------- -+ ------- ------- -----+- ------+ ------- ------- ---+--- ------- ------- --+---- -----+- ------- -+----- -+----- -+----- ------- -+
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
| 2 | DEPENDENT SUBQUERY | dk | unique_subquery | PRIMARY,Transform | PRIMARY | 4 | func | 1 | Using where |
+----+-
2 rows in set (0.00 sec)