title: Optimize sub-query selecting last record of each group
body: <p>I have this query which is a dependant query and taking much execution time</p>
  
  <pre><code>SELECT
    u.id,
    u.user_name,
    ifnull((select longitude from map where user_id = u.id order by map_id desc limit 1 ),0) as Longitude,
    ifnull((select latitude from map where user_id = u.id order by map_id desc limit 1 ),0) as Longitude,
    (select  created  from map  where user_id = 1  order by created desc   limit 1) as LatestTime
  FROM users as u
  WHERE id IN(SELECT
            user1_id FROM relation
          WHERE users.id = 1)
  ORDER BY id;
  </code></pre>
  
  <p>I tried this query in (dependant)</p>
  
  <pre><code>SELECT
    u.id,
    u.user_name,
    m.map_id,
    m.longitude,
    m.latitude,
    m.Date as created
  FROM users as u
    left join (select
             map_id,
             longitude,
             latitude,
             user_id,
             max(created) as `Date`
           from map
           group by user_id) as m
      on m.user_id = u.id
  WHERE id IN(SELECT
            user1_id FROM relation
          WHERE users.id = 1)
  ORDER BY id;
  </code></pre>
  
  <p>The problem is that the first query is dependent and working fine but taking much execution time. With the second query the problem is that it is not fetching the latest created time.
  Now i want to optimise this query. The theme is that in subquery i am first making group then i am trying to get the last record of each group. and here is the tables structure.</p>
  
  <pre><code>users : id , user_name
  map   : map_id ,  user_id ,longitude , latitude, created 
  relations : id , user1_id , user2_id , relation
  </code></pre>