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?

  • You need parenthesis there, not quotes: WHERE STARTDATE = (SELECT STARTDATE FROM TEST2 LIMIT 1) – ypercubeᵀᴹ Aug 25 '12 at 12:58
  • You know you can test that. Just create a big enough TEST table, say 1M rows. – ypercubeᵀᴹ Aug 25 '12 at 13:02
  • Yes i can do it but still i want some internal workings, it would more beneficial for others too. – Satish Pandey Aug 25 '12 at 13:12
up vote 8 down vote accepted

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.

  • Correct .. I am using 5.1 and can see the results using sleep are taking too much time (records * sleep time). – Satish Pandey Aug 25 '12 at 14:01
  • MariaDB 10.3.7 takes 8 seconds. Perhaps the optimizer recognizes SLEEP() as special and avoids subquery materialization? – Rick James Jun 4 at 21:02
  • 5.6.22 takes 8 seconds. Nice try, but something funny is going on. – Rick James Jun 4 at 21:12

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 ;
  • If we use JOIN with sub-query is not it retrieve the data for each record? – Satish Pandey Aug 25 '12 at 14:22
  • 1
    No, the subquery will be usually executed once (check the EXPLAIN plan, it will show a "Derived" table in the "Select_type" column) – ypercubeᵀᴹ Aug 25 '12 at 14:26
  • Thank you - i am checking mysql execution plan – Satish Pandey Aug 25 '12 at 14:31

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.