CREATE DATABASE test;

CREATE TABLE test.a (id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL);

CREATE TABLE test.b (id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, related_object_id int(11) NOT NULL, is_default int(1) NOT NULL DEFAULT 0, KEY related_object_id (related_object_id) );

for i in {1..1000}; do echo "INSERT INTO test.a(name) VALUES('name$i');"|mysql;done

for i in {1..1000}; do echo "INSERT INTO test.b(related_object_id, is_default) VALUES($i,0);"|mysql;done

for i in {1..1000}; do echo "INSERT INTO test.b(related_object_id, is_default) VALUES($i,1);"|mysql;done

SELECT

   a.id AS id,

   a.name AS name

FROM

   a AS a 

WHERE

   a.id IN 

   (

      SELECT

         subp.id 

      FROM

         (

            SELECT

               a.* 

            FROM

               a a 

               LEFT JOIN

                  b AS b

                  ON (a.id = b.related_object_id AND b.is_default = 1) 

            GROUP BY

               a.id 

         )

         AS subp 

   )

+----+-------+

| id     | name  |

+----+-------+

|  1     | name1 |

+----+-------+

1 row in set (0.00 sec)

MariaDB [test]> explain extended SELECT

    ->    a.id AS id,

    ->    a.name AS name

    -> FROM

    ->    a AS a 

    -> WHERE

    ->    a.id IN 

    ->    (

    ->       SELECT

    ->          subp.id 

    ->       FROM

    ->          (

    ->             SELECT

    ->                a.* 

    ->             FROM

    ->                a a 

    ->                LEFT JOIN

    ->                   b AS b

    ->                   ON (a.id = b.related_object_id AND b.is_default = 1) 

    ->             GROUP BY

    ->                a.id 

    ->          )

    ->          AS subp 

    ->    );

+------+-----------------+-------------+--------+-------------------+-------------------+---------+-----------+------+----------+-------------+

| id   | select_type     | table       | type   | possible_keys     | key               | key_len | ref       | rows | filtered | Extra       |

+------+-----------------+-------------+--------+-------------------+-------------------+---------+-----------+------+----------+-------------+

|    1 | PRIMARY         | a           | ALL    | PRIMARY           | NULL              | NULL    | NULL      | 1000 |   100.00 |             |

|    1 | PRIMARY         | <subquery2> | eq_ref | distinct_key      | distinct_key      | 4       | func      | 1    |   100.00 |             |

|    2 | MATERIALIZED    | <derived3>  | ALL    | NULL              | NULL              | NULL    | NULL      | 1000 |   100.00 |             |

|    3 | LATERAL DERIVED | a           | eq_ref | PRIMARY           | PRIMARY           | 4       | test.a.id | 1    |   100.00 |             |

|    3 | LATERAL DERIVED | b           | ref    | related_object_id | related_object_id | 4       | test.a.id | 1    |   100.00 | Using where |

+------+-----------------+-------------+--------+-------------------+-------------------+---------+-----------+------+----------+-------------+

5 rows in set, 1 warning (0.00 sec)

SET optimizer_switch="split_materialized=off";

|  991 | name991  |

|  992 | name992  |

|  993 | name993  |

|  994 | name994  |

|  995 | name995  |

|  996 | name996  |

|  997 | name997  |

|  998 | name998  |

|  999 | name999  |

| 1000 | name1000 |

+------+----------+

1000 rows in set (0.00 sec)

MariaDB [test]> explain extended SELECT

    ->    a.id AS id,

    ->    a.name AS name

    -> FROM

    ->    a AS a 

    -> WHERE

    ->    a.id IN 

    ->    (

    ->       SELECT

    ->          subp.id 

    ->       FROM

    ->          (

    ->             SELECT

    ->                a.* 

    ->             FROM

    ->                a a 

    ->                LEFT JOIN

    ->                   b AS b

    ->                   ON (a.id = b.related_object_id AND b.is_default = 1) 

    ->             GROUP BY

    ->                a.id 

    ->          )

    ->          AS subp 

    ->    );

+------+--------------+-------------+--------+-------------------+-------------------+---------+-----------+------+----------+-------------+

| id   | select_type  | table       | type   | possible_keys     | key               | key_len | ref       | rows | filtered | Extra       |

+------+--------------+-------------+--------+-------------------+-------------------+---------+-----------+------+----------+-------------+

|    1 | PRIMARY      | a           | ALL    | PRIMARY           | NULL              | NULL    | NULL      | 1000 |   100.00 |             |

|    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key      | distinct_key      | 4       | func      | 1    |   100.00 |             |

|    2 | MATERIALIZED | <derived3>  | ALL    | NULL              | NULL              | NULL    | NULL      | 1000 |   100.00 |             |

|    3 | DERIVED      | a           | index  | NULL              | PRIMARY           | 4       | NULL      | 1000 |   100.00 |             |

|    3 | DERIVED      | b           | ref    | related_object_id | related_object_id | 4       | test.a.id | 1    |   100.00 | Using where |

+------+--------------+-------------+--------+-------------------+-------------------+---------+-----------+------+----------+-------------+

5 rows in set, 1 warning (0.00 sec)