优化子查询选择每组最后一条记录

我有这个查询，它是一个依赖查询并且需要很多执行时间

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;

我在（依赖）中尝试了这个查询

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;

问题是第一个查询是相关的并且工作正常但需要很多执行时间。第二个查询的问题是它没有获取最新的创建时间。 现在我想优化这个查询。主题是，在子查询中，我首先创建组，然后尝试获取每个组的最后一条记录。这是表结构。

users : id , user_name
map   : map_id ,  user_id ,longitude , latitude, created 
relations : id , user1_id , user2_id , relation

在需要性能的地方，子查询SELECT条款确实很痛苦，必须被消除:)

您可以重写这部分：

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

In:

SELECT
    u.id,
    u.user_name,
    COALESCE(m1.longitude, 0) as longitude,
    COALESCE(m1.latitude, 0) as latitude
FROM users u
LEFT JOIN map m1 ON m1.user_id = u.id
LEFT JOIN map m2 ON m2.user_id = m1.user_id AND m2.map_id > m1.map_id
WHERE m2.map_id IS NULL

我写了一个查询结构的简短解释在这个答案中 https://stackoverflow.com/questions/11131237/mysql-can-i-do-a-left-join-and-pull-only-one-row-from-the-join-table/11134161#11134161。这是一个非常值得学习的技巧，因为它更具可读性、无子查询且性能更明智。

我还没看过IN尚未部分，但如果上述内容没有帮助，将会部分。

Edit1:您可以提取创建日期并使用 MAX() 代替。

SELECT
    u.id,
    u.user_name,
    COALESCE(m1.longitude, 0) as longitude,
    COALESCE(m1.latitude, 0) as latitude,
    created.LatestTime
FROM (SELECT MAX(created) FROM map WHERE user_id = 1) created
INNER JOIN users u ON TRUE
LEFT JOIN map m1 ON m1.user_id = u.id
LEFT JOIN map m2 ON m2.user_id = m1.user_id AND m2.map_id > m1.map_id
WHERE m2.map_id IS NULL