Sub-query executes 1 time for a parent query or more?

Question

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?

Answer 1

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.

Answer 2

As Aaron posted, MySQL optimizer is sometimes plain dumb. If you are not working with MariaDB latest versions (and you can't wait for MySQL 5.6 to be released), you can still "fool" the optimizer into running the subquery once, by moving it from the WHERE to the FROM clause:

SELECT t.STARTDATE, t.ENDDATE 
FROM 
    TEST AS t 
  JOIN 
    (SELECT STARTDATE FROM TEST2 LIMIT 1) AS s
  ON t.STARTDATE = s.STARTDATE ;