Left Join, Order by, MySQL Optimization

Question

I have a query like this:

   SELECT m...., a...., r....
     FROM 0_member AS m                 
LEFT JOIN 0_area AS a ON a.user_id = (SELECT user_id 
                                        FROM `0_area` 
                                       WHERE user_id = m.id 
                                    ORDER BY sec_id ASC LIMIT 1)
LEFT JOIN 0_rank as r ON a.rank_id = r.id 
    WHERE m.login_userid = '$username'

The idea is to get the first row from 0_area table and hence the attempted inner join. However, it is not working as expected.

Between 0_area and 0_member, 0_member.id = 0_area.user_id. However, there are multiple rows of 0_area.user_id and I want the row having the lowest value of sec_id.

Any help please?

@The Scrum Meister sec_id is not unique, does making unique may help? — Jeremy Roy, Feb 21 '11 at 21:58
@The Scrum Meister ah, I thought I might learn something new :) — Jeremy Roy, Feb 21 '11 at 22:01
@jeremy-roy If `sec_id` was unique, you can omit the `a.user_id = g1.user_id` part of the join in cyberwiki's answer and will use the better index. — The Scrum Meister, Feb 21 '11 at 22:23

score 6 · Accepted Answer · answered Feb 21 '11 at 21:48

   SELECT m...., a...., r....
     FROM 0_member AS m               
LEFT JOIN (SELECT user_id, min(sec_id) minsec
           FROM `0_area`
           GROUP BY user_id) g1 on g1.user_id=m.id
LEFT JOIN 0_area AS a ON a.user_id = g1.user_id and a.sec_id=minsec
LEFT JOIN 0_rank as r ON a.rank_id = r.id 
    WHERE m.login_userid = '$username'

Left Join, Order by, MySQL Optimization

1 Answers1