Mysql – Sub-query executes 1 time for a parent query or more

MySQLselect

If i apply a static data in where clause is it faster then applying a sub-query. Example:

(The query returns 5 records as a result set)

SELECT STARTDATE, ENDDATE FROM TEST WHERE STARTDATE = '2012-08-21';

is faster then :

SELECT STARTDATE, ENDDATE FROM TEST WHERE STARTDATE = (SELECT STARTDATE FROM TEST2 LIMIT 1);

or they are same in any case?

Sub-query executes each time a comparison perform by query with each record or only for 1 time?

Best Answer

Prior to MySQL 5.6, the inner query is executed once per entry in the outer row and is easy to prove:

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

mysql> 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.

Related Question