Sub-query executes 1 time for a parent query or more?
Prior to MySQL 5.6, the inner query is executed once per entry in the outer row and is easy to prove:

mysql&gt; SELECT COUNT(*) FROM sample_data;
+----------+
| COUNT(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

mysql&gt; SELECT COUNT(*) FROM sample_data WHERE id = (SELECT SLEEP(1) );
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (8.00 sec)


Note that the query takes 8 seconds, thus the SLEEP(1) is executed 8 times, once for each row in sample_data.

MySQL 5.6 has implemented subquery materialization which, in theory, will prevent this from happening in many cases.  MariaDB also has a greatly improved optimizer that should prevent this.  

In most cases, it is preferable to avoid subqueries in MySQL.