Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
You need parenthesis there, not quotes: WHERE STARTDATE = (SELECT STARTDATE FROM TEST2 LIMIT 1) –  ypercube Aug 25 '12 at 12:58
    
Thank you - Correcting it... –  Satish Pandey Aug 25 '12 at 12:59
    
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
add comment

2 Answers

up vote 7 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.

share|improve this answer
    
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
add comment

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 ;
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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