mysql simple subquery problem

Question

table user
____________________________________________
id    name    nickname    info_id
1     john    apple        11
2     paul    banana       12
3     pauline melon        13

table info
_____________________________________________
id    job         location 
11    model       usa
12    engineer    russia
13    seller      brazil

result I want
______________________________________________
1   john    apple    model     usa

my query

left join:

select * from user a left join info b on b.id = a.info_id where a.id=1

subquery:

select a.*, b.* from (user a, info b) where b.id = a.info_id

which is better?

I'm not totally sure what the question is. Are you saying that you think there's a query that's more efficient than both of those? — Matchu
– Matchu, Commented Jun 6, 2011 at 3:42
Are you experiencing performance problems now? Can you post the entire query?\ — Abe Miessler
– Abe Miessler, Commented Jun 6, 2011 at 3:42
@Matchu Yes, @Abe Miessler don't have performance problem yet — soredive
– soredive, Commented Jun 6, 2011 at 3:48
Your two queries are almost identical except the second is performing an inner join. Actual join syntax is a matter of personal taste though I prefer the former. — Phil
– Phil, Commented Jun 6, 2011 at 3:51
As Phil says, this is preference. The query optimizer should in such as simple case produce the same steps for both. As far as "more" performance you are better off thinking about caching on the application side before worrying about such a simple query's execution plan. — Godeke
– Godeke, Commented Jun 6, 2011 at 3:54

Kerry Jones · Accepted Answer · 2011-06-06 14:41:06Z

2

SELECT a.`name`, a.`nickname`, b.`job`, b.`location` 
FROM `user` AS a
LEFT JOIN `info` AS b 
    ON ( a.`info_id` = b.`id` )

That should be pretty efficient. Try using MySQL EXPLAIN if you are concerned (also make sure there are indexes on the ID fields): http://dev.mysql.com/doc/refman/5.1/en/using-explain.html

UPDATE

After seeing that you are not having performance problems just yet, I would not worry about it. "Don't fix what ain't broken". If you find that it is slowing down in the future, or it is bottle-necking on that function, then worry about it.

The query I gave should be pretty efficient.

edited Jun 6, 2011 at 14:41

answered Jun 6, 2011 at 3:55

Kerry Jones

21.8k12 gold badges67 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql simple subquery problem

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related