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 have a query like this:

SELECT name FROM 
(
 SELECT * FROM `table`
 WHERE id IN (1,40,300)
 ORDER BY AnotherColumn
)

From a bandwidth and overhead standpoint, is that the same thing as doing three separate queries? Or is it significantly less overhead?

share|improve this question
    
Is id the primary key of the table? –  ypercube Sep 14 '12 at 20:23

2 Answers 2

up vote 3 down vote accepted

The amount of data you want could be fast to send over a wire, but what is mysql doing to process it and prepare it for transamission ???

Let's first look at the original query

SELECT name FROM 
(
 SELECT * FROM `table`
 WHERE id IN (1,40,300)
 ORDER BY AnotherColumn
)

If id is a primary key, this should go fast. Yet, you do not need to say get all columns in a subquery, and then read on the name from that subquery. You could just craft the SQL as

SELECT * FROM 
(
 SELECT name,AnotherColumn FROM `table`
 WHERE id IN (1,40,300)
)
ORDER BY AnotherColumn

or even better, no subquery at all

 SELECT name FROM `table`
 WHERE id IN (1,40,300)
 ORDER BY AnotherColumn

You could benchmark each of these and find negligible to nominal differences but nothing really to publish a Whitepaper over. If thousands of these queries were executed, a query digest program such as pt-query-digest or mk-query-digest could easily pick out a winner as to who is the fastest in terms of running time per call.

With regard to your original question, think of it in these terms:

If going to a movie costs $8/person and you had to bring three people to see a movie, which is cheaper ?

  • CASE01) taking three people to see a movie
  • CASE02) taking each person to see the movie at a different time

Well, do the math.

  • CASE01) $32, you and the three people
  • CASE02) $48, you (three times) and the three people

Obviously, 50% more overhead in CASE02.

If the puzzle involved

  • 4 people, CASE01) $40 vs CASE02) $64
  • 5 people, CASE01) $48 vs CASE02) $80
  • 6 people, CASE01) $56 vs CASE02) $96
  • 7 people, CASE01) $64 vs CASE02) $112
  • N people, CASE01) 8(N+1) vs CASE02) 16N

As N goes to infinity, the average overhead is doubled.

Calling three queries in a subquery or getting three rows in one subquery sure beats three separate queries anyday.

share|improve this answer
5  
+1 for seeing the same crap movie 3 times –  Phil Sep 14 '12 at 21:12
1  
@Phil LOL !!! Actually. I don't have 16N or 8(N+1) in dollars to begin with. –  RolandoMySQLDBA Sep 14 '12 at 21:15
    
Shouldn't the optimizer generate the same execution plan for all three versions? At least Oracle and PostgreSQL would do that. –  a_horse_with_no_name Sep 14 '12 at 21:31
    
@a_horse_with_no_name Perhaps, they would the same. But, my two suggested queries do not use SELECT *. This is why I stated that measuring differences would be negligible-to-nominal at best. Under the hood, the amount of data churn would be less in the second and third queries. –  RolandoMySQLDBA Sep 14 '12 at 21:57
1  
The * doesn't make a difference as the outer query is only syntactic sugar and a good optimizer will realize that only two columns are used (and only retrieve those, even if the inner query uses *). But then, given MySQL's known weakness with sub-selects it probably doesn't –  a_horse_with_no_name Sep 14 '12 at 22:31

It seems like MySQL allows ORDER BY in a subselect where it shouldn't matter, like in this case. The outer SELECT doesn't have an ORDER BY, so the results of the query aren't guaranteed to be ordered.

What three SELECT statements are you expecting to emulate, Jakobud? I only see one:

SELECT name, AnotherColumn From Table01 WHERE id IN (1,40,300) ORDER BY AnotherColumn;

Indeed, you'd hope the optimizer would realize that only name and AnotherColumn are necessary to run the query, but I think that's the actual question here: what does the MySQL optimizer do when shown this statement? That'll depend on which storage engine is used for Table01, and which indexes are available on it. Ideally, this query has an index over (id, AnotherColumn, Name) so that the query is naturally satisfied from the index and won't need to seek back to the table for additional columns or perform a sort operation to satisfy the ORDER BY -- assuming the ORDER BY is honored.

share|improve this answer

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.